UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Importing Excel Data Error, Access 2016    
 
   
duggie
post Sep 9 2019, 10:46 AM
Post#1



Posts: 474
Joined: 14-October 12



I have manually imported some Excel data into Access.

After finishing, a new errors table with 3 columns appears in Access.

For example:

CODE
Error                    Field    Row
Type Conversion Failure    F74     10
Type Conversion Failure    F76     13


What does the Field and Row values mean?

Is it referring to the spreadsheet or Access table?

Thanks
This post has been edited by duggie: Sep 9 2019, 10:47 AM
Go to the top of the page
 
theDBguy
post Sep 9 2019, 10:48 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


Hi. My guess is it's referring to the original Excel spreadsheet.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
duggie
post Sep 9 2019, 10:59 AM
Post#3



Posts: 474
Joined: 14-October 12



Thanks but it's confusing.

Row number is obvious but what does Field refer to?

Can't be column (of the data in the spreadsheet) because I only have a handful of columns of data!

Besides, I experimented by only importing one row of data, yet got the same errors table in Access.


This post has been edited by duggie: Sep 9 2019, 11:01 AM
Go to the top of the page
 
projecttoday
post Sep 9 2019, 11:05 AM
Post#4


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


When importing Excel to Access there are 2 ways: if the first row is column (field) names Access will name the imported table with those names. If there are no field names, Access numbers the columns (fields) prefixed with an "F". Does the spreadsheet you're importing have row 1 with column (field) names? My guess is not.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Sep 9 2019, 11:08 AM
Post#5


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


Just saw you last post. You're getting 76 but you say only a few columns? Hmmm. Is it possible there are hidden columns in that spreadsheet? How are you importing it?

--------------------
Robert Crouser
Go to the top of the page
 
theDBguy
post Sep 9 2019, 11:10 AM
Post#6


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


Hi. When you import an Excel spreadsheet into Access, Access tries to read the spreadsheet for data. You may see nothing but Access might see something else.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Sep 9 2019, 11:22 AM
Post#7


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


Let me clarify a little bit. When you import you specify if the first row has column names or not.

--------------------
Robert Crouser
Go to the top of the page
 
duggie
post Sep 9 2019, 11:27 AM
Post#8



Posts: 474
Joined: 14-October 12



Thanks for all the replies.

I have manually "cleaned" my data in Excel first by selecting all columns and rows beyond my range and right-clicked and chose delete.

When importing into Access, I ticked the box, first row are headers (because my Excel data does have headers).

Regardless, I ignored the errors (probably shouldn't! ) and proceeded with writing some queries in Access (manually).

What I have found is the queries that use linked tables run a lot slower than tables that don't.

But if I use non-linked tables, I would have to import the data from Excel using ADO and VBA, which is equally slow!

Any ideas?
This post has been edited by duggie: Sep 9 2019, 11:30 AM
Go to the top of the page
 
projecttoday
post Sep 9 2019, 12:06 PM
Post#9


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


Did you save the spreadsheet after you cleaned it up? Now you need to clean up the import. Do you see any columns in records 10 and 13 that didn't come across? (Assuming records 10 and 13 came across at all.)

--------------------
Robert Crouser
Go to the top of the page
 
WildBird
post Sep 9 2019, 04:54 PM
Post#10


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


Sounds like Excel has data somewhere in it.

Try copying the data you want, and pasting it into a new workbook and saving it, then try importing that new sheet.

Have seen 'phantom' Excel data before.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
projecttoday
post Sep 9 2019, 08:07 PM
Post#11


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


Yes, it could be hidden data. I asked earlier if there were hidden columns. But how much does this matter? How much do the import error messages matter? Isn't the goal getting the necessary data imported fully and correctly?

--------------------
Robert Crouser
Go to the top of the page
 
duggie
post Sep 10 2019, 03:19 AM
Post#12



Posts: 474
Joined: 14-October 12



Yes I did clean, saved, closed down, re-opened to check.

A quick check shows everything had been imported.

My worry of using this method is in future, if users mix data type in their Excel source columns, it could mess things up.

Go to the top of the page
 
projecttoday
post Sep 10 2019, 05:58 AM
Post#13


UtterAccess VIP
Posts: 11,079
Joined: 10-February 04
From: South Charleston, WV


Okay. I think the only way to fix an error during an import is to change the data or change the format of the data which causes the error before making the import. Your other option is Excel automation. Someone correct me if there is another option.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 07:48 PM