importing daily excel spreadsheets into item inventory access database

[evan.carrigan]

Hello,

I am not sure how complicated of a job this is, but we want to be able to import our daily recievals of inventory into our existing inventory database instead of entering it by hand. I try using the wizard, however when I get to "finish" it says there was an error and it was not imported.

I don't know much about access but I was hoping one of you could point me in the right direction.

 

[zestygirl]

I have executed such daily tasks with a fair amount of ease by doing the following:

Ensure that your excel spreadsheest's fields are the same definition (i.e. text,number, date, currency) AND in the same order as the table you are going to add into.

You can simply copy the records in excel and paste them into the appropriate table in Access.

Of course, the above assumes that you have access to the tables so that you can do this.

 

[neileg]

Easier in theory than it often is in practice. Are you importing to a new table or appending to an existing one?

If it won't import at all, it's often down to the field names. These are the column headers in your spreadsheet. If there are duplicates, missing ones, punctuation within the names, these can all cause a failure.

Sometimes, when Access tries to guess the field types, it gets it wrong. You may have to insert a row of dummy data at the top of the spreadsheet to force it to get it right.

 

[The_Doc_Man]

Neileg is absolutely right but didn't give you all the options you can use to assure proper data conformance. (I can't either, but I can add to the list.)

First problem is that the wizards are ... well, ... DUMB. But if you understand their options, you can help them make YOU look like a genius. This is a case where Murphy's Law is applicable in its full form. "When it can go wrong, it WILL go wrong." And the rest of that law is "So make it happen in a way that can't go wrong."

Instead of importing a new table each time, take this approach.

Create a table, call it TEMPTABLE, with the exact field structure you need. Create an Erase query that amounts to DELETE * FROM TEMPTABLE; and run that before each import. Then run an Excel import to an existing table rather than letting Excel create a new one. With the field types already defined, the wizard cannot go wrong unless the data in the table is wrong. And when that happens, it ain't your fault any more.

Next, if there are rows that don't behave well when imported, you have the chance to run a remediation query on your TEMPTABLE data BEFORE you attempt to append it to the live data in your master table. AND this way, it doesn't matter as much what the field names are in the spreadsheet. You could import everything as text and just trash that title-row record.

Third, if there is a format change required, you can to that in the Append query if you need to do so. Like dates come in as text but you want them as dates. Either the import to a temporary table will do that right or you can include a CDate$() function in one of the fields in the final Append query that adds the new records to your existing table. So if you imported all fields as text for screening purposes, learn the various conversions such as CDbl, CSngl, CLng, CInt, etc etc. Then you can write a meaningful Append query afterwards. If it doesn't work, you have the option of stepping back and trying again.