Alaska Software Inc. - Create XLSX file via ODBC
Username: Password:
AuthorTopic: Create XLSX file via ODBC
Konstantin LebedevCreate 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 LeeRe: 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 LebedevRe: 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 LebedevRe: 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 JuracRe: 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 LeeRe: 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 JuracRe: 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 LebedevRe: 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 JuracRe: 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 LebedevRe: 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 LeeRe: 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 BorzicRe: 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 LeeRe: 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 BorzicRe: 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 HodakRe: 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 JuracRe: 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 LebedevRe: 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 JuracRe: 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 LebedevRe: 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 OverhageRe: 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 ((