 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Neil Macintyre
Guest
|
 Posted: Thu Jan 08, 2004 3:00 pm Post subject: Excel file with numeric columns and non-numeric headers |
 |
|
I am trying to access an Excel file that contains several columns of
numerical data. Not surprisingly, the file contains header
information in the first few rows to explain the file and identify the
columns.
Unfortunately those columns where the first row has not been left
blank cannot be viewed. Delphi assumes that the column is a
WideString Field and further down, where the numeric data should be,
it is simply blank.
I am using Delphi Version 6.
I defined a TADOConnection with Provider = Microsoft Jet 4.0 OLE Provider
and Extended Properties = Excel 8.0.
I have a TADODataSet linking to the first worksheet in the Excel file
and I then link a DBGrid to this.
Any suggestions?
Neil
|
|
| Back to top |
|
 |
Andreas Schmidt
Guest
|
| Posted: Fri Jan 09, 2004 2:30 pm Post subject: Re: Excel file with numeric columns and non-numeric headers |
|
|
"Neil Macintyre" <bellfieldpark (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:33dd8898.0401080700.6b1e641c (AT) posting (DOT) google.com...
| Quote: | I am trying to access an Excel file that contains several columns of
numerical data. Not surprisingly, the file contains header
information in the first few rows to explain the file and identify the
columns.
Unfortunately those columns where the first row has not been left
blank cannot be viewed. Delphi assumes that the column is a
WideString Field and further down, where the numeric data should be,
it is simply blank.
I am using Delphi Version 6.
I defined a TADOConnection with Provider = Microsoft Jet 4.0 OLE Provider
and Extended Properties = Excel 8.0.
I have a TADODataSet linking to the first worksheet in the Excel file
and I then link a DBGrid to this.
Any suggestions?
|
a.) try to experiment with the Extended Properties:
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
HDR=Yes; indicates that the first row contains columnnames, not data
IMEX=1; tells the driver to always read "intermixed" data columns as text
b.) Modify the Excel file:
1st Row: only fieldnames without spaces or special chars
2nd to las Row: the data
mark the columns and set the correct data type (in context menu of
excel)
avoid "intermixed" data columns!
c.)
don't use ADO to access the excel data; use Automatation
http://www.djpate.freeserve.co.uk/AutoExcl.htm
http://vzone.virgin.net/graham.marshall/excel.htm
And last but not least:
forget Excel, it's not a format that can easily read by a computer
programm.
Many bussiness peoples are misusing Excel as a database only because
they don't know MS Access.
hth
Andreas
|
|
| Back to top |
|
|
Neil Macintyre
Guest
|
| Posted: Sun Jan 11, 2004 10:38 am Post subject: Re: Excel file with numeric columns and non-numeric headers |
|
|
"Andreas Schmidt" <a_j_schmidt (AT) rocketmail (DOT) com> wrote
| Quote: | Any suggestions?
a.) try to experiment with the Extended Properties:
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
HDR=Yes; indicates that the first row contains columnnames, not data
IMEX=1; tells the driver to always read "intermixed" data columns as text
b.) Modify the Excel file:
1st Row: only fieldnames without spaces or special chars
2nd to las Row: the data
mark the columns and set the correct data type (in context menu of
excel)
avoid "intermixed" data columns!
c.)
don't use ADO to access the excel data; use Automatation
http://www.djpate.freeserve.co.uk/AutoExcl.htm
http://vzone.virgin.net/graham.marshall/excel.htm
And last but not least:
forget Excel, it's not a format that can easily read by a computer
program.
Many business peoples are misusing Excel as a database only because
they don't know MS Access.
hth
Andreas
|
Andreas,
Thank you for your suggestions (especially (a.))
Regarding your last remark - I cannot forget Excel. I am not using it
as a database, I am using it as a source of information from my
clients that I need to import into my database via a validate and
import facility.
Neil
|
|
| Back to top |
|
|
Aage Johansen
Guest
|
| Posted: Mon Jan 12, 2004 8:36 am Post subject: Re: Excel file with numeric columns and non-numeric headers |
|
|
På 11 Jan 2004 02:38:08 -0800, skrev Neil Macintyre
<bellfieldpark (AT) hotmail (DOT) com>:
| Quote: |
...
And last but not least:
forget Excel, it's not a format that can easily read by a computer
program.
Many business peoples are misusing Excel as a database only because
they don't know MS Access.
hth
Andreas
...
Regarding your last remark - I cannot forget Excel. I am not using it
as a database, I am using it as a source of information from my
clients that I need to import into my database via a validate and
import facility.
|
Maybe you could let Access do the dirty work: Import the Excel file into
Access, and then you can read the Access table(s) via Delphi/ADO.
Don't know if this will work (and I don't use Access) :-
--
Aage J.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
