Board index » delphi » Importing Excel Spreadsheets using ADOX

Importing Excel Spreadsheets using ADOX


2005-11-30 06:15:11 PM
delphi241
Hi,
Im trying to import an Excel spreadsheet using ADO. (Im using Delphi 7 sp1, ADO
2.8)
The TADOConnection is opened like this: [Provider=Microsoft.Jet.OLEDB.4.0;
Data Source='+ADBName+';Extended Properties=Excel 8.0]
I then open my TADODataset with this SQL statement: 'SELECT * FROM
[Sheet1$]'
It all works fine, when I use the FieldByName('xxx').AsXXX method.
However, if a column in the spreadsheet contains both numeric and string
data, i get a problem.
E.g. i have a spreadsheet with 1 column and 20 records. The first 10 records
contains numeric values, the last 10 contains string values.
I can read the first 10 records, but the last 10 are empty!
If I turn the records around, so the 10 first records a strings and the last
10 are numerics, I can now read the first 10 string values, but not the last
10 numeric records.
No matter how I read the field, FieldByName('xxx').AsString or
FieldByName('xxx').AsInteger, I get the same problem.
Can anybody help please ?
Am I basicly using the wrong approach alltogether?
LC
 
 

Re:Importing Excel Spreadsheets using ADOX

"LC" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях следующе?
Quote

Hi,

Im trying to import an Excel spreadsheet using ADO. (Im using Delphi 7 sp1, ADO
2.8)

The TADOConnection is opened like this: [Provider=Microsoft.Jet.OLEDB.4.0;
Data Source='+ADBName+';Extended Properties=Excel 8.0]

I then open my TADODataset with this SQL statement: 'SELECT * FROM
[Sheet1$]'

It all works fine, when I use the FieldByName('xxx').AsXXX method.

However, if a column in the spreadsheet contains both numeric and string
data, i get a problem.

E.g. i have a spreadsheet with 1 column and 20 records. The first 10
records contains numeric values, the last 10 contains string values.
I can read the first 10 records, but the last 10 are empty!
If I turn the records around, so the 10 first records a strings and the
last 10 are numerics, I can now read the first 10 string values, but not
the last 10 numeric records.

No matter how I read the field, FieldByName('xxx').AsString or
FieldByName('xxx').AsInteger, I get the same problem.

Can anybody help please ?
Am I basicly using the wrong approach alltogether?


LC



To work around this problem for read-only data, enable Import Mode by using
the setting "IMEX=1" in the Extended Properties section of the connection
string.
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source'+ADBName+';Extended
Properties="Excel 8.0;IMEX=1;"'
 

Re:Importing Excel Spreadsheets using ADOX

Thank you for your reply Vitali,
I have added the IMEX=1 property and it almost cured the problem.
But when I import large files I still get the problem.
E.g. I import a file with 800 records. The first column contain numeric
values in the first 150 records, then suddently 5 records with strings shows
up, and then numeric values for next 200 records....
When I reach the string records, I read NULL values.
However, if I place the 5 string records as the first 5 records, it all
works. The 5 string records are imported along with the rest of the numeric
and alpha-numeric records later in the file.
Is there a solution for this?
-Or does the string records have to be in the beginning of the file.
Could it be because the driver, after 150 numeric records thinks all the
records in the file are numeric, and therefore exclude the strings.
LC
"Vitali Kalinin" <XXXX@XXXXX.COM>writes
Quote


"LC" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях следующе?
news:438d7b8f$XXXX@XXXXX.COM...
>
>Hi,
>
>Im trying to import an Excel spreadsheet using ADO. (Im using Delphi 7 sp1, ADO
>2.8)
>
>The TADOConnection is opened like this:
>[Provider=Microsoft.Jet.OLEDB.4.0; Data Source='+ADBName+';Extended
>Properties=Excel 8.0]
>
>I then open my TADODataset with this SQL statement: 'SELECT * FROM
>[Sheet1$]'
>
>It all works fine, when I use the FieldByName('xxx').AsXXX method.
>
>However, if a column in the spreadsheet contains both numeric and string
>data, i get a problem.
>
>E.g. i have a spreadsheet with 1 column and 20 records. The first 10
>records contains numeric values, the last 10 contains string values.
>I can read the first 10 records, but the last 10 are empty!
>If I turn the records around, so the 10 first records a strings and the
>last 10 are numerics, I can now read the first 10 string values, but not
>the last 10 numeric records.
>
>No matter how I read the field, FieldByName('xxx').AsString or
>FieldByName('xxx').AsInteger, I get the same problem.
>
>Can anybody help please ?
>Am I basicly using the wrong approach alltogether?
>
>
>LC
>
>
>
To work around this problem for read-only data, enable Import Mode by
using the setting "IMEX=1" in the Extended Properties section of the
connection string.
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source'+ADBName+';Extended
Properties="Excel 8.0;IMEX=1;"'


 

Re:Importing Excel Spreadsheets using ADOX

Unbder HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel there is
value TypeGuessRows which controls how many records are scanned in order to
detect column type. You can change it to $FFFF but probably it will affect
performance.
Regards,
Vitali
"LC" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях следующе?
Quote

Thank you for your reply Vitali,

I have added the IMEX=1 property and it almost cured the problem.
But when I import large files I still get the problem.

E.g. I import a file with 800 records. The first column contain numeric
values in the first 150 records, then suddently 5 records with strings
shows up, and then numeric values for next 200 records....
When I reach the string records, I read NULL values.

