Add header to csv file

4619
11
11-22-2016 11:44 AM
jaykapalczynski
Frequent Contributor

I am creating a csv file via the SearchCursor.  This Works great....although I am not getting the field names in there.  I went about this 2 ways.  I have tried a ton of forums but just cant seem to get the right syntax to create this ONLY once and when I bring it into Excel it has a header for each field.

Any thoughts?

I add the headers IN the "for row in cursor"

This adds them BUT it creates a set of headers for each record imported

with arcpy.da.SearchCursor(fc, [var_region_1 ,var_county_1,var_ramp_1], where_clause=expression) as cursor:
   for row in cursor:
       zval = str('{0},{1},{2}'.format(row[0],row[1],row[2]))
       headers = ['region_1','county_1','ramp_1']
       outFile.write('\t'.join(headers) + '\n')       
       outFile.write(zval + "%s\n")

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Add headers BEFORE the "for row in cursor"

This adds them but when I bring into excel they are all crammed into the first box, not spread out for each field.

headers = ['region_1','county_1','ramp_1']
outFile.write('\t'.join(headers) + '\n')

with arcpy.da.SearchCursor(fc, [var_region_1 ,var_county_1,var_ramp_1], where_clause=expression) as cursor:
   for row in cursor:
      zval = str('{0},{1},{2}'.format(row[0],row[1],row[2]))
      outFile.write(zval + "%s\n")

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
11 Replies
JoshuaBixby
MVP Esteemed Contributor

You are creating a CSV, which is comma delimited, but you are inserting tabs between your headers, so they are all getting treated as a header for a single field/column.

jaykapalczynski
Frequent Contributor

I got it...answered my own question!  Just added , inside the quotes.

headers = ['region_1,','county_1,','ramp_1,']
0 Kudos
jaykapalczynski
Frequent Contributor

I have one last issue.  I have many many fields...when I write the header in it writes to the second line when I bring the csv into excel.

BUT the data that I am writing to the same csv file does not.  The individual records are all on the same line.

Why is this?  Can I specify to not return to the 2nd line ?

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Although that works, it isn't very Pythonic.  Your original code can be modified slightly to make it work with existing header format:

outFile.write(','.join(headers) + '\n')
0 Kudos
jaykapalczynski
Frequent Contributor

I changed to that...but when using the below it writes header values to the 1st and 2nd line.

NOTE: I have 78 fields.  The header line below is just showing 3.  Might be hitting a limit in Excel from a CSV file?

headers = ["region_1,county_1,ramp_1,date,"]

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

What does this produce, using either 3 or 78? (Note, commas outside of strings)

headers = ['region_1', 'county_1', 'ramp_1']
outFile.write(','.join(headers) + '\n')
0 Kudos
jaykapalczynski
Frequent Contributor

When i use 3 it places them in the correct location.

When I use all 78 it gets to a point it cuts off the text between the ' '  

It then throws the rest of what go cut off to the next line and then all the others that follow are placed in the proper locations albeit in the 2nd row.

It appears that I am hitting some sort of character limit in the CSV as it returns to the next line for the header....too many characters?

See attached csv and excel file outputs.

0 Kudos
jaykapalczynski
Frequent Contributor

I think I am running into this issue....researching now...

Maximum length of CSV file 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

When I open the CSV, everything looks fine.  When I open the CSV directly in Excel, it displays fine.  When I open your XLS, I see your issue.  What version of Excel are you using?  I am looking at the CSV using Excel 2013?

0 Kudos