[Solved] Saving to CSV inserts spaces before numbers

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
bartjeman
Posts: 177
Joined: Sun Jan 03, 2010 6:23 am
Location: Toronto

[Solved] Saving to CSV inserts spaces before numbers

Post by bartjeman »

I have some code to save a file to CSV, excerpt:

Code: Select all

Open NewURL For Output As #1
ExportArray = oSheet.getCellRangeByName("ExportToM2M").DataArray
For i = 0 to UBound(ExportArray)
   dataRow = ExportArray(i)
   Qty =  Val(dataRow(5))    'first row is 0, oQty = dataRow(5) allowed empty cells to be exported - something about machine epsilon - only occurs with empty cells, cells= 0 would work as expected 

   If Qty > 0 Then
      PartNo =  Chr(34) & dataRow(4) & Chr(34)
      RowID =  j : Validated = dataRow(0) : CustNo = dataRow(1) : Price = dataRow(6)
      PO =  Chr(34) & dataRow(2) &  Chr(34) : Ref = Chr(34) &  dataRow(3) &  Chr(34)
      Print #1,RowID;",";Validated;",";CustNo;",";PO;",";Ref;",";PartNo;",";Qty;",";Price
      j = j + 1
   End If
Next i
Close #1
But the CSV has single space in front of the numbers like this:

Code: Select all

 1, 0, 238,"TEST5678901234567890","test567890123456789012345","800114SD", 20, 125.7762
Thanks for any assistance :)
Last edited by MrProgrammer on Wed Mar 22, 2023 7:04 pm, edited 1 time in total.
Reason: Tagged ✓ by bartjeman in their second post -- MrProgrammer, forum moderator
OpenOffice 4.1.7 on Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Saving to CSV inserts spaces before numbers

Post by RusselB »

I don''t know if this is the reason, but years ago (ie: 1980's) positive numbers and zero had a space in front of them to allow for the sign location, even though the sign + isn't seen.
I suggest that this might be the reason, even though it's been around for quite a while.
What happens if you try a negative number?
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Saving to CSV inserts spaces before numbers

Post by JeJe »

That's what the Print statement does.

https://www.thevbprogrammer.com/Ch03/03 ... 1-Semi.htm

To avoid that you can use another method of writing to a file such as Binary output or you could convert from a number to a string yourself (possibly the whole line) before writing to the file.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Saving to CSV inserts spaces before numbers

Post by JeJe »

If you use Save As to a csv file instead of using basic it doesn't add a space.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Saving to CSV inserts spaces before numbers

Post by Zizi64 »

Workaround:

Convert the numbers to string type an cut the first character when the number is not negative (do it before you print or export them) .
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
bartjeman
Posts: 177
Joined: Sun Jan 03, 2010 6:23 am
Location: Toronto

Re: Saving to CSV inserts spaces before numbers

Post by bartjeman »

Thank you everyone !
I'll prob concatenate all the fields and write the whole line at once
OpenOffice 4.1.7 on Windows 10
Post Reply