Importing Excel 2007 data into an Access 2007 table.

[Blackmac007]

When trying to import an Excel 2007 spreadsheet with identical field names into a table in Access 2007 I receive an error that states that field F16 does not exist in the access table and then it will not complete the import? Any help with this would be greatly appreciated. Thanks!

 

[boblarson]

What is in the 16th field of the Excel File (Column P)?

 

[Blackmac007]

Hi Bob, no there is data in the field F16. I should also say that as a test I added the field F16 to the Access table and then tried to import the worksheet again and then I got the same error only this time it said that field F18 was not in the access table?

 

[boblarson]

Just import the spreadsheet to a NEW table (don't set it up first) to see what it does. You will likely need to import it to that table from that point on and then use append queries to move the data to the actual table you want. The problem is extraneous fields you aren't wanting but the Excel to Access import wants to put all of the columns with data somewhere.

 

[dcb]

Just import the spreadsheet to a NEW table (don't set it up first) to see what it does. You will likely need to import it to that table from that point on and then use append queries to move the data to the actual table you want. The problem is extraneous fields you aren't wanting but the Excel to Access import wants to put all of the columns with data somewhere.

Another method I have used is to copy the colums I want and paste special(values) into a new sheet. One of our contractors at work seem to give me - every fourth data sheet or so - a funny one. Even if you right click delete the column in the spreadsheet it just wont let go of trying to import that one.

PS: I think my long term work around was to set a range...

 

[Blackmac007]

Thank you Bob. I will go forward with the suggestion that you made. Many thanks for your expert assistance.

Cheers,

Michael

 

[pmproszynal]

THis is silly. I still have this problem in Office 2010.

What's so hard about MS fixing the Excel to access Import issues? Whay must we do these workarounds?
I thought I'd check out InfoPath FOrms to see if XML imports are any better in Access. Short answer - NOT!

ANyone have a clean path for gathering data from techs in the field and importing it into Access - without workarounds?

 

[mdlueck]

What's so hard about MS fixing the Excel to access Import issues? Whay must we do these workarounds?

ROTFLOL!!! "Preaching to the choir!"

ANyone have a clean path for gathering data from techs in the field and importing it into Access - without workarounds?

Due to the non-standardized CSV outputs of Access and Excel, I opted to brute force read Excel outputed CSV data one character at a time in Access VBA code to import data from Excel to Access. It works reliably, but it was tedious / extensive to code in Access VBA.

On the up side, I coded it to be universal file line ending character compatable... Cr / Lf / CrLf format.

 

[pmproszynal]

Soooo, I'll take that as a no

If you convert that VBA code to an Excel macro, you could sell it.
I would buy it, as long as it works with 2010.

 

[mdlueck]

Thank you for the compliment / encouragement... Trouble is, it becomes very implementation specific. Example: The CSV the code I developed reads is hard coded to reading three columns... so if an additional coma is encountered, said line is marked as in error and excluded from the import.

 

[pmproszynal]

...or an Access plug-in

 

[pmproszynal]

I see. Fun with code is that sometimes it's more like hardware than software

 

[mdlueck]

That is certainly one way to look at it, pmproszynal.

 

[pmproszynal]

I'm going a different route now - pushing Word forms through OneNote folders to techs in the field, and letting them email the completed form to me.

Sometimes a lo-tech solution is best when strapped for time. It's not where I wanted to go, but we can take that trip later. Ouch!