Need to automate the reformatting of tables

[BarryG]

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

[RhettZufelt]

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_

[BarryG]

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.

[RhettZufelt]

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_

[BarryG]

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

[BarryG]

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.

[RhettZufelt]

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_

[BarryG]

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.

[RhettZufelt]

How does the csv file look in Excel?

R_

[BarryG]

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