[AccessD] Automating excel

Hale, Jim jim.hale at fleetpride.com
Thu Sep 4 16:39:55 CDT 2003


John,
Generally I shoot my users who mix data. Pretty soon the ones who are left
know better than to mess up the spreadsheet. Failing this you might try
functions T,TEXT,ISNUMBER,ISTEXT, VALUE or some combination in a
"validation" column that references the actual data. This "scrubbed" data
becomes the column you import.
Jim Hale

-----Original Message-----
From: John Colby [mailto:jcolby at colbyconsulting.com]
Sent: Thursday, September 04, 2003 12: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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030904/f88f5ea2/attachment-0001.html>


More information about the AccessD mailing list