Importing Data from excel

[Guest]

Could someone please give me some direction on importing freom excel? I have
account numbers that start with zero and everytime I try to import the data I
lose the leading zero. How can I maintain the full account number?

 

[Guest]

I always add a dummy first record in the excel file I am importing. Since
the filed you are having problem is a number Access is converting it to a
neric field type. In you r dumm record add text like aaaa for that field.
Access will assume the rest of the data in that field is alpha.

 

[Keith Wilby]

Could someone please give me some direction on importing freom excel? I
have
account numbers that start with zero and everytime I try to import the
data I
lose the leading zero. How can I maintain the full account number?

It depends on what method you're using but it's down to a type mismatch.
Your source is text and your destination would appear to be number - change
it to text.

HTH - Keith.
www.keithwilby.com

 

[John Nurick]

The best way I know is to prefix these "numbers" - better to think of
them as strings of digits, because you're not going to do arithmetic
with them - with apostrophes, e.g.
'0034989
instead of
0034989

This doesn't affect the way they're displayed in the Excel worksheet,
but it ensures that both Excel and Access always treat them as strings
and preserve the leading zeroes.

These two little Excel macros add or remove apostrophes to/from the
selected cells.

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

 

[Guest]

I just did a quick test. I created a spreadsheet with one column. Formatted
the column to TEXT then added in the numbers 001, 002, etc. Imported into
Access using the menu and it was fine.

Lauri S.