Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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.
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
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,
Yes I did, and the connection seems to be OK.
The Load statement seems to be the problem
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.
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 !!!
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,