Help! Convert CSV file into Excel format using VBScript

[billdvldog]

I have tried modifying SarkMan's previously posted code (from 10/02/2003 - below with my modifications) to open the CSV file using Excel and simply saving the file with an xls extension. Unfortunately this puts all the data into a single column in Excel. I'm not sure why this is happening. When I manually open the CSV file using Excel and save it as an Excel file it formats the file correctly.

Thanks in advance for your help!

Bill

<code>
set xlApp = CreateObject(&quot;excel.application&quot
xlApp.Visible = false
xlApp.DisplayAlerts = False
newFileName=&quot;C:\test.xls&quot;
xlApp.Workbooks.Open &quot;C:\test.csv&quot;
xlApp.ActiveWorkbook.SaveAs newFileName
xlApp.ActiveWorkbook.Close(0)
xlApp.Quit
Set xlApp = Nothing
</code>

 

[dougcranston]

Take a look at: thread329-697532

Although they are talking about Access, you could change the provider to reference EXCEL.

It too is dealing with comma separated data.

HTH
DougCranston

 

[PHV]

Try this:

xlApp.Workbooks.Open "C:\test.csv",0,True,2

The last 2 means separator is a comma.

Hope This Help
PH.

 

[billdvldog]

Thanks PH, but unfortunately that didn't work. It's still putting everything into the first column for some reason... Any other ideas?

 

[ggriffit]

You could try writing it the long way, read a line it, split it in your app and then write it out to the cells in excel, with some kind of loop, this could be quite efficent and small.

 

[billdvldog]

Thanks ggriffit, I'll look into how to do that!

 

[dougcranston]

Ran across the following at Born City site.

Excel: Import a CSV file

http://www.borncity.com/WSHBazaar/WSHBazaar.htm

Hope this helps.
DougCranston

 

[billdvldog]

Thanks Doug! I'm still not sure why it's still doing it, but I have worked around it by saving it as a tab delimited text file.

Thanks!

Bill