convert XLS file to CSV file using VBS removing the comma separator

[talss21]

Hi

I need the xls file to CSV by removing the comma separator and replace it with tilda as my field contains comma into it .



Set lstArgs = WScript.Arguments
For I = 0 to lstArgs.Count - 1 ' Loop through each file

FullName = lstArgs(I)
FileName = Left(lstArgs(I), InStrRev(lstArgs(I), ".") )

' Create Excel Objects
Set objWS = CreateObject("Excel.application")
set objWB = objWS.Workbooks.Open(FullName)


objWS.application.visible=false
objWS.application.displayalerts=false
'MsgBox FileName
objWB.SaveAs FileName & "csv", 23
objWB.SaveAs
objWS.Application.Quit
objWS.Quit

' Destroy Excel Objects
Set objWS = Nothing
set objWB = Nothing

Next

Please help !

 

[tedsmith]

Try the Replace Function?
Eg
FullName = Replace(FullName,Chr(44),"~") will replace all commas with the Tilde

 

[SkipVought]

Then can you REALLY say that you have a .CSV (COMMA Separated Values) file, if the delimiters are not COMMAs?

You want to make a TEXT file, pure and simple.

Semantics, but important.

Not all text files are .csv (COMMA Separated Values) but ALL .csv are text files.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jges]

If you have any control over the creation of the CSV file, wrap each value in double quotes. So instead of:

Value1, Value2, with comma, Value3

you get the more manageable:

"Value1", "Value2, with comma", "Value3"

Assuming you do not have control over the CSV creation:
The replace function will indeed replace all the commas, but the problem is it will replace all the commas. Instead of:

Value1, Value2, with comma, Value3

you will have:

Value1~ Value2~ with comma~ Value3

and you will essentially be back where you started.

Is there a pattern to the data? If so, you might be able to use a regular expression to determine which commas need to be replaced and which need to be kept.

 

[strongm]

Try changing

objWB.SaveAs FileName & "csv", 23

to

objWB.SaveAs FileName & "csv", 20 ' xlTextWindows

Now your seperator is a TAB, and commas in fields are preserved

 

[strongm]

talss21 - was any of this of any assistance?

 

[jges]

no news is good news?

 

[dilettante]

This is VBScript anyway, so responding at all is inappropriate.

There is a proper forum for that.