Importing Excel files into Access -- without having to change theData Type to "text" one field at a

[Mike C]

Hello - I have recently begun a job where we need to transfer files to
Excel, make a few data manipulations, and then import the data into
access.

The problem is, the only way that I know to do it seems to take a lot
of time.

When I import the file to access, I have to go through each field to
identify it as text. Otherwise, I get import errors.

Is there a way to make this process more efficient. For instance, can
I somehow let access know that ALL FIELDS are text, rather than having
to scan through each individual field to change them to text.

I tried saving the excel file as a text file, but access still reads
the numbers as "long integers" until I change them to text.

Thanks for any perspective you can provide me on this.

Any advice at all on importing files to access would be appreciated.

- M

 

[Jeanette Cunningham]

Mike,
When importing from excel to access, access does a scan of the first 8 rows
of the spreadsheet.
If it finds any text in the first 8 rows of a column, it gives it a text
data type.
Access gives text a higher priority than numbers.
If you have long integers for the first 10 or so rows in a column followed
by some text data, access will error out because it can't put text in a
number data type field.
If you have nulls for the first 10 or so rows in a column followed by
numbers, access will scan the nulls and interpret them as text data type and
then be unable to put the numbers in a text field.

Choosing the option of no column headings can make each column have one row
of text data as the first row of each column.

Another option might be to link to the spreadsheet and use append queries
to move the data back into access.

If your excel data has mixed data types in a column, you could create an
excel macro that converts all fields to text before you do the
import.

There are some options to change this default behavior by using ADO to do
the import (althought it is quite limited). I can't find the reference to
this right now.

Others will probably add more suggestions to the list.

Jeanette Cunningham

 

[Jeanette Cunningham]

Oops,
this paragraph:
If you have nulls for the first 10 or so rows in a column followed by
numbers, access will scan the nulls and interpret them as text data type and
then be unable to put the numbers in a text field.

should be:
If you are importing into a field with number data type
and if you have nulls for the first 10 or so rows in a column followed by
numbers, access will scan the first 8 rows, scan the nulls and interpret
them
as text data type and be unable to put text in a number field.

Jeanette Cunningham

 

[AG]

What format is the data originally in? If in Access, do all your
manipulations withing Access and eliminate all of the transfers.

If they are text files, I would import directly into Access and again do the
manipulations there.

If the above are not options, then I would suggest creating a table with all
text fields and import your Excel data into that instead of into a new table
each time. Of course, don't forget to empty the table prior to each import.
Once in your 'all text' table, you can then use append queries to move the
data to whatever other tables you want.

 

[gllincoln]

Hi Mike,

If this is a repetitive task with many sheets having the same format, and it sounds as if it is -

You might consider doing what I've done a few times - brute force at its finest.

If I have all text and Excel ->Access aren't playing nicely together (do they ever?)

I insert a new row 2 and clone something like bogus | bugus | bogus | bogus across this first data row.
Acess will see that text and assume, "Oh, this must be a text row."

You can use A | A |A to the same effect - if you have some short text fields to contend with.

Write a macro to insert the bogus row - and on the import side (Access) run a query to search and destroy the bogus row.

Hope this helps,
Gordon

 

[Tony Toews [MVP]]

Is there a way to make this process more efficient. For instance, can
I somehow let access know that ALL FIELDS are text, rather than having
to scan through each individual field to change them to text.

The quick and dirty way would be to create the second row as having
all text. (Assuming the first row is column headings.) Then ignore
it in your processing.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Mike C]

The quick and dirty way would be to create the second row as having
all text.  (Assuming the first row is column headings.)  Then ignore
it in your processing.

Tony
--
Tony Toews, MicrosoftAccessMVP
   Please respond only in the newsgroups so that others can
read the entire thread of messages.
   MicrosoftAccessLinks, Hints, Tips & Accounting Systems athttp://www..granite.ab.ca/accsmstr.htm
   Tony's MicrosoftAccessBlog -http://msmvps.com/blogs/access/

Thanks to all for your replies!

 

[Gary Hollenbeck]

Hi Jeanette,

I am having the same problem... Interesting enough I never had the "type mismatch" error until I started using Access 2007. My process consists of importing a spreadsheet range to a temorary Access table. Then I append new records to the main table, then update all records to the main table. The import and append work fine.. Then I get the type mismatch on the update... Access insists on changing the excel format of general to a text field with the column containing all numbers for the first 12 records, no nulls. The append process doesn't seem to mind and it adds new records without a problem. Kind of strange.. I have been using this operation for about 7 years now.. Even worked for the past year with Access 2007. Since I did my last "shrink" on the excel spreadsheet it has stopped working... was wondering if you might have any other suggestions. Right now, I simply go to the access temp table once it imports and manually change it to a number format field.. my update works smoothly then.. Just kind of a pain that we no longer have a streamlined process... Thanks ahead for any help.

Gary Hollenbeck