[AccessD] Import errors from Excel

Welcome to the list Nate.

And welcome to the suckiest data transport mechanism on this earth - Excel.

There are a whole raft of issues with using Excel as a data transport.  The first thing to know is 
that Excel uses the data in the first few (10 I believe) rows of data in each column to determine 
what kind of data is in that column.  Thus... if you have two otherwise identical spreadsheets with 
(for example) what you think are zip codes... zips are numbers right?

Silly boy...

Problem 1:  Numbers in Excel will drop the leading zero when you import to Access.  So you hope that 
the spreadsheet was filled in with TEXT (not numbers).  But...

Problem #2: It is darned difficult to tell whether the cell contains numbers or text

Problem #3; If the data in the column begins with characters, then all is good, when imported into 
excel, the data will be brought in as data type text.  Otherwise... the column will be set to an 
integer (and lose the leading zeros).

Problem #4: What happens if the data switches in mid stream, part way down the column the data 
switches from text to numeric?  Well... the import mechanism in Access may decide that you have a 
format change and puke.  Just not import the data, but only for that column.  It will nicely give 
you a table of import exceptions, with row / column / value.

Ahhh... Nate, my condolences go out to you.  Receiving data in a true spreadsheet just sucks.

CSV is only marginally better, but it is better.  Why?  Because the "data type" issue goes away. 
There are only numbers and not numbers.  However (I believe) the import mechanism in Access still 
tries to use the "first few rows" trick to determine the data type so you could still have the field 
defined as some data type based on the first few rows and then have the data switch somewhere down 
the sheet.  And in this case (zips) would decide that they are numbers and... drop the leading zeros.

Another example you could have a date which looks like 01012009 in the first few rows.  Access MIGHT 
pick up that this is a data (or it might not) and if not it would decide that it is a number and 
make the data type in the import table be an integer data type.  Except that somewhere down the data 
it switches to text (1 Jan 1980).  Sigh!  Not imported, exception table time.

Anyhow, as you can see this is not a trivial subject.

Your best bet is to define the table that it will be imported to, define an import spec and DEFINE 
that all data in all fields are text, and then convert to the right thing from inside of Access once 
the data is in.

John W. Colby
www.ColbyConsulting.com


Nathan Hosburgh wrote:
> Hi everyone - I'm rather new to the list and don't have incredible experience with Access.
> 
> I've exported data to Excel from a website.  All the data looks ok when it's in Excel, but when I import it as a new table into Access there are errors.  There is a date field that has various date formats: 1995, SUMMER 2002, SEPT-OCT 2005, Dec-08, etc.  I'm not concerned about normalizing the data into consistent date formats... I can leave it as text.  I have done this before with other tables and the data came through fine in Access.  But, this time, some of the dates are coming through in Access as 5-digit numbers.
> 
> It's really bizarre.  This happened to me originally a couple months ago, then I did something to make it import correctly.  Now, of course, I cannot remember what it was that I did.  I have a feeling it has something to do with how I save the Excel file, but I'm not sure.
> 
> Thanks,
> 
> Nate
>