Trouble importing Excel to Access

[kelly1]

I am trying to import an Excel Spreadsheet into Access as a new table but I’m having a problem with one column.


Some entries are being displayed incorrectly as example:
1.01173e+009

The proper format should be as eaxample:
321040989454

In Excel and Access the columns are formatted as ‘Text’

Can anybody give me any ideas as to what could be wrong please?

Regards

Kelly

 

[SydneyGeek]

Go through the import process again. When you get to the stage in the Wizard where you can include / skip fields, use the navigation arrows to go to the problem field. It's probably coming in as a Double (Excel's default data type). Dunno why -- sometimes Access ignores the Text data type when you bring in data from Excel.
Have you tried saving the Excel sheet as a .csv?

Denis

 

[kelly1]

I tried saving as a CSV but the problem is still there. It seems to be the way Excel is exporting it.

Is there a way to prefix the numbers with a letter to make it an obvious text data with find and replace using wildcards, then remove them afterwards?

Its not happening with any other column, just the one with 12 reference numbers.

Kelly

 

[bat17]

You may need to create your own VBA eport routine to force Excel to formalt it the way that you want it.
You could also add a prefix/suffix either by creating a coderoutne to do it for you or just by adding a column joining the data with a letter there and then paste back over the original data. In Acess you could use an Update query to strip your suffix.
The method to choose will really depend on how often you have to do this.
You dont say what methods of import that you have tried in Access. Sometimes just pasting the data or linking to it will work when the wizard fails.

HTH

Peter

 

[jagsdj]

Hello...totally new to this forum and VBA.

I'm trying to get the specifics of exporting a excel file into a access table either via a access form or routine(?).

Any and all help is greatly appreciated.

Thanks

 

[NitherWise]

Kelly,

In Excel you can put a single quotation mark in front of the value and it will be interpreted as text. For example: '321040989454 instead of 321040989454.

If that doesn't work or if there are too many rows to manually insert a single quotation mark, try copying the column and doing a Paste Special->Values to get excel to save it as text.

There is also an Excel feature under Data->Text To Columns that can convert the data in the column to all text. This works for me sometimes.

Hope this helps.

 

[jobb]

in Access, the only way I can get those to show correctly is to import them as numbers but with the following specs:

Field Size: Double
Format: Fixed
Decimal Places: 0