Importing Data

[TeeSee]

I have two Excel spreadsheets with the same columns (fields) .... one
for 2005 and one for 2006. The 2006 sheet imports nicely into Access
but the 2005 sheet does not. The import Wizard states an error occurs
and so the file is not imported but doesn't explain at all what the
error is. I have visually compared the sheets and can't see the
problem. Any thoughts?

 

['69 Camaro]

Hi.

The import Wizard states an error occurs
and so the file is not imported but doesn't explain at all what the
error is.

You'll probably find out what the problem is if you use the Import Text Wizard
instead. Please see the following Web page for a link to the article,
"Spreadsheet Import Wizard Error":

http://www.Access.QBuilt.com/html/articles.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[TeeSee]

Thanks ... That created an "import errors table" with the reason given
as "type conversion failure". I ensured that the offending columns were
formatted correctly but it still wouldn't import as a spreadsheet. What
creates that error?

 

[TeeSee]

Further to that I have checked each column using TYPE() and each column
uniformly contains the correct data type.??

 

['69 Camaro]

Hi.

Further to that I have checked each column using TYPE() and each column
uniformly contains the correct data type.??

Which data type? Date/time commonly gives a type conversion failure when the
month/day/year format in the spreadsheet is formatted improperly in some of the
records. For example, 18/12/2006 would fail because there isn't an 18th month
of the year.

Try to match up the record numbers that fail (as listed in the import errors
table) with the actual row numbers in the spreadsheet to see what Jet is trying
to import. You may also try linking to the spreadsheet. The columns with
problems converting the data will show #Num! or #Error! in specific records.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[TeeSee]

Thanks again for responding ... I did read the QBuilt article which is
very enlightening. There are no dates whatsoever. The only data types
are wunsies and twosies. I'll try the link thing to see what I can
glean.
Regards

 

[TeeSee]

Well, the linked tables had no #NUM or #ERROR in it but two columns of
data didn't show up. From that I saw that the two column headers had
leading spaces in them. I retyped each of the six columns and then it
imported as a spreadsheet with no problem. Sorry to waste your time but
I did learn quite a bit by this little exercise.
Have a great holiday! Best regards

 

['69 Camaro]

Hi.

The only data types
are wunsies and twosies.

Since these are numbers, Jet may be trying to import them as text into number
columns in the Jet table due to the formatting in Excel. Another technique in
troubleshooting is to convert the Excel spreadsheet to a .txt file, then open
the file in Notepad. If you do this, look at the rows that fail to import and
see if you see anything funny, i.e., double quotes around the number or a single
quote (tick mark) preceding the number in that column. Those won't import as
numbers, but a complete text string instead.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

['69 Camaro]

Hi.

I retyped each of the six columns and then it
imported as a spreadsheet with no problem.

Great! You solved the problem.

Sorry to waste your time but
I did learn quite a bit by this little exercise.

You didn't waste my time. You needed help and asked for it, and in the process
solved the problem. Good job!

Merry Xmas!
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.