problem with numbers when importing txt file into Access

[Atoga]

Hi All,
I need to set up a linked table to my Access database, which links to a txt file.
When I import the txt file some numbers appear with zeros in front of them (as they also appear in the original txt file) in Access, for example 009040. I would like to import this file in a way that these numbers appear without the zeros, so instead of 009040 I would like to see 9040. There are other numbers that appear in Access fine, without the zeros in front of them, only the shorter ones seem to have this issue with the zeros.
I used to work with this data in Excel where I just converted these numbers stored as text to general format and this has solved the issue, the zeros disappeared, but I don`t know how to do this in Access. I could edit the data first in Excel and then import it into Access, but I hope there is a way Access can handle this and I don`t have to do it each time. Can anyone help me?
Cheers

[CJ_London]

what is the field type you are importing into? if it is numeric, the preceding zeros will be ignored. Any number with a preceding zero will not be a number type, but a text type.

[Atoga]

The field I am importing into is Short Text data type, because my original data is mixed (I have codes in that field, such as AJT005, BNHT, 9400). So unfortunately it does not ignore the preceding zeros....so how can I sort this out? Any help appreciated, thanks!

[CJ_London]

so you would want

AJT005, BNHT, 009400

converted to

AJT005, BNHT, 9400?

if so, then something like

iif(isnumeric(myfield), val(myfield), myfield)

but if you are using transfertext this will not be an option. You would need to link to the text file and import by creating an insert query to insert data from the linked table to your destination table

[ItsMe]

I vote for temp table. Use all text fields in the temp table and the appropriate data types in the production table. Use VBA to validate data within temp table before appending production table.

[NTC]

so just to be clear; in Access 009040 means that Access perceives the field to be a text field. Depending on one's situation you could:
* in excel; explicitly format that column as a number type, before the Access import
* in Access; during the import wizard set up - identify that column as a number field
* in Access; after import of text - change the table field type to number - it will warn you that data may be lost - - I'm actually not sure as I type this whether it will change it to 9040 or delete it totally - try it and see
* in Access; after import of text - set up a new column and then make an Update Query to Convert to Number (use to be called Cast) using a calculated field.

[CJ_London]

so just to be clear; in Access 009040 means that Access perceives the field to be a text field. Depending on one's situation you could:
* in excel; explicitly format that column as a number type, before the Access import

This is why I prefer text files to Excel. With text files it is perfectly clear what you are getting, text or number. With Excel, maybe, maybe not.

See this example - in A1, it is definitely a number - but formatted to have the preceding zeros. In B2, it is a calculation formatted as text - (in excel, the text function is the equivalent of the access format function)


now in cell c1, type in 009040 - and Excel strips off the preceding zero's, leaving a number. If you want to type in with the preceding zeros you have to type '009040

My point is for importing, formatting the excel column as a number will have no effect - it has to be a number - so you need to look at the actual values in the cells, not what they 'look like'. If you had a decimal number 12.3456, formatted as 12.35, the value imported will be 12.3456

* in Access; during the import wizard set up - identify that column as a number field

yes - depending on what is in the first few rows (I think it is 16 rows) Access will make a decision about what type of data it is bringing in, identifying the column as numeric forces it to consider it to be a number - but you said in your original post you have text values such as AJT005, BNHT - so forcing the column to be numeric will mean these values will be rejected.

* in Access; after import of text - change the table field type to number - it will warn you that data may be lost - - I'm actually not sure as I type this whether it will change it to 9040 or delete it totally - try it and see

only necessary if you have not identified it as number in the import wizard

* in Access; after import of text - set up a new column and then make an Update Query to Convert to Number (use to be called Cast) using a calculated field.

only necessary if you have not identified it as number in the import wizard

[Atoga]

Thanks all, finally I left all stockcodes in the format they were imported originally as I realized that all my other documents in Access have the stockcodes in this format (they all are imported from csv and txt files) so I can actually work with the stockcodes in this format, set up relationships between them. I will deal with the numbers stored as text whenever I need to export data from this Access database into Excel.