Import Data - Scientific Display of Numeric Text

[Lisa]

I am trying to import a worksheet from Excel to an Access 2002 database
table. The Excel worksheet has roughly 30 columns of data and about 26,000
rows. Several columns contain various identification numbers with both
numeric & alpha numeric data. I formatted the Excel columns that contain ID
information to Text. In Access Table Design View the Data Type for these
fields is also Text. Despite Text to Text import, Access converts he the
number data to Scientific. For example, 398755597 is changed to 3.98756e+008
(which is not unusable.) I tried to Filter Sort on the original number
(398755597), however do not get a result. I put a dummy record in the 1st
row of the Excel worksheet containing alpha numeric data in each of these ID
type fields to try help Access determine that text is the appropriate data
type. I have tried everything I can think of to correct this. Can someone
help?

 

[Phil Smith]

Here is an Idea. Add a dummy data row into your spreadsheet, just under
the headers. Put obvious text, "DeleteMeNow" for instance, in those
fields. Access will not have any choice but to use text to import the
first row, and the other rows should follow.

Phil

 

[j.mapson.nurick]

Alternatively, prefix the values in the Excel cells with an apostrophe
'. This doesn't show up in the worksheet, but forces Excel and Access
to treat the values as strings of characters, not numbers.

 

[pietlinden]

Did you try specifying that those columns were text in the Import
Specification? (File, Get External Data... and when you go to
Advanced, make sure that field is set to Text and not numeric.)

 

[j.mapson.nurick]

The OP is importing from Excel files, not text files.

 

[Lisa]

John, I already added a first record to try and "trick" Access into setting
the correct data type but that does not work. On the one record that I used
the apostrophy on, it fixed the problem. The challenge now is to identify
every field on every record that is affected. Any suggestions on the best
way to do that? Lisa

 

[Lisa]

Where should I be getting the option to select "Advanced" during the import
(as I don't ever see that as an option when using the import wizard)??

 

[John Nurick]

Hi Lisa,

Here are two Excel macros to add or remove apostrophes to/from cells
in the selection.

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub


Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

 

[John Nurick]

Up to Access 2007 there was no way of selecting data types when
importing from Excel. Pietlinden was confusing it with importing from
text files.

 

[Lisa]

John, Thanks for your help. I was able to correct the scientific notation
with your suggestion of adding the ' in front of the number (by using a
custom number display.) Now I'm struggling with some other issues with
zero's (in custom displays) that disappear during the import but will start a
new thread if I can't get it figured out. Lisa