Author | Topic: Create XLSX file via ODBC | |
---|---|---|
Konstantin Lebedev | Create XLSX file via ODBC on Sun, 27 Feb 2022 12:22:32 +0100 For a long time we used "Export to Excel..." how ODBC export To XLS. Now we want to save data into XLSX format. This code creates file TEST.XLS with one sheet "test": cConnString := ; "DBE=ODBCDBE;DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;DriverId=790;FIL=excel 12.0;DBQ=C:\1\TEST.XLS" oSession := dacSession():new(cConnString) oSession:setDefault() aStruSQL := {{"NLG_COD", "F", 4, 0}, {"NLG_TYPE", "M", 2, 0}, {"NLG_NAME", "M", 50, 0}} cSheet := "test" DbCreate(cSheet, aStruSQL, "ODBCDBE") oSession:disconnect() If I change connect string to .... DBQ=C:\1\TEST.XLSX, will be create TEST.XLSX file, but it is not a REAL "XLSX". It has "XLS"-structure if I simply change the extension to "XLS", it will be sucessfully open in Excel). Whats wrong? | |
Jim Lee | Re: Create XLSX file via ODBC on Sun, 27 Feb 2022 16:14:56 +0100 hi, > For a long time we used "Export to Excel..." how ODBC export To XLS. > Now we want to save data into XLSX format. Connection-String have change for *.XLSx it is now "Microsoft.ACE.OLEDB.12.0" --- Diese E-Mail wurde von AVG auf Viren geprüft. http://www.avg.com | |
Konstantin Lebedev | Re: Create XLSX file via ODBC on Sun, 27 Feb 2022 17:08:14 +0100 Jim Lee wrote in message news:470e207c$6086baef$2457c@news.alaska-software.com... >hi, > >> For a long time we used "Export to Excel..." how ODBC export To XLS. >> Now we want to save data into XLSX format. > >Connection-String have change for *.XLSx >it is now "Microsoft.ACE.OLEDB.12.0" > We are using "AccessDatabaseEngine 2010" for install drivers (the most of usrs workstations have only Office 2003). There is no driver like this "Microsoft.ACE.OLEDB.12.0" .... | |
Konstantin Lebedev | Re: Create XLSX file via ODBC on Mon, 28 Feb 2022 09:24:16 +0100 NB. Code below shows me value of column named "nom_n" of sheet named "test" from the 1-st line file C:\1\TEST.XLSX ! So, ODBC driver works properly for read (and write too!) data from table if it already exists. I have not ability to CREATE NEW TABLE... cConnString := ; "DBE=ODBCDBE;DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;DBQ=C:\1\TEST.XLSX" oSession := dacSession():new(cConnString) oSession:setDefault() DbSelectArea(0) hCursor := SELECT() run query SQLStmtExec('select * from "TEST$"', oSession,,, .f.) xValue := ((hCursor) -> (FIELDGET(FIELDPOS("nom_n")))) ? xValue | |
Matej Jurac | Re: Create XLSX file via ODBC on Mon, 28 Feb 2022 12:04:55 +0100 Then simple - you provide a small xlsx without any data and copy it into target, than open, drop first tab page and create data in it ? And yes, it became PITA to export via ODBC .... I learned that you cannot install 32bit drivers for (xlsx,...) if you already have previously 64bit office installed Konstantin Lebedev je 28.2.2022 ob 9:24 napisal: > NB. > Code below shows me value of column named "nom_n" of sheet named "test" from the 1-st line file C:\1\TEST.XLSX ! > So, ODBC driver works properly for read (and write too!) data from table if it already exists. > I have not ability to CREATE NEW TABLE... > > cConnString := ; > "DBE=ODBCDBE;DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;DBQ=C:\1\TEST.XLSX" > oSession := dacSession():new(cConnString) > oSession:setDefault() > > DbSelectArea(0) > hCursor := SELECT() > run query > SQLStmtExec('select * from "TEST$"', oSession,,, .f.) > xValue := ((hCursor) -> (FIELDGET(FIELDPOS("nom_n")))) > > ? xValue | |
Jim Lee | Re: Create XLSX file via ODBC on Mon, 28 Feb 2022 19:15:31 +0100 hi, > We are using "AccessDatabaseEngine 2010" so why do you use ODBC instead of ADO direct ? > the most of usrs workstations have only Office 2003. as i know Office 2003 can "read" *.XLSx but need "Office Compatibility Pack" to "write" *.XLSx but almost 20 Year old Software which Support have end 2018 ... no Updates for Securty Problems --- Diese E-Mail wurde von AVG auf Viren geprüft. http://www.avg.com | |
Matej Jurac | Re: Create XLSX file via ODBC on Tue, 01 Mar 2022 09:28:08 +0100 @Konstantin afaik there is updated "Microsoft Access Database Engine 2016 Redistributable" that contains quite updated xlsx ODBC drivers and is from 2020-08-11 and royalty free . 32 & 64bit version https://www.microsoft.com/en-us/download/details.aspx?id=54920 It suppports Windows 7 SP1 and never OS. Jim Lee je 28.2.2022 ob 19:15 napisal: > hi, > >> We are using "AccessDatabaseEngine 2010" > > so why do you use ODBC instead of ADO direct ? > > >> the most of usrs workstations have only Office 2003. > > as i know Office 2003 can "read" *.XLSx but need "Office Compatibility Pack" > to "write" *.XLSx > but almost 20 Year old Software which Support have end 2018 ... no Updates > for Securty Problems > > > > --- > Diese E-Mail wurde von AVG auf Viren geprüft. > http://www.avg.com > | |
Konstantin Lebedev | Re: Create XLSX file via ODBC on Tue, 01 Mar 2022 13:54:37 +0100 Matej Jurac wrote in message news:345131e4$4034324e$2b65d@news.alaska-software.com... >@Konstantin > >afaik there is updated "Microsoft Access Database Engine 2016 >Redistributable" that contains quite updated xlsx ODBC drivers and is >from 2020-08-11 and royalty free . > >32 & 64bit version > >https://www.microsoft.com/en-us/download/details.aspx?id=54920 > >It suppports Windows 7 SP1 and never OS. We have "Windows Server 2008". > > > > > >Jim Lee je 28.2.2022 ob 19:15 napisal: >> hi, >> >>> We are using "AccessDatabaseEngine 2010" >> >> so why do you use ODBC instead of ADO direct ? >> >> >>> the most of usrs workstations have only Office 2003. >> >> as i know Office 2003 can "read" *.XLSx but need "Office Compatibility Pack" >> to "write" *.XLSx >> but almost 20 Year old Software which Support have end 2018 ... no Updates >> for Securty Problems >> >> >> >> --- >> Diese E-Mail wurde von AVG auf Viren geprüft. >> http://www.avg.com >> | |
Matej Jurac | Re: Create XLSX file via ODBC on Tue, 01 Mar 2022 14:59:41 +0100 You are covered with that, package supports: Windows Server 2008 R2 Konstantin Lebedev je 1.3.2022 ob 13:54 napisal: > Matej Jurac wrote in message news:345131e4$4034324e$2b65d@news.alaska-software.com... >> @Konstantin >> >> afaik there is updated "Microsoft Access Database Engine 2016 >> Redistributable" that contains quite updated xlsx ODBC drivers and is >>from 2020-08-11 and royalty free . >> >> 32 & 64bit version >> >> https://www.microsoft.com/en-us/download/details.aspx?id=54920 >> >> It suppports Windows 7 SP1 and never OS. > > We have "Windows Server 2008". > > >> >> >> >> >> >> Jim Lee je 28.2.2022 ob 19:15 napisal: >>> hi, >>> >>>> We are using "AccessDatabaseEngine 2010" >>> >>> so why do you use ODBC instead of ADO direct ? >>> >>> >>>> the most of usrs workstations have only Office 2003. >>> >>> as i know Office 2003 can "read" *.XLSx but need "Office Compatibility Pack" >>> to "write" *.XLSx >>> but almost 20 Year old Software which Support have end 2018 ... no Updates >>> for Securty Problems >>> >>> >>> >>> --- >>> Diese E-Mail wurde von AVG auf Viren geprüft. >>> http://www.avg.com >>> | |
Konstantin Lebedev | Re: Create XLSX file via ODBC on Tue, 01 Mar 2022 13:51:55 +0100 Jim Lee wrote in message news:4f1a8bce$78665bea$29aa0@news.alaska-software.com... >hi, > >> We are using "AccessDatabaseEngine 2010" > >so why do you use ODBC instead of ADO direct ? Because, we are using ODBC drivers for many data sources, for MySQL server for example. > > >> the most of usrs workstations have only Office 2003. > >as i know Office 2003 can "read" *.XLSx but need "Office Compatibility Pack" >to "write" *.XLSx >but almost 20 Year old Software which Support have end 2018 ... no Updates >for Securty Problems > > > >--- >Diese E-Mail wurde von AVG auf Viren geprüft. >http://www.avg.com | |
Jim Lee | Re: Create XLSX file via ODBC on Tue, 01 Mar 2022 16:01:09 +0100 hi, >>> We are using "AccessDatabaseEngine 2010" >> >>so why do you use ODBC instead of ADO direct ? > > Because, we are using ODBC drivers for many data sources, for MySQL server > for example. aFaik, ADO can be used for many Data "Type" like ACCESS ADS MYSQL MARIADB MSSQL ORACLE FIREBIRD SQLITE POSTGRE INFORMIX ANYWHERE DBF EXCEL there is also a ADO-RDD which can be used to simulate ISAM Style ODBC is old Way but since ADO it is recommend to use new Way with Microsoft Windows --- Diese E-Mail wurde von AVG auf Viren geprüft. http://www.avg.com | |
Boris Borzic | Re: Create XLSX file via ODBC on Tue, 01 Mar 2022 16:53:18 +0100 Read this: https://blogs.msdn.microsoft.com/sqlnativeclient/2013/01/23/introducing- the-new-microsoft-odbc-drivers-for-sql-server/ Quote: "Microsoft is adopting ODBC as the de-facto standard for native access to SQL Server and Windows Azure SQL Database. We have provided longstanding support for ODBC on Windows and, in the SQL Server 2012 timeframe, released support for ODBC on Linux (Red Hat Enterprise Linux 5 and 6, and SUSE Enterprise Linux)." The above is interesting because for many years Microsoft was pushing OLEDB and ADO very hard (both are Microsoft specific technologies). ODBC is a widely supported open industry standard. In addition, when using SQLExpress ODBC is also much faster than ADO. Best regards, Boris Borzic http://xb2.net http://sqlexpress.net industrial strength Xbase++ development tools Jim Lee wrote in news:67c0348$cba4c94$2a4dc@news.alaska-software.com: >>>> We are using "AccessDatabaseEngine 2010" >>> >>>so why do you use ODBC instead of ADO direct ? >> >> Because, we are using ODBC drivers for many data sources, for MySQL >> serve > r >> for example. > > > aFaik, ADO can be used for many Data "Type" like > > ACCESS > ADS > MYSQL > MARIADB > MSSQL > ORACLE > FIREBIRD > SQLITE > POSTGRE > INFORMIX > ANYWHERE > DBF > EXCEL > > there is also a ADO-RDD which can be used to simulate ISAM Style > > ODBC is old Way but since ADO it is recommend to use new Way with > Microsoft Windows | |
Jim Lee | Re: Create XLSX file via ODBC on Wed, 02 Mar 2022 03:17:27 +0100 hi Boris, which ODBC Driver will work with his Environment ? Konstantion have ODBC Driver from Office 2003 which can "read" *.XLSx but not "write" --- Diese E-Mail wurde von AVG auf Viren geprüft. http://www.avg.com | |
Boris Borzic | Re: Create XLSX file via ODBC on Wed, 02 Mar 2022 05:03:37 +0100 Jim Lee wrote in news:643a86dd$366fcea7$2d770@news.alaska-software.com: > which ODBC Driver will work with his Environment ? > > Konstantion have ODBC Driver from Office 2003 which can "read" *.XLSx but > not "write" Don't know. Have not accessed Excel via ODBC for a long time. Best regards, Boris Borzic http://xb2.net http://sqlexpress.net industrial strength Xbase++ development tools | |
Damir Hodak | Re: Create XLSX file via ODBC on Tue, 01 Mar 2022 14:50:29 +0100 On Sun, 27 Feb 2022 12:22:32 +0100, Konstantin Lebedev wrote: > For a long time we used "Export to Excel..." how ODBC export To XLS. > Now we want to save data into XLSX format. > > This code creates file TEST.XLS with one sheet "test": > > cConnString := ; > "DBE=ODBCDBE;DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;DriverId=790;FIL=excel 12.0;DBQ=C:\1\TEST.XLS" > oSession := dacSession():new(cConnString) > oSession:setDefault() > > aStruSQL := {{"NLG_COD", "F", 4, 0}, {"NLG_TYPE", "M", 2, 0}, {"NLG_NAME", "M", 50, 0}} > > cSheet := "test" > > DbCreate(cSheet, aStruSQL, "ODBCDBE") > oSession:disconnect() > > If I change connect string to .... DBQ=C:\1\TEST.XLSX, will be create TEST.XLSX file, but it is not a REAL "XLSX". It has "XLS"-structure > if I simply change the extension to "XLS", it will be sucessfully open in Excel). > > Whats wrong? Hi Konstantin! Have you tried to create xlsb file? I have also tried to write to xlsx, but without success. Then I found out that writing to xlsb works just fine. Regards, Damir | |
Matej Jurac | Re: Create XLSX file via ODBC on Tue, 01 Mar 2022 15:04:21 +0100 Can confirm. xlsb (xls binary) subformat works DBQ=FancyNamexls.xlsb;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;MaxScanRows=0;FirstRowHasNames=1;DriverId=1046 But you have to have 32bit drivers installed. 64bit only won't work as it will be violation of application vs driver architecture. Gruss. Damir Hodak je 1.3.2022 ob 14:50 napisal: > On Sun, 27 Feb 2022 12:22:32 +0100, Konstantin Lebedev wrote: > >> For a long time we used "Export to Excel..." how ODBC export To XLS. >> Now we want to save data into XLSX format. >> >> This code creates file TEST.XLS with one sheet "test": >> >> cConnString := ; >> "DBE=ODBCDBE;DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;DriverId=790;FIL=excel 12.0;DBQ=C:\1\TEST.XLS" >> oSession := dacSession():new(cConnString) >> oSession:setDefault() >> >> aStruSQL := {{"NLG_COD", "F", 4, 0}, {"NLG_TYPE", "M", 2, 0}, {"NLG_NAME", "M", 50, 0}} >> >> cSheet := "test" >> >> DbCreate(cSheet, aStruSQL, "ODBCDBE") >> oSession:disconnect() >> >> If I change connect string to .... DBQ=C:\1\TEST.XLSX, will be create TEST.XLSX file, but it is not a REAL "XLSX". It has "XLS"-structure >> if I simply change the extension to "XLS", it will be sucessfully open in Excel). >> >> Whats wrong? > > Hi Konstantin! > > Have you tried to create xlsb file? > > I have also tried to write to xlsx, but without success. Then I found out > that writing to xlsb works just fine. > > Regards, > > Damir | |
Konstantin Lebedev | Re: Create XLSX file via ODBC on Sat, 12 Mar 2022 09:31:31 +0100 Unfortunately, all my efforts have not yet yielded results! Now the problem occurs when trying to write a long xlsx file (150,000 records X 60 columns). After approx. 81,000 record, there is a ODBC runtime error message about "insufficient system resource". Moreover, the situation is the same when using "Microsoft" Excel driver and "CDATA" Excel driver. I tried to use both writing options: via xBase++ USE + DBappend() and via SQL Insert,the result is the same (( | |
Matej Jurac | Re: Create XLSX file via ODBC on Mon, 14 Mar 2022 13:00:45 +0100 150k Excel write is a lot... Did you use :Append()+FieldPut() way or just plain "INSERT INTO" ? imho - .Csv file format is viable option for you: - write data into .csv (fopen + fwrite + fclose) - it is fast - use CreateObject("Excel.Application") to create Excel object (needs local install of Excel) - Load .csv with that object (open method) - execute 'SaveAs' method to save data into .xlsx - close object and destroy it to free system resources how to do excel side is documnented with VBA docs at Microsoft technet might be even faster than pure odbc write to Excel file Konstantin Lebedev je 12.3.2022 ob 9:31 napisal: > Unfortunately, all my efforts have not yet yielded results! > > Now the problem occurs when trying to write a long xlsx file (150,000 records X 60 columns). > After approx. 81,000 record, there is a ODBC runtime error message about "insufficient system resource". > > Moreover, the situation is the same when using "Microsoft" Excel driver and "CDATA" Excel driver. > > I tried to use both writing options: via xBase++ USE + DBappend() and via SQL Insert,the result is the same (( | |
Konstantin Lebedev | Re: Create XLSX file via ODBC on Mon, 14 Mar 2022 15:12:23 +0100 Thank U!! ActiveX was a good idea. It works... Matej Jurac wrote in message news:21994903$aa560ef$51fa1@news.alaska-software.com... >150k Excel write is a lot... Did you use :Append()+FieldPut() way or >just plain "INSERT INTO" ? > >imho - .Csv file format is viable option for you: > > - write data into .csv (fopen + fwrite + fclose) - it is fast > - use CreateObject("Excel.Application") to create Excel object (needs >local install of Excel) >- Load .csv with that object (open method) >- execute 'SaveAs' method to save data into .xlsx >- close object and destroy it to free system resources > >how to do excel side is documnented with VBA docs at Microsoft technet > >might be even faster than pure odbc write to Excel file > > > >Konstantin Lebedev je 12.3.2022 ob 9:31 napisal: >> Unfortunately, all my efforts have not yet yielded results! >> >> Now the problem occurs when trying to write a long xlsx file (150,000 records X 60 columns). >> After approx. 81,000 record, there is a ODBC runtime error message about "insufficient system resource". >> >> Moreover, the situation is the same when using "Microsoft" Excel driver and "CDATA" Excel driver. >> >> I tried to use both writing options: via xBase++ USE + DBappend() and via SQL Insert,the result is the same (( | |
Klaus Overhage | Re: Create XLSX file via ODBC on Mon, 14 Mar 2022 14:56:33 +0100 Why not use HBLibXL ? See news://news.alaska-software.com:119/11e3862c$29de04c8$8b770@news.alaska-software.com Am 12.03.2022 um 09:31 schrieb Konstantin Lebedev: > Unfortunately, all my efforts have not yet yielded results! > > Now the problem occurs when trying to write a long xlsx file (150,000 records X 60 columns). > After approx. 81,000 record, there is a ODBC runtime error message about "insufficient system resource". > > Moreover, the situation is the same when using "Microsoft" Excel driver and "CDATA" Excel driver. > > I tried to use both writing options: via xBase++ USE + DBappend() and via SQL Insert,the result is the same (( |