converting format from number to text during import

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
Joe Fallon said:
You can't do it during the import.
You have to do it after.
Click to expand...

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.
Click to expand...

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).
Click to expand...

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

Jamie.

--
 
Back
Top