Export to Excel (Comma delimeted) creates new columns with commas in a text field

Discussions about tools to complement CSPro data processing
chalasa
Posts: 17
Joined: November 25th, 2014, 9:37 am

Export to Excel (Comma delimeted) creates new columns with commas in a text field

Postby chalasa » May 24th, 2018, 5:02 am

Dear Support,

My CSPRO app has a number of test fields that enumerators may sometime enter data and use commas. For example an item Food, asking for food produced; Enumerators may fill Maize, Beans, Kales under the one text alpha field (Food) separating the stated items with commas. When it comes to exporting the data to an excel file using comma delimited, the data is exported but new columns are created due to the commas in the test field instead of having all the items in one column (Food).

Is there a way I can go around this?

Gregory Martin
Posts: 997
Joined: December 5th, 2011, 11:27 pm
Location: Washington, DC

Re: Export to Excel (Comma delimeted) creates new columns with commas in a text field

Postby Gregory Martin » May 24th, 2018, 9:39 am

CSPro cannot export to a .csv format that "escapes" commas, so your best option is to export to "tab delimited" or "semicolon delimited" formats. Excel can read these, and then you won't have problems with your commas. If you have semicolons in your file, then you could have problems with that format, so tab delimited may be your best choice.

chalasa
Posts: 17
Joined: November 25th, 2014, 9:37 am

Re: Export to Excel (Comma delimeted) creates new columns with commas in a text field

Postby chalasa » May 24th, 2018, 5:19 pm

Dear Gregory,

Thank you. All is well, but still when I export with tab delimited or comma delimited, cells which contain data from a textfield (multiline) have the letter "\n" in them. I think they pick directly from the code of "next line"

How can I ensure " \n " is not exported into the exported data?

Gregory Martin
Posts: 997
Joined: December 5th, 2011, 11:27 pm
Location: Washington, DC

Re: Export to Excel (Comma delimeted) creates new columns with commas in a text field

Postby Gregory Martin » May 26th, 2018, 9:24 am

Because the \n is in the data file, it will end up in the exported data unless you remove that cell from the value before exporting. You could remove it in a batch application.

chalasa
Posts: 17
Joined: November 25th, 2014, 9:37 am

Re: Export to Excel (Comma delimeted) creates new columns with commas in a text field

Postby chalasa » May 27th, 2018, 9:11 am

Thank you Gregory.


Return to “Tools”