lyle.hannum at co.wake.nc.us
lyle.hannum at co.wake.nc.us
Thu Sep 4 13:07:58 CDT 2003
John
Our shop has also used Erwin's approach, with a twist perhaps. From Access
we open Excel and import the text file. As with you, the problem of
changing datatypes arises here. However, as a last step before importing
from Excel to Access, we insert at row 2 some text that we can recognize as
not belonging (ie: we fill all the columns with "Delete Me"). This will
force all data to be imported to Access (into a temp table) as text,
utilizing the TransferSpreadsheet command. From here we can change
datatypes (cdate etc) in an append querry to our final table. This may be
the long way around the pond, but it has proved to user friendly and
reliable. Just a note, this is all in Access/Excel 2000 with txt files that
are delimited in a non standard way (there is a whole set of code in Excel
VBA to handle that portion).
Lyle
"Erwin Craps"
<Erwin.Craps at ithelps.be> To: "Access Developers discussion and problem
Sent by: solving" <accessd at databaseadvisors.com>
accessd-bounces at databasead cc:
visors.com Subject: RE: [AccessD] Automating excel
09/04/03 01:38 PM
Please respond to Access
Developers discussion and
problem solving
John
Don't export from Excel to afterwards import in Access.
Write some code in your Access app to browse to the excel file,
Create a object to excel
Open the file
Open a table in access
Than you have the choice
A) first read and verify all lines in Excel
Add record by record in the table
(this means twice reading all lines, but you can refuse to import before
actualy updating.
This give a faster user response in case of trouble.
Or
B) read line by line and add to table line by line.
(once reading
You can even give a error message to the user and pointing to the cell
which contains the error.
Erwin
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Thursday, September 04, 2003 7:01 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Automating excel
This is precisely the problem. does not create an import spec in the
same way that you can do with comma delimited files. You are expected
to "link" to the spreadsheet. That works, but when you do that, Access
looks at the first row (or few rows) of data to decide what the data is.
If the data type in a given column is (for example) date, and then 10
rows down changes to text - EVEN IF THE TEXT STRING IS A DATE - Access
gives "error" in the cell (or something like that).
Even Excel itself, up until XP, did not allow you to SEE what the data
type is. You could place your cursor in the top cell and start moving
down the spreadsheet - you would have no clue by looking in the cell
that the underlying datatype of the cell was no longer a date, but
rather a string. In XP there is now a visual cue (a little red triangle
in the corner or the
cell) that the datatype changed.
Anyway, for this reason, Excel spreadsheets can really suck to use as a
data exchange method. IF the spreadsheet is created programmatically
such that all the cells in a given column are the same data type you
will never have a problem. However if a user cuts and pastes the data
in, that process for one reason or another may cause these data type
changes at which point the linked spreadsheet will be useless.
For this reason, my idea was to use Excel itself to export the data to a
true comma delimited file. The reason is that as we all know, a comma
delimited file does not have associated with the data what the datatype
was. It is ALL just a string of characters with commas between them.
Thus a data would be converted to a string that looks like a date. A
string that contains that same date would end up looking EXACTLY the
same as the DATE itself. Is that as clear as mud? It's all just text
to a CSV. Dates are text, strings are text, real numbers are text,
currency is text.
NOW, with a pure text comma delimited file, a real import specification
can be created that imports that pure text file back in to a table and
there is no possibility of misunderstanding that date.
John W. Colby
www.colbyconsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark A Matte
Sent: Thursday, September 04, 2003 10:41 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Automating excel
John,
Could you create an "Import Specification" in Access where you could
control what datatype was brought into each field?
Mark
>From: "Erwin Craps" <Erwin.Craps at ithelps.be>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem
>solving"<accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Automating excel
>Date: Thu, 4 Sep 2003 07:53:40 +0200
>
>You could open the file in excel from Access and read/evaluate line by
>line from Access. Saving to CSV will not help your datatype property.
>Erwin
>
>-----Oorspronkelijk bericht-----
>Van: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] Namens Charlotte Foust
>Verzonden: woensdag 3 september 2003 22:09
>Aan: Access Developers discussion and problem solving
>Onderwerp: RE: [AccessD] Automating excel
>
>
>I'm not even sure you can do that, John. Excel has a nasty habit of
>changing datatypes no matter what you want it to do.
>
>Charlotte Foust
>
>-----Original Message-----
>From: John Colby [mailto:jcolby at colbyconsulting.com]
>Sent: Wednesday, September 03, 2003 11:56 AM
>To: AccessD
>Subject: [AccessD] Automating excel
>
>
>Has anyone ever automated Excel to save a spreadsheet as TRUE comma
>delimited text? My issue is that a client's client sends them data.
>It was comma delimited text. Now it's a spreadsheet. Spreadsheets
>SUCK for data import since it is possible that the data will change
>datatypes down the spreadsheet and that causes the import to fail
>without any warning.
>
>If I can automate excel to export that same spreadsheet, I suspect that
>the result would just be text in all cases and so that "datatype
>change" issue would disappear.
>
>Has anyone ever done this?
>
>John W. Colby
>www.colbyconsulting.com
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
Compare Cable, DSL or Satellite plans: As low as $29.95.
https://broadband.msn.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com