Page 1 of 1

Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 12:50 pm
by driessen
Hello,

I'm starting from a CSV-file which I need to save as an Excel-file (XLS).

I'm trying to this by using these code :
Code: Select all  Expand view
* cBest = CSV-file
oBook  := oExcel:WorkBooks:Open(TRIM(cBest))
oBook:SaveAs("Test.xls")
oBook:Close()
oBook := oExcel:WorkBooks:Open("Test.xls")
Unfortunately, the result is not good. All I get is every line of the CSV-file in a cell in the first column.

I need to have the fields seperated. How can I establish that?

Thanks a lot in advance for any help.

Re: Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 1:18 pm
by Marc Vanzegbroeck
Michel,

Are you using a Dutch version of excel?
If so, the seperator is ";" instead of ",".
You have to change the seperator in the CSV-file, of change the seperator in excel.
I usual change it with strtran() in the csv-file, before reading it to excel.

Re: Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 1:27 pm
by driessen
Mark,
I have no problem in read a CSV-file. That is going fine.
I only want to save it as an Excel-file.

I use a similar function in Word for saving a Word-document as a PDF-file. So, I add "wdFormatPDF" to the line "SaveAs". This "wdFormatPDF" is defined at the beginning of my PRG-file.
I simply can't find the similar function for Excel.

Re: Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 1:47 pm
by Marc Vanzegbroeck
Michael,

You say that you don't have any problem reading the CSV-file in excel, but isn't is so that when you read it, that everything is in the first column?

Re: Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 1:58 pm
by driessen
Mark,

No problem at all.

The CSV-files come from Belgian banks and the fields are separated by a semi-colon.

Re: Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 2:09 pm
by Marc Vanzegbroeck
Maybe specify the FileFormat since:
For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Code: Select all  Expand view
.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local)

Re: Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 8:14 pm
by driessen
Mark,

Thanks a lot.

But that is my question : how do I define the fileformat?

Re: Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 10:03 pm
by Marc Vanzegbroeck

Re: Saving a CSV-file as a Excel-file

PostPosted: Thu Dec 19, 2019 11:15 pm
by Jimmy
driessen wrote:Mark,
I use a similar function in Word for saving a Word-document as a PDF-file. So, I add "wdFormatPDF" to the line "SaveAs". This "wdFormatPDF" is defined at the beginning of my PRG-file.

I simply can't find the similar function for Excel.

you make a mistake when think you can use Excel SaveAs(PDF).
you can only use Constant from https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

PDF is a "End-User" Format that mean you can't load it again to Excel or Word without "Konverter"
you can "print" a Excel Sheet as PDF but not SaveAs()