Problem importing XLS file to Access

[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.

 

[nickjar2]

I believe that Access determines the field type by the first cell in each column. Eg. In column 1, say the first cell is 10, and the rest of the cells are text ie. 10a amd 10b. Access will assume that all cells in that column are numeric (because of the 10). I think if you amend the first cell to be the same datatype as u want, it should work. Ie. change the 10 to 10a, import the data, then change the 10a back to 10. During the import process there is an option to save your import spec for later use. Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[jar251169]

Hello, your information is correct of course but in my case not all cells have the same type of data (number, alphanumeric, text, decimal number...), the first cell never will have the same data type that all other cells in the same column.
About import process (using "Import Spreadsheet Wizard&quot, data type list is disabled (step 4), is it normal?. Importing XLS file it isn't possible to save specification, only importing text.
As troubleshooting, i'm exporting from excel to CSV format and importing from access using "Import Text Wizard", in this case, yes, is possible to change data type. This works but is another step to a simple process.

Thanks in advance. Regards.

Delgado.

 

[nickjar2]

Delgado,

What I was thinking was to initially change the first cell in the spreadsheet to the correct data type. Then import the data using the wizard. During using the wizard, there should be an option to save your import specification. If u do this, next time you do an import, choose your import spec, and it should work ok.

About import process (using "Import Spreadsheet Wizard&quot, data type
list is disabled (step 4), is it normal? YES


Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[jar251169]

Hello, I'm sorry but there isn't option to save specification, not in "import Spreadsheet Wizard", yes in "Import Text Wizard".

About import process (using "Import Spreadsheet Wizard&quot, data type list is disabled (step 4), is it normal? YES --> You know where is this reported by Microsoft?, I need a confirmation to send to my manager.

Thanks in advance. Regards.

Delgado.

 

[paron]

If you have mixed types in a column, then I have found it best to go into Excel, format the whole sheet as text, and do the import. Otherwise, you get a lot of unimported records to clean up manually.

Then you can change the resulting data type in Access, which is pretty graceful at changing types. And, you can split fields to capture both types in one field or the other.

Of course, it is better not to have mixed types in a spreadsheet column, but we as a species are just not that consistent.

 

[KenCunningham]

Don't know if this will help, but I've got around such problematical data in the past by importing the data once, allowing all errors. Then I've deleted all the data out of the table and have been left with the design. Alter the design to your specification, then import the data again to the empty table.

Bit long winded, but it certainly worked for me. Hope this helps.

Everton indeed!