Problem importing from Excel !!!!!!!

[jar251169]

Hello, I'm having a problem importing an Excel 2000 spreadsheet from Access 2000. This book is simple, only a worksheet with data, first row is the column name, some columns but only one column has numeric and alphanumeric data.

Problem: From Access, menu File/Get External Data/Import; I select the Excel file and the "Import Spreadsheet Wizard" runs, well, this is the process: "Show Worksheets" -> Next -> "First Row Contains Column Headings" -> Netxt -> "In a new Table"....in this step the problem occurs. Access assign automatically data type for each field but it isn't correct for one field (numeric and alphanumeric data) and when the wizard finish I get some import errors. I can't to change datatype during the import process because this field is dimmed, disable, unavailable.

Searching in Access help about the import errors, it suggest to change datatype during the import process but this isn't possible or I don't find where to change it.

Is correct this behaviour (datatype field disabled)?, importing from TXT file is enabled. Why?. Any solution, any troubleshooting?.

Thanks in advance. Regards.

Delgado.

 

[scking]

You have a problem the I overcame a couple months ago. The real issue is that Excel does not properly identify text columns if the column has numeric data in it. Here is an Excel macro that will force columns to text by inserting the ' character prior to the value. This is merely a control character in Excel and does not affect what you actually see in the cells. Select the column that you want to be text and run the macro.

Note: In the

http://www.microsoft.com

knowledge base there is a item about this problem and it explains how to correct it manually with a couple keystrokes for each cell but I thought the programmatic approach more appropriate since I would have to do it for each cell in a multi-thousand row Excel file.

Sub Format_to_Text()
On Error GoTo HandleErr

For Each xCell In Selection
If Len(xCell.Value) > 0 And Left$(xCell.Value, 1) <> &quot;'&quot; Then
xCell.Value = &quot;'&quot; & xCell.Value
Debug.Print xCell.Value
End If
Next xCell
Exit_Proc:
Exit Sub

HandleErr:
MsgBox Err.Number & &quot;, &quot; & Err.Description
Resume Exit_Proc
Resume

End Sub

You can change the DataType only if you import the data and then Access controls the data type and you can make the changes. But, this frequently results in import errors that are not included in the imported data.

Steve King
Growth follows a healthy professional curiosity

 

[BoxHead]

If you sort the 'offending' column DESC in the excel spreadsheet, the text values should be brought to the top and then Access will recognize it as text.

If you don't have control over the sort order on the excel sheet, tell Access that it doesn't have column headings, it will import the field names and recognize everything as text. SInce you presumably know the field names, you could automatically delete this bogus record as another step in the import operation.

HTH John

Use what you have,
Learn what you can,
Create what you need.