Change datatype when importing Excel file into Access

Question

Is there any way to change the default datatype when importing an Excel file into Access? (I'm using Access 2003, by the way).

I know that I sometimes have the freedom to assign any datatype to each column that is being imported, but that could only be when I'm importing non-Excel files.

EDIT: To be clear, I understand that there is a step in the import process where you are allowed to change the datatype of the imported column.

In fact, that's what I'm asking about. For some reason - maybe it's always Excel files, maybe there's something else - I am sometimes not allowed to change the datatype: the dropdown box is grayed out and I just have to live with whatever datatype Access assumes is correct.

For example, I just tried importing a large-ish Excel file (12000+ rows, ~200 columns) in Access where column #105 (or something similar) was filled with mostly numbers (codes: 1=foo, 2=bar, etc), though there are a handful of alpha codes in there too (A=boo, B=far, etc). Access assumed it was a Number datatype (even after I changed the Format value in the Excel file itself) and so gave me errors on those alpha codes. If I had been allowed to change the datatype on import, it would have saved me some trouble.

Am I asking for something that Access just won't do, or am I missing something? Thanks.

EDIT: There are two answers below that give useful advice. Saving the Excel file as a CSV and then importing that works well and is straightforward like Chris OC says. The advice for saving an import specification is very helpful too. However, I chose the registry setting answer by DK as the "Accepted Answer". I liked it as an answer because it's a one-time-only step that can be used to solve my major problem (having Access incorrectly assign a datatype). In short, this solution doesn't allow me to change the datatype myself, but it makes Access accurately guess the datatype so that there are fewer issues.

Cleanthes

Answer 1

This may be caused by Excel Jet driver default settings. Check out the following registry key and change it's value from default 8 to 0, meaning "guess column data type based on all values, not just first 8 rows."

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"TypeGuessRows"=dword:00000000

Please, tell if this works.

Muricate

Well, it does work in that Access is now guessing the datatype more intelligently. However, I still do not have the power to change the datatype to whatever I please during the import process. I do realize that I may have no other option, though. If that is the case, I'll accept this as the answer. – Cleanthes

Answer 2

There are a couple of ways to do this. The most straightforward way is to convert the .xls file to a .csv file in Excel, so you can import into Access using the Import Text Wizard, which allows you to choose the data types of every column during the import.

The other benefit to doing this is that the import of a csv (or text) file is so much faster than the import of an xls file. If you're going to do this import more than once, save the import setup settings as an import specification. (When in the Import Text Wizard, click on the "Advanced..." button on the bottom left, then click on "Save As" and give a specification name to save the changes you just made.)

Overbear

Answer 3

open your excel file. In Home tab change format from General to Text. then import into access

Commanding

Hi Deepak. Your answer lacks details and examples. Please check on how to write awesome answers here : stackoverflow.com/help/how-to-answer – Masjid

Answer 4

Access can do what you need, but there is no straightforward way for that. You'd have to manage some recordsets, one being your Excel data, the other one being your final Access table. Once both recordsets are open, you can transfer data from one recordset to the other by browsing your Excel data and adding it to your Access table. At this stage, it will be possible to change datatype as requested.

Alcatraz

Answer 5

When importing from CSV files you can also have a look at schema.ini you will find that with this you can control every aspect of the import process.

Merriott

Answer 6

Access will let you specify the datatype at the import process. the problem is at the "Append" process for the following times, it will not ask about the import to datatype, and it will forget you changed it. I think it is a bug in MS Access.

Equestrian

Answer 7

This is an old post but the issue persists! I agree with Deepak. To continue that thought:

Access determines the field types when linking to or importing Excel files based on the field types in Excel. If they are all default, it looks at the first X rows of data. A few ways to fix this:

1. Open the Excel file and add about 6 rows (under the field headers if any) that emulate the type you want. If you prefer all text, enter 'abcdef' in each cell of those first six rows.

2. Open the Excel file, highlight all cells, right click, and change format to 'Text' or whatever format you like. Save, then link/import into Access.

3. Use a macro (VBA script) to do step 2 for you each time:

Public Function fcn_ChangeExcelFormat()
On Error GoTo ErrorExit

Dim strExcelFile As String
Dim XL_App As Excel.Application
Dim XL_WB As Excel.Workbook
Dim XL_WS As Excel.Worksheet

strExcelFile = "C:\My Files\MyExcelFile.xlsx"

Set XL_App = New Excel.Application

Set XL_WB = XL_App.Workbooks.Open(strExcelFile, , False)
Set XL_WS = XL_WB.Sheets(1)  '  1 can be changed to "Your Worksheet Name"

With XL_WS
   .Cells.NumberFormat = "@"  'Equiv to Right Click...Format Cells...Text
End With

XL_WB.Close True
XL_App.Quit

NormalExit:
Set XL_WB = Nothing
Set XL_App = Nothing
Exit Function
   
ErrorExit:
strMsg = "There was an error updating the Excel file!  " & vbCr & vbCr & _
         "Error " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "Error"
Resume NormalExit
End Function

Harrold

Answer 8

Access will do this.

In your import process you can define what the data type of each column is.

Underset