Access Converts Datatype on Import from Excel

[bostonfrog]

I was trying to import an Excel datasheet with an EmployeeID column formatted as Text in Excel, and I want it to remain a text field in Access, because some of the EmployeeIDs have leading zeros that should not be truncated, e.g. 0023335. It imports EmployeeID as datatype double. But since the datatype textbox is disabled in the import dialog, I can't change its datatype. Thus, it won't import any records with leading zeros, and creates an error log table. All I could do was basically create a new blank table and cut and paste the cells from Excel. It worked, but was sort of primitive. Why does Access convert these text-formatted columns in Excel to datatype double on import? Anyway to get around this?
-- Michel

 

[PaulBricker]

Interesting. I just created a small .xls file and formatted the first column as text and the second as currency. Then I went and imported it into Access and (you are correct when you say the DataType option is greyed out) after the import, my field in Access was text and all my leading 0's were still in place. Double check to make sure the field in Excel is formatted Text. That's about all I saw.

Paul

 

[SgtJarrow]

if you really can't fix it at the source as Paul mentioned (and this is really the solution)...once imported, simply place 0000000 in the format field and then change the field type to text.

****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[OhioSteve]

On the first screen of the import text wizard, click the "advanced" option. I think you will find it helpful.

 

[PaulBricker]

Sorry OhioSteve, I don't see an advanced option in the import wizard. This is from Excel to Access. In Access, go to File...Get External Data....Import
and you get an import wizard but I don't see any Advanced option.

Paul

 

[OhioSteve]

After doing some tests, I see the source of our miscommunication.

My experience with importing files has been with .txt files. When you import .txt files using the import wizard, you CAN designate the data type of each column. In addition, you can click on the "advanced" button. This button allows you to develop and save "import specifications". An import spec is a set of parameters for importing a file. They are extremely handy because you CAN refer to them when you build macros for importing files. So if your app has to import the payroll file every two weeks, you can store the specs and refer to them easily.

I now see that if you are importing an .xls file, your options are painfully limited. You cannot choose the data type of the fields, and you can't work with import specs.

I can think of three possible solutions to this problem.

First, you could do the transfer through the clipboard. You can select an excel range, copy it, and paste it into the Access tables area. Access will make a new table for it. You can even paste directly into a table if the table is open. Of course, that technique would only work if you were actually there, clicking on the mouse.

The other alternative would be to save the .xls file as a .txt file. "Tab-delimited" would be a handy type of text file to use. Then you could use import specs when you imported the file.

A third alternative would be to save the .xls file as a .dbf file. This will make the file very easy to import into Access. However, I still don't think that you can designate the data types.