<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: [AccessD] Automating excel</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1226" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=337560223-04092003><FONT face=Arial color=#0000ff
size=2>Generally I shoot the users too. In this case it is data provided
to my client (an company that assists retirees with any problems they have with
their medical insurance ) by the various companies they interface with - the
pension offices, insurance carriers etc. So it would likely get me in
trouble if I tried to track down the culprit and shoot them.</FONT></SPAN></DIV>
<P><FONT size=2>John W. Colby<BR>www.colbyconsulting.com</FONT> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Hale,
Jim<BR><B>Sent:</B> Thursday, September 04, 2003 5:40 PM<BR><B>To:</B> 'Access
Developers discussion and problem solving'<BR><B>Subject:</B> RE: [AccessD]
Automating excel<BR><BR></FONT></DIV>
<P><FONT size=2>John,</FONT> <BR><FONT size=2>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.</FONT></P>
<P><FONT size=2>Jim Hale</FONT> </P>
<P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From: John
Colby [<A
href="mailto:jcolby@colbyconsulting.com">mailto:jcolby@colbyconsulting.com</A>]</FONT>
<BR><FONT size=2>Sent: Thursday, September 04, 2003 12:01 PM</FONT> <BR><FONT
size=2>To: Access Developers discussion and problem solving</FONT> <BR><FONT
size=2>Subject: RE: [AccessD] Automating excel</FONT> </P><BR>
<P><FONT size=2>This is precisely the problem. does not create an import
spec in the same</FONT> <BR><FONT size=2>way that you can do with comma
delimited files. You are expected to "link"</FONT> <BR><FONT size=2>to
the spreadsheet. That works, but when you do that, Access looks at
the</FONT> <BR><FONT size=2>first row (or few rows) of data to decide what the
data is. If the data</FONT> <BR><FONT size=2>type in a given column is
(for example) date, and then 10 rows down changes</FONT> <BR><FONT size=2>to
text - EVEN IF THE TEXT STRING IS A DATE - Access gives "error" in the</FONT>
<BR><FONT size=2>cell (or something like that).</FONT> </P>
<P><FONT size=2>Even Excel itself, up until XP, did not allow you to SEE what
the data type</FONT> <BR><FONT size=2>is. You could place your cursor in
the top cell and start moving down the</FONT> <BR><FONT size=2>spreadsheet -
you would have no clue by looking in the cell that the</FONT> <BR><FONT
size=2>underlying datatype of the cell was no longer a date, but rather a
string.</FONT> <BR><FONT size=2>In XP there is now a visual cue (a little red
triangle in the corner or the</FONT> <BR><FONT size=2>cell) that the datatype
changed.</FONT> </P>
<P><FONT size=2>Anyway, for this reason, Excel spreadsheets can really suck to
use as a data</FONT> <BR><FONT size=2>exchange method. IF the
spreadsheet is created programmatically such that</FONT> <BR><FONT size=2>all
the cells in a given column are the same data type you will never have
a</FONT> <BR><FONT size=2>problem. However if a user cuts and pastes the
data in, that process for</FONT> <BR><FONT size=2>one reason or another may
cause these data type changes at which point the</FONT> <BR><FONT
size=2>linked spreadsheet will be useless.</FONT> </P>
<P><FONT size=2>For this reason, my idea was to use Excel itself to export the
data to a</FONT> <BR><FONT size=2>true comma delimited file. The reason
is that as we all know, a comma</FONT> <BR><FONT size=2>delimited file does
not have associated with the data what the datatype was.</FONT> <BR><FONT
size=2>It is ALL just a string of characters with commas between them.
Thus a data</FONT> <BR><FONT size=2>would be converted to a string that looks
like a date. A string that</FONT> <BR><FONT size=2>contains that same
date would end up looking EXACTLY the same as the DATE</FONT> <BR><FONT
size=2>itself. Is that as clear as mud? It's all just text to a
CSV. Dates are</FONT> <BR><FONT size=2>text, strings are text, real
numbers are text, currency is text.</FONT> </P>
<P><FONT size=2>NOW, with a pure text comma delimited file, a real import
specification can</FONT> <BR><FONT size=2>be created that imports that pure
text file back in to a table and there is</FONT> <BR><FONT size=2>no
possibility of misunderstanding that date.</FONT> </P>
<P><FONT size=2>John W. Colby</FONT> <BR><FONT
size=2>www.colbyconsulting.com</FONT> </P></BLOCKQUOTE></BODY></HTML>