Import CSV File into Table with ArcPy?

Question

I need to import a csv file into a local table. The code I have written is creating the table and populating it, but it is not separating the fields.

My csv file looks like this:

Serial_Num,SB_Lot_Number,Acct_Status,METER_SIZE,Cust_Name,Last_Name,SERVICE_AD,STREET_NAM
11527815,309,Active,5/8",first,last,1111111111,1373,North 10th St 
86822741,310,Active,5/8",first,last,2222222222,1367,North 10th St 
11527819,312,Active,5/8",first,last,3333333333,1353,North 10th St
11527817,313,Active,5/8",first,last,4444444444,1345,North 10th St

However the output table looks like this (Had to block out some info):

enter image description here

As you can see, instead of creating individual fields for each comma, it is lumping everything together.

Here is my code:

import csv
import os
import arcpy
newFile = r"C:\Users\jraes\Documents\NastyDrankinProjects\NewTestFile.csv"
out_gdb = r"C:\Data\temp.gdb"
arcpy.TableToTable_conversion(newFile, out_gdb, 'tempTable')

It looks like it should be fairly simple. I also did not see in the documentation where I can set a delimiter.

Answer 1

I found two problems with your input csv file:

• There are 8 entries in the column heading line, but there are 9 data entries in each of the subsequent lines, so you are missing a column header (presumably for the 1111111111 data item)

• The Table to Table gp tool does not like the double-quotes (") in your METER_SIZE column. To fix this, you must enclose the field in double-quotes and use two double-quotes to escape the double-quote character, so it will look like this:

  11527815,309,Active,"5/8""",first,last,1111111111,1373,North 10th St
  

  FYI, If you open the csv file in Excel and then re-save it, Excel will reformat the double-quotes for you.

Once I performed those fixes, Table to Table worked as expected.

• JLott: Wow.. gotta to love issues like that haha. Do you have any suggestions on getting the quote into my table? It is used in our domains. Thanks sooo much for your help.
• user94030: If you format your csv file as shown in my answer above, Table to Table will import the quote. I "fixed" the csv file by opening it in Excel and then re-saving it (in csv format) prior to running the import. Excel inserted those extra quotes for me.

Answer 2

Try making a table view using Make Table View prior to TableToTable:

import csv
import os
import arcpy
newFile = r"C:\Users\jraes\Documents\NastyDrankinProjects\NewTestFile.csv"
arcpy.MakeTableView_management(in_table=newFile, out_view='viewtemp')
out_gdb = r"C:\Data\temp.gdb"
arcpy.TableToTable_conversion('viewtemp', out_gdb, 'tempTable')

• JLott: Thanks for the quick answer @BERA. I am getting an "Invalid Expression" error when running the MakeTableView line. I''m going to try to figure out why and then see if that fixes my issue. I am wondering if it is because my .csv has quotes in it..