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]

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:

R_

[BarryG]

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

[RhettZufelt]

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.

R_

[BarryG]

Attached are the original data dump files. Thanks!

[RhettZufelt]

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.

R_

[BarryG]

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.

Barry