How to prevent datatype conversion when importing tables from excel????

[strayerstu]

I'm building a database at work to see if we can replace some of the "buhzillion" spreadsheets we currently use to track data on sites and employees across the country. I've set up the primary excel tables the way I want to import them, but Access keeps converting my Employee Numbers (mostly 7 digit numbers, "text" format in excel) to scientific notation during the import process and then giving me errors because my primary key "EmployeeNumber" has duplicates. How do I tell Access to import these as text instead of numbers? I'd really like to not have to type in data for 100+ employees and over 1000 sites, you know?

Thanks in advance.

 

[Liv Manto]

Have you tried exporting it to a ready made Access database structure?

 

[strayerstu]

I can't find the menu options to do that... How?

I've been using the import function in Access to do this (it works on other, non-numeric data).

Thanks.

 

[Liv Manto]

"errors because my primary key "EmployeeNumber" has duplicates."

You cannot have have duplicates in primary key.



Take off the primary key icon from your Employee Number.

What are the steps that you took? When you are importing the data in Access from Excel, it would ask you if you want access to add primary key, correct? let it add the primary key (which would be an autonumber.)


"Access keeps converting my Employee Numbers (mostly 7 digit numbers, "text" format in excel) to scientific notation "



I tried doing it in several ways, it doesnt generate the error. What excel version are you using?

If its an old one, maybe you can save it to another file with the current version?

If you must, send me the excel file through my email address which I will send to you by private message, because i cant download from the net directly.

 

[strayerstu]

I have office version 2000 at home and 2002 at work. This is what I've been trying...

In Access, create a NEW database and name it.

FILE >> Get External Data >> Import
In the dialog, navigate to my excel spreadsheet, select it, and click Import

In the import wizard:
» Show Worksheets >> employees east (from list) >> NEXT
» First row contains column headings >> NEXT
» Import into a new table >> NEXT
» EmployeeNumber column Indexed option select "Yes (No Duplicates)"
>> NEXT
» Primary Key (with "choose my own" I get the following message:
"Error creating primary key on field "EmployeeNumber" (Index or
Primary Key cannot contain a NULL value)")

(when I select "no primary key" I get the message that says not all of
my data was imported correctly -- and it turns out that the
alphanumeric employee numbers were ignored completely)
>> NEXT

My employee numbers consist of either 7 digits (#######) or 3/4 letters and 4 numbers (ABC#### -- old employee system).

My spreadsheet was originally sorted on employee number, which put the numbers on top and the alphanumerics at the bottom. When I tried to import it this way, it created the table with the EmployeeNumber field being a number datatype, and therefore would not import the alphanumeric employee numbers (although it did display the numbered ones correctly (1234567 instead of 1.0465e+006). Employees with the alphanumeric employee numbers were brought in with the EmployeeNumber field blank.

I tried it again with the alphanumerics on top, and it correctly gave the EmployeeNumber field a datatype of text, and it brought in all the employee numbers, but I'm still having two problems:
1) numeric employee numbers are brought in with scientific notation format (1.0465e+006) instead of 1234567, and
2) there are a number of blank rows at the top of my table that do not exist in my spreadsheet.

I figured out how I could use my original spreadsheet to import that table and then go into the table design to make changes, add my primary key, type in the alphanumeric employee numbers (there's only about 10 of us), etc. to get that table the way I want it. But that seems like an awful lot of work for something that should be automatic. Additionally, I have other sheets (job titles, badges with expiration dates, security clearances with expiration dates, certifications, training requirements and milestones, etc) that are all keyed on the employee number that are going to have to be imported to make this thing viable.

 

[iccaros]

in excel format the the cells as text (right click format text) then import

 

[strayerstu]

I did that after the first failed attempt. Formatting the excel cells as text made no difference during the import process. The only way I found to make a difference in the way access assigns the datatype (may not be the only way to influence, but it's the only one I've found so far) is dependant on what I make the first data field it sees. If it's alphanumeric, it is imported as text, if it's numeric, it is imported as a number. Thanks.

 

[strayerstu]

Workarounds...

I was able to import most of my data automatically and then tweak the last few manually. If this had been a table with thousands of records, however, that would have been unacceptable.

Liv did suggest that I try exporting the spreadsheet to a text file and then import the text file. I was able to get that to work (but there's still the question of why can't I do it straight from excel?).

Thanks.

 

[fuzzygeek]

The institute where I work just changed the employees identification from Social Security Numbers to Generated IDs that begin with 010. The programs I have that import from Excel to Access are going ape. I have found the following through this site and others:

1) Excel sees a "0" and perceives it as a number even if the cell is defined text.

2) Access looks at the first 10 or so rows to determine the type of field and defines it as a number if excel perceives it as a number.

3) Importing into a predesigned table gives you the text option, but numbers that begin with "0" are perceived by excel as numbers and you get an error.

4) If you attach the Excel spreadsheet, the field will show numbers that are perceived as text normally. Those perceived as number get #NUM in the field.

See the following from Microsoft:

http://support.microsoft.com/default.aspx?scid=kb;en-us;214233

Have you come up with any better solution than exporting to a text file before importing to Access, or putting a letter or ' infront of the number before importing and then stripping it of the letter or '?

 

[gyang]

I insert a row of fake data to the first row of my spreadsheet. The spreadsheet would look something like this.

PrimaryKey Field1 Field2 Field3
999999 xxxxx xxxxx xxxxxx (fake data)
1 1234 1234 1234 (actual data)
2 5678 5678 5678 (actual data)

This automatically forces Access to import the columns as text. Then I find the fake data and delete the row of fake data.

This tip was posted awhile ago by someone else on the forum, but can't find the reference to the link. It has worked wonders!

 

[neileg]

I do the same as gyang (it may have been my posting you saw).

The problem is that Access tries to guess the datatype based on the first few records, and it can get this wrong.

The two obvious options are the dummy first record or creating the table structure first and importing the data to that table.