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>