Select to view content in your preferred language

Need to automate the reformatting of tables

4534
15
01-26-2023 06:42 AM
Labels (2)
BarryG
by
Frequent Contributor

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

0 Kudos
15 Replies
RhettZufelt
MVP Notable Contributor

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_

0 Kudos
BarryG
by
Frequent Contributor

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.

0 Kudos
RhettZufelt
MVP Notable Contributor

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()

R_

 

0 Kudos
BarryG
by
Frequent Contributor

I do see that it converts it to comma-delimited, but still have the issue that it drops lots of values (see attached).

0 Kudos
BarryG
by
Frequent Contributor

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.

0 Kudos
RhettZufelt
MVP Notable Contributor

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.

R_

0 Kudos
BarryG
by
Frequent Contributor

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.

0 Kudos
RhettZufelt
MVP Notable Contributor

How does the csv file look in Excel?

R_

0 Kudos
BarryG
by
Frequent Contributor

I believe the issue is that several pipes exist in a row where the cells are to be vacant.

0 Kudos