HELP Import Excel Data to Access HELP

[abesimpson]

I am trying to import a weekly stock data spreadsheet into an Access database. The spreadsheets are generated automatically, so the header names are absolutly consistant. The Access database has had a unique primary key added at the first import. Attemps to import subsequent weeks data comeup with an error message: Import Failed; 0 Files imported".

Each spreadsheet is a Matrix of ~8400 Rows x 110 Columns. Header descriptions can be as long at 40 characters. I don't think this is creating a problem.

Automating the process would be nice, but as it is a weekly update it is not really nessesary, and I imagine difficult and each file name is quite different.

I can provide additonal info if anyone needs it.

Any suggestions? ray:

Thanks

[/img]

 

[Dugantrain]

Make a table in Access with the same number of fields as the number of columns in your spreadsheet. Either use the TransferSpreadsheet Macro that Access provides you or code your own TransferSpreadsheet procedure (recommended, look it up in the Access/VBA help files, it's really quite easy to use). With TransferSpreadsheet, you can specify the range that you'll be importing, thereby avoiding any entanglements with column heading names altogether.

 

[abesimpson]

Where do I find this macro?

Thanks

abe

 

[Philem]

Hullo. I believe what D'train was talking about was this: Create a new macro. In the Steps box, there is a drop down choice for TransferSpreadsheet. The lower portion of the macro area will populate with the various choices available for this macro method.

HTH

P

 

[Dugantrain]

Yes, that's exactly what I meant. Sorry, I sometimes forget that not everybody stares at this stuff 50 hrs a week like I do.

 

[abesimpson]

I have used the File/Get External Data/Import command. I've tried this both with and without the headers from the spreadsheets (1st spreadsheet imports,, subsequent spreadsheets generate 0 records imported message).

There are always a few records with errors on each spreadsheet where the stock data was not available. Could this be the cause of the problem, and if so, any suggestions for a solution?


abe

 

[mdmilner]

We're going to need to see more information to try to diagnose the problem. It could be something quirky with the output spreadsheet you are trying to import into Access.

Abe:
What they are suggesting is that you do not use the File-Get External Data option. Instead you use the Macro tab and select Transfer Spreadsheet then fill in the blanks.

How about this, adding a step. Import the spreadsheet into a brand new table. Then run an append query to try to add the data to your original table.

The easy way to try this is to use QBE (query by example) wizard that you get from hitting the 'new' button. Select 'Simply Query Wizard'. Change the origin table name to the one you just imported, then use the double right arrow (move) to add all fields to the query. Hit Next. You can make the query name anything you want, BUT, hit the radio button that says "Modify the query design", then Finish. At the top of your screen, ABOVE the query window, should be a toolbar menu option called "Query Type". You'll have to hold your mouse over the button to see the tooltip info. The icon has a small down arrow on it. That means it has a pull-down menu. Select "Append" then use the pull down menu on the popup dialog box to select the table that you want to stuff all the information into and hit ok.

That's the 'easy' way to create an append query using the wizard interface.

Make sure you copy your original table before doing any of this. I usually add "BAK" to the end of any table name that's a backup (inside Access)

Note: All this will tell you, should it work, that there may be something quirky with the origin spreadsheet preventing an import.

*If* this works - you can make it slightly more complex. Go to the Macro tab and add a 'transfer spreadsheet' action and as the second line, tell it to run an OpenQuery action. This won't 'open' the query but will instead run the action query and add one table to the other.
-
Really, the best way to do all this requires a little VBA programming.
You can start with a function that throws up a windows API file-open dialog box, lets you select the file by clicking on it, then it will import the file/append it to the origin file in one shot.

Mike

 

[blueshark]

Are you trying to overwrite the existing data in access or append to it?

Or are you creating a new table? My two cents would be to do the import to a new table and see what it gives you and then go from there to see what you need to do to get it in the form you want. With 110 columns you're begging for data type conversion errors but since you are getting 0 records imported it sounds more like a primary key issue.