-1

I try to write a script in VBS which opens CSV file do some changes in data and save it. The problem which I have is that I have data in following format:

data1,data2,
data3,data4,data5,data6,

After save operation (even that I'm not changhing file content) VBS adds additional comma characters to ends onf lines and file looks as follow:

data1,data2,,,
data3,data4,data5,data6,

It completes the missing (for him) comma. Unfortunatelly I have a program which doesn't read correctly such CSV.

Is it any way to prevent VBS from adding these additional commas? If no do you know how to remove additional commas from the end of line?

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
user3461823
  • 1,303
  • 2
  • 11
  • 17
  • This happens because your second row has 4 cells of data. Your first row has two cells of data, meaning two columns of missing values in row 1. Excel will add the comma. Yes, you can write line by line to CSV excluding these comma's. What is your VB-Code looking like? – JvdV Jun 25 '18 at 06:31
  • Have a look at the various answers here : https://stackoverflow.com/q/769621/4961700 – Solar Mike Jun 25 '18 at 06:31
  • Hi JvdV,I'm not able to paste this code now. But I can tell that it happens also when I don't modify any data just use obj save and obj close. – user3461823 Jun 25 '18 at 06:36
  • @user3461823, Have a look here: https://stackoverflow.com/questions/35037327/avoid-empty-cells-on-csv-export-for-varying-row-lenght – JvdV Jun 25 '18 at 06:51
  • CSV (comma-separated values) is a textual representation of tabular data with the table columns separated from each other by commas (or other delimiters). It's quite natural for each row in a CSV to have the same number of delimiters and empty fields for missing values. – Ansgar Wiechers Jun 25 '18 at 07:25
  • Without code we can't be much help. If you're opening in it VBS as a text file and saving it, I can guarantee it doesn't get changed unless you're writing code that changes the lines. If you're opening it in excel Application you need to specify that. – dbmitch Jun 25 '18 at 15:39

1 Answers1

1

The format of your output data is perfectly valid CSV (which is what Excel exports). If your target program is not capable of processing that you probably need to post-process the exported data before feeding it into your program, e.g. with a regular expression replacement:

filename = "C:\path\to\your.csv"

Set fso = CreateObject("Scripting.FileSystemObject")

Set re = New RegExp
re.Pattern   = ",+$"
re.Global    = True
re.Multiline = True

txt = fso.OpenTextFile(filename).ReadAll
fso.OpenTextFile(filename, 2).Write re.Replace(txt, ",")
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328