Excel to Access

[vb707]

Excel to Access

--------------------------------------------------------------------------------

Hi!

I was importing the large array of accounting data into Access from Excel through File - Get External Data - Import. Several columns contained tangible accounting data (e.g. registration numbers) that had different formats like "23423 34", "32-3545" and in some cases even contained letter constants. Therefore, I have assigned TEXT type to this column while importing and was sure that all values are going to be stored as text. However, what has happened is: 18307769 turned into 1.83078e+007! this means tangible accounting data that was used as a key field for further linking turned into bull&&$%! :-(

I know this problem is very simple, so please, can you give me a hint as to how do I solve it in the future.

Thank you in advance.

--
Regards,
vb707
SecureMarket: credit card processing

 

[Remou]

As far as I recall, Access picks the format of Excel columns based on the first 30(?) rows, regardless of what you do in Excel. I think the best bet is to import into an existing table structure.

 

[vb707]

I have 30 columns and 20 tables... Do you mean I have to manually create a table with column headings separately for each table and then process import operation?

 

[Remou]

Here is one idea:

'Link spreadsheet
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tmp", "Test.xls", True
'Copy structure
strSQL = "SELECT tmp.* INTO impExcel1 FROM tmp WHERE 1=2"
CurrentDb.Execute strSQL
'Change relevant column
strSQL = "Alter Table impExcel1 Alter Column Field1 Char (30)"
CurrentDb.Execute strSQL
'Then import
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "impExcel1", "Test.xls", True
'Get rid of linked tmp
DoCmd.DeleteObject acTable, "tmp"

 

[oharab]

Probably an even easier way is to save your excel data as CSV and import that into Access. Importing text files will bring up the wizard to allow you to define fields as text or number or date, etc. You can then save this specification and use it over and over.
When you import from Excel, Access decides it is way cleverer than you, so makes all the choices for you! IMO the singular most annoying feature of Access!

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.