Problem importing Excel data into Access using TransferSpreadsheet 1

[EdRev]

I am trying to import data from an excel file into an existing ACcces table. I defined all the fields in the table as text datatype, hoping that data (like date and numbers) will not be reformatted in the table. I was wrong, excel date as 04/14/2005 was imported as 38456 (???) and number as 1392782 came in in scientific notation as 1.39278e+006.

In turn, I have to query the table and spit out multiple excel files. Now I am afraid on what the excel file will look like.

Any help will be greatly appreciated.

 

[telephoto]

I've played with this many times in Access 97, so the method should work for you.

As a principle, import the data to a temporary table, you can then check it for errors before transferring the data to its proper table.

In the excel sheet, set row one as field names - these must be unique on the worksheet and identical to the temp table field names.

Set data type in the access fields for the expected type of data

In row two of the spreadsheet enter typical values. For a text field use "text" for a number field use 123 for a date field use 25/12/2006 or 01/04/2007

Import the data.

Errors in data type will be flagged in a separate table. Open this and check which row and column is causing the error. Go back to the spreadsheet and correct it.

Delete all data in temp table, delete the error table, and then reimport, continue until you have no further error table generated.

Transfer the data to its proper table. Job done.

If this is a regular job then it may be an idea to set up a macro in Excel to check that the data in the columns is correct before you start the transfer.


HTH

Telephoto

 

[EdRev]

Thanks for the reply.

I do not have anymore problem importing the date field. Though I am still having problem importing one column with a "general" excel format.

I set the text data type in access for this field. I did not have any problem importing 30 records. The column (mixed characters and number)imported without errors. But when I tried 100 records, only the numeric data was imported, I got Type conversion error on the alphanumeric data. I have to import almost 60,000 rows "as is".

 

[telephoto]

Have you tried setting the Excel column format as text?

Right click on the column letter and select format, choose text.

If the first row is field names and the second is "text" then the import should go OK

Telephoto

 

[EdRev]

Yes I did. The 99999999 values came in as scientific notation (1.000+e6 or someting like that) and the alphanumeric did not import at all.

 

[Reuel]

I used to have this problem all the time.

It was long ago, but I think the best way around all those goofy troubles was to create a linked table directly to Excel, and then extract the data using VBA to my Access table.

 

[KornGeek]

On the text field where it is only importing numeric text, this is most likely because the first record is numeric. Go to the Excel spreadsheet and type an apostrophe (') character in front of the data in the first record. This tells Excel that the contents are text, and Access with then import that field for all records as text.

 

[EdRev]

Thanks for all the replies.

KornGeek, it worked. All data were imported and no import error table created.

Originally, I have three excel files being e-mailed to the end-user. When I tried to import the files into Access, I got "error 3274 external table is not in the expected format". I saved it as FileType MS Excel 97 - Excel2003 & 5.0/95 Workbook and import the file again to Access, it worked.

Is there a conversion issue? HOw can I resolve this using VBA?

Thanks again.