Access import function problem (1 Viewer)

murph_123

New member
Local time
Today, 16:28
Joined
Aug 3, 2007
Messages
8
I am trying to import a excel spreadsheet ( 3,000) rows into a access database. It has been working finr for over a year. When I do the import it gives the following message: I have attached part of the excele spread sheet.

'Master 7-20-07$'_ImportErrors Error Field Row
Type Conversion Failure pID 161
Type Conversion Failure pID 162
Type Conversion Failure pID 163
Type Conversion Failure pID 164
Type Conversion Failure pID 165
Type Conversion Failure pID 176
Type Conversion Failure pID 177
Type Conversion Failure pID 180
Type Conversion Failure pID 181
Type Conversion Failure pID 182
Type Conversion Failure pID 183
Type Conversion Failure pID 184
Type Conversion Failure pID 185
Type Conversion Failure pID 186
Type Conversion Failure pID 187
Type Conversion Failure pID 188
Type Conversion Failure pID 189


Can anyone tell me what is happening. I cannot even paste the spreadsheet into the DB.

Thanks Brian
 

Attachments

  • Master 7-31-07-andy.zip
    48.8 KB · Views: 137

Moniker

VBA Pro
Local time
Today, 15:28
Joined
Dec 21, 2006
Messages
1,567
Those rows (161-165, 176, 177, etc.) all contain text along with numbers in the pID column. For example, row 160 is 17840 for pID while row 161 is BCSM-110.

You are trying to import those with alpha-numeric characters into an Access database where Access is told to expect numbers only. Change the data type of pID in your Access database to Text, and you'll be fine.

Note that by doing that, and depending on how you use pID, you may break seemingly unrelated processes. For example, if you have another query where you do something like "WHERE pID = 12345", that will need to change to "WHERE pID = '12345'". This is because pID is now text and must be treated as much.
 

murph_123

New member
Local time
Today, 16:28
Joined
Aug 3, 2007
Messages
8
Changing pID to text

how do I change the pID column to text?


Thanks Brian
 

murph_123

New member
Local time
Today, 16:28
Joined
Aug 3, 2007
Messages
8
Still having probolems

I changed the pID to text by going into design view and highlighting the column and changing pID to text. It now will copy over all 4267 rows but only some of the pID's ???? What should the column in the excel spreadsheet be set to?

Brian
 

Moniker

VBA Pro
Local time
Today, 15:28
Joined
Dec 21, 2006
Messages
1,567
Set the Excel spreadsheet to match the Access DB. Highlight column A (your pID in that example spreadsheet), press Ctrl-1 to open the formatting options, and set it to text.
 

murph_123

New member
Local time
Today, 16:28
Joined
Aug 3, 2007
Messages
8
still not working

Can I zip the spreadsheet and send it it ya... if so I would need an email
 

murph_123

New member
Local time
Today, 16:28
Joined
Aug 3, 2007
Messages
8
little progress

I tryed to cut and paste the Spreadsheet not import and received a mesaage that the fiels is too small to paste the amount of data? Need help

Brian
 

neileg

AWF VIP
Local time
Today, 20:28
Joined
Dec 4, 2002
Messages
5,975
You have a problem that often crops up when you mix Excel with Access. You see Excel doesn't really support the idea of datatypes, whereas Access depends on it. You can try and set Excel columns to different types, but most of the time it will simply accept any old rubbish. Access gets pretty upset if you try to mismatch datatypes and most of the time it won't accept them, especially on import.

As Moniker has pointed out, your PID column is a mix of text and number (masked by the fact that you've set it to be left justified). Unfortunately, formatting as text won't sort this out. The only way you are going to get over this is to force the numeric data to be text. You could do this by prefixing the numeric data with an apostrophe in Excel (').
 

murph_123

New member
Local time
Today, 16:28
Joined
Aug 3, 2007
Messages
8
Any more ideas

Does any one have any ideas why I cannot import a field from excell to access? I have been doing it for over a year. Both column are defined as numbers.

Need help

I can send the excel spreadsheet if you like
 

Moniker

VBA Pro
Local time
Today, 15:28
Joined
Dec 21, 2006
Messages
1,567
Use a free online storage center (search Google if you don't like my suggested one), upload your file there, and paste the link. Not trying to be paranoid, but I don't like giving out email.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:28
Joined
Sep 12, 2006
Messages
15,614
are you sorted now, murph?

the problem is, is that access gives you little control over excel imports. it seems to guess the type of data in the excel column, and often gets it wrong, and you cant fix it.

if your access data is currently numeric, then it might be dangerous just changing the data type of the access column to text, as other things may stop working.

you perhaps need a validation stage after the initial excel capture to verify that all is correct, or to manually inspect the excel file first.

the problem arises the other way also, as has already been noted - ie its hard to force an excel column to be treated as text, if there are only numerics in there.

neil suggested prefixing the cells with the apostrophe - you can also just put non-numeric text in the first data cell in the column ,but then you have to disregard that row. you can also save the excel sheet as a csv, then you can use an import spec, and get much better control.
 

neileg

AWF VIP
Local time
Today, 20:28
Joined
Dec 4, 2002
Messages
5,975
Hi gemma. I'd forgotten the trick of using a CSV as an intermediate file type. It has saved my bacon in the past!
 
Last edited:

murph_123

New member
Local time
Today, 16:28
Joined
Aug 3, 2007
Messages
8
DId you have a chance to check the file

Use a free online storage center (search Google if you don't like my suggested one), upload your file there, and paste the link. Not trying to be paranoid, but I don't like giving out email.


Did yoiu have a chance to look at the file ?

Thanks
Brian
 

Moniker

VBA Pro
Local time
Today, 15:28
Joined
Dec 21, 2006
Messages
1,567
Here you go.

There are several issues that needed to be resolved.

1) While okay for an Excel file, as a DB, this is highly denormalized. Study up on normalization.
2) The main thing I suggested, "Change pID to text" was never done. I did it.
3) I generically created a new table and set the field types that I wanted instead of using an importer. Then I just copy/pasted. There were still some paste errors -- around 100 -- but this is due to abnormalities in the data.

In the file below, there are two files. One is the DB I made with the data imported, and the other is your Excel sheet with formatting corrections in it. Try importing it yourself and you'll see the import errors and what is causing them.

Download this (around 1.8MB)
 

Users who are viewing this thread

Top Bottom