[AccessD] Automating excel

RE: [AccessD] Automating excelGenerally 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.
John W. Colby
www.colbyconsulting.com

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hale, Jim
  Sent: Thursday, September 04, 2003 5:40 PM
  To: 'Access Developers discussion and problem solving'
  Subject: RE: [AccessD] Automating excel


  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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030904/38a7c214/attachment-0001.html>