converting format from number to text during import

[Guest]

I have a bunch of excel files that need to be importing into access, but on
some of the fields (in the excel files) are missing the leading "0"'s? How
can add these back in during my import so they match the rest of the data in
the table already? Any help would be greatly apreciated.

PS the fields are in text format in access.. how can i turn 39 into 0039
during import?

 

[Joe Fallon]

You can't do it during the import.
You have to do it after.

Import to a staging table.
Then write a query to append data from that table to the real table.

In your query you can append the leading "0".
If you need 2 leading zeroes sometimes then you have to get a bit fancier
and come up with a rule for determining it.
e.g.
If I have 2 digits then add 2 leading zeroes.
If I have 3 digits add 1 leading zero.
If I have 4 or more then do not ad a zero.

You can code this as a VBA function (or a nested IIF).
Then when you append, you should have the right number of zeroes.

Tip - run Select queries untiul the staged data look 100% correct.
Then run the append.

 

[Jamie Collins]

You can't do it during the import.
You have to do it after.

Depends how you are doing the import (the OP didn't specify). For
example, here's a way to do it during the import:

INSERT INTO MyJetTable
(MyTextCol)
SELECT
FORMAT(MyIntCol, '0000') AS MyTextCol
FROM
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MyWorkbook.xls;].[Sheet1$]
;

Import to a staging table.

No need for this.

In your query you can append the leading "0".
If you need 2 leading zeroes sometimes then you have to get a bit fancier
and come up with a rule for determining it.
e.g.
If I have 2 digits then add 2 leading zeroes.
If I have 3 digits add 1 leading zero.
If I have 4 or more then do not ad a zero.

You can code this as a VBA function (or a nested IIF).

Using the FORMAT function as above avoids the need for 'fancy' nested
IIFs and VBA.

Jamie.

--