Ok...the link table didn't work. The values still come up as numbers, and
Access won't allow me to change the values. Philip's solution of creating a
new field is likely not feasible with my client, as he'll have toi convince
all customers who create these spreadsheets to make this change, and input
only in the correct field.
However...I did an IMPORT in Access, and that imported perfectly. Does
anyone know of a script or a custom tag that will automate the importing of
an external Excel spreadsheet into an Excel database?
-Kev
-----Original Message-----
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 31, 2001 10:13 AM
To: CF-Talk
Subject: RE: Help needed with ODBC Excel datasource problems...
> When creating an ODBC datasource for Excel, you define how many
> rows to scan
> to allow CF to determine what the datatype of each field is. If you have
> column headings in your spreadsheet, then they are automatically used as
> field names when imported. I have set the number of rows to scan to 1,
> hoping to force the ODBC driver to use the column headings as the
> determining factor for data type determinination, and that didn't work. I
> figured maybe it was skipping the first row and reading the second, so I
> added "Do not alter this text" in every field in row two, again hoping to
> force the datatypes to strings.
The Excel ODBC driver is notoriously bad - even using a Link table produces
the same problem
Once it decides on a field type, then it forces it into that mode, and the
"other" type (text/numeric) are ignored
The only way to force it is to format a row into text, but not used the Cell
Format function in Excel as that's only visual
Make a cell beside the one with data with
=Mid("'"&D2,2,Len(D2))
Where D2 is the cell to the left
This will FORCE it into text mode... it's what I had to do to get one of our
clients sheets working - they now have to add that every time they upload
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
