Importing multiple spreadsheets with same type data using wizard

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm a new database user. I'm trying to import an excel file I have that has
390,000 records spread over 7 sheets. The data is on each sheet is all the
same type of data and is formated the same way. Each sheet has the same row
headings on the first row of the sheet. I have been able to import each
sheet seperately into Access, but I wanted to get them all into one sheet.
Everytime I try importing one of the sheets into the exsisting tables using
the wizard I get an error that just says the import failed. Why does this
not work? How can I get all of these records into one table? Again this is
all new to me so I don't know a whole lot.
 
Murph

Terminology -- Access doesn't have sheets, even if the tables look like a
spreadsheet...

?No other information than "import failed"?

A work around might be to import the separate sheets, each into its own
table, then run append queries against them, appending each of them into an
8th, "permanent" table. Perhaps doing an append query will give you a more
descriptive error message if there's a problem combining the data.
 
To add to Jeff's information, if you tried to import a second worksheet into
an existing table, and that table was created when you imported the first
table, and you allowed ACCESS to create a primary key field for you in that
table, then your second and subsequent worksheet imports will fail because
those worksheets do not contain a column corresponding to the "autonumber"
primary key field that ACCESS created in the table for you.

When doing what you seek, I always import each worksheet into its own table
and then use append queries to copy the data into a permanent table. That
also allows you full control over the data type that you want the data to be
in the final table -- I'm sure you've already noted that ACCESS and EXCEL
don't always agree on the data type for the fields, and often it's not the
data type you want.
 
If you want to import these sheets, I know an app that can help assist you.
You can download it from http://www.tairasoft.com/download/DataMinerBeta.zip

I use this tool to extract certain portion of the data from the worksheets.
The initial step requires you to convert each worksheet into csv file. After
that the process is quite simple. I use it couple times already and it works
fine for me.

Just a reminder excel has maximum capacity for the worksheet 65,536 rows by
256 columns. If your final set of data is more than that, then you need to
split your result.

Regards,
-Kris
 
Back
Top