However, if I place the 5 string records as the first 5 records, it all
works. The 5 string records are imported along with the rest of the
numeric and alpha-numeric records later in the file.

Is there a solution for this?
-Or does the string records have to be in the beginning of the file.

Could it be because the driver, after 150 numeric records thinks all the
records in the file are numeric, and therefore exclude the strings.


LC


"Vitali Kalinin" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>
>
>"LC" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях следующе?
>news:438d7b8f$XXXX@XXXXX.COM...
>>
>>Hi,
>>
>>Im trying to import an Excel spreadsheet using ADO. (Im using Delphi 7 sp1,
>>ADO 2.8)
>>
>>The TADOConnection is opened like this:
>>[Provider=Microsoft.Jet.OLEDB.4.0; Data Source='+ADBName+';Extended
>>Properties=Excel 8.0]
>>
>>I then open my TADODataset with this SQL statement: 'SELECT * FROM
>>[Sheet1$]'
>>
>>It all works fine, when I use the FieldByName('xxx').AsXXX method.
>>
>>However, if a column in the spreadsheet contains both numeric and string
>>data, i get a problem.
>>
>>E.g. i have a spreadsheet with 1 column and 20 records. The first 10
>>records contains numeric values, the last 10 contains string values.
>>I can read the first 10 records, but the last 10 are empty!
>>If I turn the records around, so the 10 first records a strings and the
>>last 10 are numerics, I can now read the first 10 string values, but not
>>the last 10 numeric records.
>>
>>No matter how I read the field, FieldByName('xxx').AsString or
>>FieldByName('xxx').AsInteger, I get the same problem.
>>
>>Can anybody help please ?
>>Am I basicly using the wrong approach alltogether?
>>
>>
>>LC
>>
>>
>>
>To work around this problem for read-only data, enable Import Mode by
>using the setting "IMEX=1" in the Extended Properties section of the
>connection string.
>'Provider=Microsoft.Jet.OLEDB.4.0;Data Source'+ADBName+';Extended
>Properties="Excel 8.0;IMEX=1;"'
>
>



 

Re:Importing Excel Spreadsheets using ADOX

I've tried you suggestion, and it works.
Performance is not an issue in this case, so no problems.
Thanks a lot
LC
"Vitali Kalinin" <XXXX@XXXXX.COM>writes
Quote

Unbder HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel there
is value TypeGuessRows which controls how many records are scanned in
order to detect column type. You can change it to $FFFF but probably it
will affect performance.

Regards,
Vitali
"LC" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях следующе?
news:438e4f45$XXXX@XXXXX.COM...
>
>Thank you for your reply Vitali,
>
>I have added the IMEX=1 property and it almost cured the problem.
>But when I import large files I still get the problem.
>
>E.g. I import a file with 800 records. The first column contain numeric
>values in the first 150 records, then suddently 5 records with strings
>shows up, and then numeric values for next 200 records....
>When I reach the string records, I read NULL values.
>
>However, if I place the 5 string records as the first 5 records, it all
>works. The 5 string records are imported along with the rest of the
>numeric and alpha-numeric records later in the file.
>
>Is there a solution for this?
>-Or does the string records have to be in the beginning of the file.
>
>Could it be because the driver, after 150 numeric records thinks all the
>records in the file are numeric, and therefore exclude the strings.
>
>
>LC
>
>
>"Vitali Kalinin" <XXXX@XXXXX.COM>writes
>news:XXXX@XXXXX.COM...
>>
>>
>>"LC" <XXXX@XXXXX.COM>сообщи?сообщила ?новостях следующе?
>>news:438d7b8f$XXXX@XXXXX.COM...
>>>
>>>Hi,
>>>
>>>Im trying to import an Excel spreadsheet using ADO. (Im using Delphi 7 sp1,
>>>ADO 2.8)
>>>
>>>The TADOConnection is opened like this:
>>>[Provider=Microsoft.Jet.OLEDB.4.0; Data Source='+ADBName+';Extended
>>>Properties=Excel 8.0]
>>>
>>>I then open my TADODataset with this SQL statement: 'SELECT * FROM
>>>[Sheet1$]'
>>>
>>>It all works fine, when I use the FieldByName('xxx').AsXXX method.
>>>
>>>However, if a column in the spreadsheet contains both numeric and
>>>string data, i get a problem.
>>>
>>>E.g. i have a spreadsheet with 1 column and 20 records. The first 10
>>>records contains numeric values, the last 10 contains string values.
>>>I can read the first 10 records, but the last 10 are empty!
>>>If I turn the records around, so the 10 first records a strings and the
>>>last 10 are numerics, I can now read the first 10 string values, but
>>>not the last 10 numeric records.
>>>
>>>No matter how I read the field, FieldByName('xxx').AsString or
>>>FieldByName('xxx').AsInteger, I get the same problem.
>>>
>>>Can anybody help please ?
>>>Am I basicly using the wrong approach alltogether?
>>>
>>>
>>>LC
>>>
>>>
>>>
>>To work around this problem for read-only data, enable Import Mode by
>>using the setting "IMEX=1" in the Extended Properties section of the
>>connection string.
>>'Provider=Microsoft.Jet.OLEDB.4.0;Data Source'+ADBName+';Extended
>>Properties="Excel 8.0;IMEX=1;"'
>>
>>
>
>
>