Page 1 of 1
Saving a CSV-file as a Excel-file
Posted: 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
* 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local)
Re: Saving a CSV-file as a Excel-file
Posted: 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
Posted: Thu Dec 19, 2019 10:03 pm
by Marc Vanzegbroeck
Re: Saving a CSV-file as a Excel-file
Posted: 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 ... fileformat
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()