Author Topic: Create Excel file with ADODB: Problem with numbers in .xlsx file.  (Read 2624 times)

0 Members and 1 Guest are viewing this topic.

roy_043

  • Water Moccasin
  • Posts: 1895
  • BricsCAD 18
If I create an .xls file (Excel 8.0) I am able to create a table (=sheet) with numerical fields:
Code: [Select]
(setq oConnect (vlax-get-or-create-object "ADODB.Connection"))
(vlax-invoke
  oConnect
  'Open
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Test_8.xls;Extended Properties=\"Excel 8.0;HDR=Yes;\";"
)
(vlax-invoke oConnect 'Execute "CREATE TABLE [newTable] (Nr INT, Article VARCHAR, Price INT)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (1, 'Bolt', 0.25)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (2, 'Nut', 0.15)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (3, 'Washer', 0.10)")
(vlax-invoke oConnect 'Close)

If I try the same with an .xlsx file it does not work. All the fields in the new table are text fields. Does anybody know a solution?
Code: [Select]
(setq oConnect (vlax-get-or-create-object "ADODB.Connection"))
(vlax-invoke
  oConnect
  'Open
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test_12.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;\";"
)
(vlax-invoke oConnect 'Execute "CREATE TABLE [newTable] (Nr INT, Article VARCHAR, Price INT)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (1, 'Bolt', 0.25)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (2, 'Nut', 0.15)")
(vlax-invoke oConnect 'Execute "INSERT INTO [newTable$] (Nr, Article, Price) VALUES (3, 'Washer', 0.10)")
(vlax-invoke oConnect 'Close)

I have tried alternative field types (INTEGER, REAL, DOUBLE). They do not solve the issue. Although they also work for Excel 8.0.

irneb

  • Water Moccasin
  • Posts: 1794
  • ACad R9-2016, Revit Arch 6-2016
Re: Create Excel file with ADODB: Problem with numbers in .xlsx file.
« Reply #1 on: January 28, 2015, 10:37:28 AM »
In the connection string, try the following changes:
Code: [Select]
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test_12.xlsx;Extended Properties=\"Excel 12.0 XML;HDR=YES;IMEX=1\";"
Common sense - the curse in disguise. Because if you have it, you have to live with those that don't.

roy_043

  • Water Moccasin
  • Posts: 1895
  • BricsCAD 18
Re: Create Excel file with ADODB: Problem with numbers in .xlsx file.
« Reply #2 on: January 29, 2015, 05:36:30 AM »
Thanks you irneb for looking into this.
The addition of 'XML' seems to have no effect. The IMEX=1 setting does have an effect. But it can only be used for existing files. It means that columns with mixed content (text and numbers) in the rows scanned by ADO are treated as text fields.

My notes now read:
Quote
Ado Notes Excel .xlsx:
New tables and empty tables (= headers only) will only accept text values.

Ado Notes Excel .xls:
New tables will accept text values and numerical values. Empty tables (= headers only) will only accept text values.

For info on IMEX:
http://msdn.microsoft.com/en-us/library/ms141683.aspx
http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled