load all the sheets from XLSX

[thierrytt1102]

Dears,

The code below shows how to load all the sheets of a XLS file. Unfortunately, I can't make the same for  XLSX files. Do you see where is the problem?

Thank you very much!

CONNECT TO [PROVIDER=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\Prod\file.xls];

tables:       

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

          AuditBasic:

          CrossTable(Datum, docnum, 14)

          LOAD *

                              FROM C:\Prod\file.xls (biff, embedded labels, header is 1 lines, table is [$(sheetName)]);

NEXT

DROP TABLE tables; 

[thierrytt1102]

Hi,

I have office 2007 installed and I deleted some columns so you have a simple file. 14 isn't good anymore.

I'll check for office 2007 ODBC connection.

Thank you for your help

TT

[#3]

Hi, Thierrytt.

With these statements (and Office 2007 installed), seems to work. I deleted the sentence "CrossTable" just to prove that the load process works OK.

Basically, change the type of access (as indicated by Sajeevan) to "ooxml" and cut the last "$" in the name of the table (I don't now why, but the name of the sheet in "tables" is with an extra "$").

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Data Source=pruebas;Extended Properties="DSN=pruebas;DBQ=C:\Temp\AuditPartners.xlsx;DefaultDir=C:\Temp;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"];
tables:       
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
LET sheetName = Left('$(sheetName)',Len('$(sheetName)') -1);
    AuditBasic:
    LOAD *
    FROM C:\Temp\AuditPartners.xlsx (ooxml, embedded labels, header is 1 lines, table is [$(sheetName)]);
NEXT
DROP TABLE tables; 

I hop this helps you.

[thierrytt1102]

hi,

I made the DSN like yours, it seems to be connected but I got that error:

AuditBasic:

    LOAD *

    FROM C:\Temp\AuditPartners.xlsx (ooxml, embedded labels, header is 1 lines, table is [project1])

Can't go further.

Does that depends on the driver?

Regards and thank you

[#5]

Hi.

Perhaps is for the path. I wrote the path for testing in my PC ("C:\Temp\AuditPartners.xlsx").

¿Have you changed the path according with your environment?

Regards,

[thierrytt1102]

Yes I did, and the connection seems to be OK.

The Load statement seems to be the problem

[#7 · Accepted solution]

Hi.

It seems that the sentence "DISCONNECT" don't "diconnect" all; if you put another CONNECT sentence to another data source, it work:

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=pruebas;DBQ=C:\Temp\AuditPartners.xlsx;DefaultDir=C:\Performer\Temp;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"];
tables:       
SQLtables;
DISCONNECT;

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Data Source=Drop;Extended

Properties="DSN=Drop;DBQ=C:\Temp\Drop.xlsx;DefaultDir=C:\Performer\Temp;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"];

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
LET sheetName = Left('$(sheetName)',Len('$(sheetName)') -1);
    AuditBasic:
    LOAD *
    FROM C:\Temp\AuditPartners.xlsx (ooxml, embedded labels, header is 1 lines, table is [$(sheetName)]);
NEXT
DROP TABLE tables; 

Regards.

[thierrytt1102]

Dear,

Thank you for your great help... it's the first time I see such a thing. But I don't understand what is your Drop.xlsx. What's does that contain?

I made a copy paste of the original  xlsx and it works fine now.

Thank you very much !!!

[#9]

Hi.

No matter the content of de Drop.xlsx; just the fact of opening another connection. ¡In fact, I have done the same of you (copy-paste-rename of your original file)!.

Regards,