I would like to automate a process to copy, reformat, and join tables to a feature class. I have an idea of how to implement the process in Modelbuilder, all except reformatting the tables (attached snapshots). The original tables are Pipe delimited. The attached files are the final result of reformatting them in Microsoft Excel.
Does anyone know of geoprocessing tools that can be used to reformat the original tables into a usable format as such?
Thanks,
Barry
You don't say what format the input tables are, other than pipe delimited. With pipes as delimeter, (I used *.psv extention) Pro will see *.psv directly, but ArcMap will not. Assuming they are *.csv files:
This code will save new file in csv format with .csv extention (can overwrite the existing file aslo if already csv).
infile = r"C:\pathtofile\file.psv" f = open(infile,'r') filedata = f.read() f.close() newdata = filedata.replace("|",",") f = open(infile.replace('psv','csv'),'w') #f = open(infile,'w') # use this line if you want to overwrite original file f.write(newdata) f.close()
You should be able to make a script tool with similar code and incorporate that into the Model.
R_
Thanks RhettZufelt. The tables have no file extension, as are simply a nightly database dump. I did manually add one of the tables to ArcGIS Pro, as a CSV file, which did correctly separate the fields, though dropped a lot of the data out.
They "look" like text files though. Should be able to use the supplied code to open them without the extention.
If you append the extention on the output (csv file) to .csv, Arc should recognize it as being such and load.
infile = r"C:\pathtofile\file" f = open(infile,'r') filedata = f.read() f.close() newfile = infile + ".csv" newdata = filedata.replace("|",",") f = open(newfile,'w') f.write(newdata) f.close()
I do see that it converts it to comma-delimited, but still have the issue that it drops lots of values (see attached).
This (attached) is the ideal result, by importing as CSV file into blank Excel spreadsheet, and simply removing space in the header. Was fairly easy to convert this way, so may be the best option, and then build the model around the Excel file.
I can't seem to reproduce your issue. All the script does is replace the pipes with commas and saves.
What happens if you open the resultant csv file in Excel or text editor?
This would be the test of the script. If it looks fine, then is an issue with Pro.
If I generate a file similar to yours, I can load the data into Pro, but get errors when I try to open it, at least if I have two columns with the same name as your original data (Pin,Pin). If I rename the second field (as you did in your Excel_Import example), then it loads fine.
So, open with Excel to see if the pipes were all replaced with commas and each cell loads into column. If so, make sure all the field names are valid (no duplicates, none start with numbers, sometimes you can get away with spaces in the field names, but normally not, etc.).
Some best practices are as follows:
Make sure the first row of the worksheet is properly formatted. Field names are derived from the first row in each column of the worksheet in ArcGIS Pro.Field names must start with a letter.Field names must contain only letters, numbers, and underscores.Field names must not exceed 64 characters.
Cells with numeric data and dates must be consistently formatted.Ensure data intended to be numeric is categorized as such.
Field type in ArcGIS is determined by the required Microsoft driver. If a field contains mixed data types, the content is identified as strings.
RhettZufelf, I changed the title of the second field (good catch), and deleted the first row under the heading. The results are still not sorting the data in the correct fields it appears.
How does the csv file look in Excel?
I believe the issue is that several pipes exist in a row where the cells are to be vacant.
Maybe something weird with your input table as the replacement of pipes with delimeter should still keep the vacant cells. Hard to tell with just a pic of it.
I did my best to capture the first part, including empty cells and spaces, though I did rename Pin1.
However, had a little brain fog on this end I guess. Comma separated values is NOT the way to go when you have commas in your data as it sees them as delimeters also. So, any row that has a city,state in it will add extra columns.
Looks like if you make it a tab separated file, should work for you:
infile = r"C:\folder\tmp\_testPipe" delimiter = '\t' f = open(infile,'r') filedata = f.read() f.close() newfile = infile + ".txt" newdata = filedata.replace("|",delimiter) f = open(newfile,'w') f.write(newdata) f.close()
Input data:
Then, in Excel:
ArcMap:
Pro:
Hmmm, when I ran the script this time, it only separated out the last column (see attached).
Thank you so much for helping!
infile = r"C:\TEMP\PRC\PARCELADDRESSLIST.csv" delimiter = '\t' f = open(infile,'r') filedata = f.read() f.close() newfile = infile + ".txt" newdata = filedata.replace("|",delimiter) f = open(newfile,'w') f.write(newdata) f.close()
This was written for a Pipe "|" delimited input file. looks like yours is csv? And should output to a *.txt file.
Can you post the original pipe file (like you did with these csv's) instead of a pic of them? Would make it easier to see what is going on.
Attached are the original data dump files. Thanks!
Dowloaded the files, Renamed Pin to Pin2, also in the PRCDATA, had to rename the second BLDG NO to BLDG NO2 (surprised there are not more issues as Arc generally doesn't like spaces in field names) as there were errors trying to bring a table into Pro with duplicate field names.
Kept them as pipe delimted file with no extention as supplied:
Ran the code:
which saved the new files with .txt extention:
Open the new .txt files in Excel:
In ArcMap:
In Pro:
Only issues it had were the duplicate column names Pin and BLDG NO.
Strangely, I tried to recreate the same and still doesn't work on my end. It is still not fully separating the fields/values correctly.
Thank you.
Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
