[AccessD] importing from Excel

I have run into cases where applications that create Excel files that in
turn are imported into Access treat numbers as text fields for some reason.
Since Excel shows it as text, Access also imports it as text. One solution
is to highlight an empty Excel cell and <paste special><value><add> onto the
text column. This has the effect of adding a zero (Excel treats an empty
cell as zero) and forcing Excel to change the text to a number. This also
cures the problem where some entries are numbers and some text (a user puts
an apostrophe in front of a "number" for ex).

Since Excel "data tables" are free form, i.e. the user can (and often does
insert rows, stick text comments in a number field, etc., etc., I never link
directly to a user provided table- I write code to read the "records" and
validate them as necessary. I write the raw data to an Access table then do
all my processing from there. I have found that links to Excel sheets are
relatively fragile. Excel sheets are easily corrupted, so I prefer to
transfer everything into Access ASAP.

Jim Hale

-----Original Message-----
From: Susan Harkins [mailto:ssharkins at bellsouth.net]
Sent: Saturday, July 17, 2004 4:17 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] importing from Excel


I'm wondering if any of you routinely see data type conflicts when importing
Excel data into Access? I spent an afternoon with a client a few weeks back
cleaning up Excel data for a clean import into Access -- we were totally
successful. We went from losing hundreds of records to capturing every
single one -- with just a few simple changes in their routine. 
 
The one potential problem I thought of that we didn't encounter was a
conflict in data types -- have any of you experienced this? My guess is
you'd simply convert using the Import Wizard, but I'd like to hear your
horror stories and solutions.  I know imports are often automated and that
complicates things. 
 
The client's experience was strictly a one-time effort. Once they got it in
Access and we normalized the data, they were done with that part for good.
They were trying to create a searchable/reportable archive of some very old
data and used Excel as the go-between -- but then couldn't get the Excel
data clean enough to import. The truth is, it was very easy to solve -- they
just didn't know the right tricks. So, now they want it all documented, but
I personally, haven't run into any data type conflicts while importing from
Excel, but I'd like to note any potential problems if they're out there. 
 
Susan H. 
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com