x
Highlighted
Flippers

CSV output issues - Transactions break Rows when value is over 999.99 due to comma in data field.

Essentially my complaint is the CSV Transaction reports have commas in the currency fields that are send to the CSV build engine. When you then try to open the resulting CSV file using "Comma Delineated" then a currency value of "999.99" is fine, but add one currency unit (dollars for North America) and your system then exports "1,000.00" and then the CSV file field placements are broken from that point rightward for that row.

This issue is true for all three CSV EXPORT outputs - Transactions CSV, Items Summary CSV, and Items Detail CSV.

The fix is simple, do not have commas in the exported data fields. Square has to do this as there does not appear to be an option about selecting the output CSV data format on that Transactions page. You can't select any other character for the out delineator.

Message 1 of 5
70 Views
4 REPLIES 4
Admin

Re: CSV output issues - Transactions break Rows when value is over 999.99 due to comma in data fi...

@Flippers

 

I'm going to look further into this for you, but when you open the file, are you doing so in Excel or a different program like Google Sheets? I've seen the comma error in older versions of Excel that have difficulties reading the CSV - but looking to dig a little further into what program is opening the export.  I have not had this issue surfaced previously, so any other clarifying information would help!

Message 2 of 5
58 Views
Flippers

Re: CSV output issues - Transactions break Rows when value is over 999.99 due to comma in data fi...

I am using LibreOffice - revision 6.1.3.2, on a Macbook Pro under OSX 10.13.6 (High Sierra). Numeric Data should not have commas in it.

Message 3 of 5
56 Views
Admin

Re: CSV output issues - Transactions break Rows when value is over 999.99 due to comma in data fi...

@Flippers

 

I did check into this a bit more. Given the nature of a .CSV (comma separated document), we actually believe the issue may be in the way LibreOffice is reading the document and translating the information.

 

If you open the CSV in Google sheets and export it as a .xls does LibreOffice have the same error with the doc?

Message 4 of 5
47 Views
Flippers

Re: CSV output issues - Transactions break Rows when value is over 999.99 due to comma in data fi...

I don't like using Google tools very much, they get enough info from me in my searches that I don't have to give them my business transactions as well.

 

This is interesting reading on alternatives from IBM:

 

https://www.ibm.com/support/knowledgecenter/en/SSWU4L/Data/imc_Data/Data_q_a_watson_assistant/File_F...

 

So please offer a choice of delimiters to end users for retrieving the data such as: tab, pipe,  special characters, etc. so we aren't forced to use Google: TANSTAAFL

Message 5 of 5
35 Views