topic-38229

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 12:50 PM

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 (fw): Select all Collapse

* 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.

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 01:18 PM

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.

driessen

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Re: Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 01:27 PM

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.

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 01:47 PM

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?

driessen

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Re: Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 01:58 PM

Mark,

No problem at all.

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

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 02:09 PM

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 (fw): Select all Collapse

.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local)

driessen

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Re: Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 08:14 PM

Mark,

Thanks a lot.

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

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 10:03 PM

Michael,

Code (fw): Select all Collapse

oBook:SaveAs("Test.xls",xlExcel8)

Format saveas
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

List fileformat:
https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: Saving a CSV-file as a Excel-file
Posted: Thu Dec 19, 2019 11:15 PM

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()

greeting,

Jimmy

FiveTech Support Forums

Continue the discussion