[GIS] Import CSV File into Table with ArcPy

arcgis-10.2arcpycsv

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.

Best Answer

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.

Related Question