topic Re: Extra rows when a .csv file is opened in Excel. in Python Questions

Extra rows when a .csv file is opened in Excel.

DaleShearer — Thu, 05 Jun 2014 14:50:46 GMT

Hi, I am on my 4th day of using Python and making a lot of progress. I am looping through a feature class and writing field data to a .csv file. It all runs good. The issue is when I open the .csv file in Excel there is an empty row between each row of data.

When writing a .csv file from VB.net there are no empty rows when the file is opened in Excel. On the file created with Python all the data is there, just all spaced out with an empty row between.

I do not have a spacing character (if there is one) in the .writerow statement.

How can I fix this so there are no empty rows when I open the .csv file in Excel?

Thank you for your time. Dale,

Re: Extra rows when a .csv file is opened in Excel.

Zeke — Thu, 05 Jun 2014 14:53:02 GMT

Post your code so we can see what it's doing.

Re: Extra rows when a .csv file is opened in Excel.

DaleShearer — Thu, 05 Jun 2014 15:41:38 GMT

Thanks for responding Greg, I did find a solution. As soon as I posted the question, I did another search on the internet and found the answer. I have already tested it.

What I found is that when using .csv in Python the output file needs to be opened in binary mode.

My code was like this: open('DataFileComparison', 'w')

When I set the file mode (sorry, not sure exactly what to call it yet) to 'wb' it worked fine, no extra rows.

The code is now: open('DataFileComparison', 'wb')

Dale,

Re: Extra rows when a .csv file is opened in Excel.

JamesCrandall — Thu, 05 Jun 2014 15:49:09 GMT

I realize you mentioned that you are just starting, but its also good to be exposed to advanced (maybe) techniques too. Here's an alternative way to write out feature classes and tables to .csv output files using arcpy.da, numpy and pandas libraries.

 import pandas as pd import numpy as np  fc = r'H:\Documents\ArcGIS\Default.gdb\MyFeatureClass'  #generate a list of fields flds = [f.name for f in arcpy.ListFields(fc)]  #you cannot include Shape/Geometry or Date fields in a NumPyArrray, so remove them from the field list flds.remove('Shape') #this is the geometry field in my FeatureClass flds.remove('START_TEST') #this is a date field in my FeatureClass  #convert to a NumPyArray nmpyarr = arcpy.da.TableToNumPyArray(fc, flds)  #convert to a Pandas DataFrame dfFC = pd.DataFrame(nmpyarr)  #Write the .csv file csv_out = r'H:\Documents\csvOUTPUT.csv' dfFC.to_csv(csv_out)

Re: Extra rows when a .csv file is opened in Excel.

DaleShearer — Thu, 05 Jun 2014 15:59:45 GMT

Hi James, that is definitely an answer. I do thank for the code you posted, I will give it a try as I continue in with Python. Right now I am just happy it is working, once I get more knowledgeable of the code I will go from I can see what it is doing to knowing what it is doing.

Thank you again. Dale,

Re: Extra rows when a .csv file is opened in Excel.

JamesCrandall — Thu, 05 Jun 2014 16:44:04 GMT

Hi James, that is definitely an answer. I do thank for the code you posted, I will give it a try as I continue in with Python. Right now I am just happy it is working, once I get more knowledgeable of the code I will go from I can see what it is doing to knowing what it is doing.

Thank you again. Dale,

No problem. It's a good idea to look at a diverse set of solutions as you may find need for them in the future. It's perfectly fine to keep withint ESRI stack! But I've had a lot of success incorporating 3rd party libraries into my GIS applications and glad I was exposed to alternative approaches --- I simply thought you might want that too!

Take care