MS Access 2003 imports Excel files incorrectly. Why is this?

Question

MS Access 2003 imports Excel files incorrectly. Why is this?

Answer 1

You should double check your data types for both Excel and Access. Also, I like to use the clean and trim functions in Excel to remove any extra spaces, and to insure the correct data type.

Another check is to see if all your data is the same type. As you probably know, Excel wants to revert all data to "General" rather then "Text" or "Number". Are you also sure that you have your data as Text or Number, rather than General.

Answer 2

when you say it refuses to accept them, do you mean the data is put into an error table, or just simply not imported? This sometimes happens if you are trying to import an awful lot of data. Anyhow, try to eliminate why. Have you tried, instead of importing, pasting as a link? If that fails, why not set up an Access table with correct field names and data types and copy and paste over manually. I know it's a bind, but it could be the best way if you can't get to the bottom of the problem. As an afterthought, I presume you are doing it the right way, by creating the blank database and going up to File, Get External data?

Answer 3

occasion: word 2003 created ".record" archives by potential of default. by potential of default, word 2007 creates ".docx" archives. yet that doesn't propose something on your case. 2007 can examine and manage and edit each and every thing, even lower back to ninety 5. it rather is the case for excel and powerpoint besides. Theo nly component you could desire to be bothered approximately is while you're making plans on giving a record to a chum with 2003 or convey it to college that has 2003, in case you do this you're able to circulate to "shop As" and alter the report kind to compatibility for 2003. desire it rather is obvious

Answer 4

Another point on Access importing Excel files.

If ANY ROW has a Non-Numerical value in it Access may classify the Column as "Text" rather than generating an "IMPORT ERROR LOG". (IE row 12345 has "-" instead of 0. Excel would still view the column as "Numeric", but allow the substitution. Access will not.) Make certain Your data is CLEAN before You try to import it.



I've found when working with any imported file it's worth it to IMPORT then run a MAKE TABLE or APPEND QUERY off the imported file into another Table. That guarantees You get teh formats I need (regardless of how it imported). You can also add columns such as "Load Date" "File name" and any detail level Calculations that could come in handy...