Import txt files -> Text converted to number

[LeeIsAlive]

Hi All,

Can someone help me with the below problem?

I'm having a number of tables in Access which I want to populate with data from excel/txt. In my import files I have some columns which contain alphanumeric data, like 0001 or XYZ0.

I've defined these columns as text in the definition of my table, however whenever I'm importing the text file, it converts those rows that can be read as a number to a number prior to storing it in my table. Hence values like 0001 becomes 1. Which is absolutely not what I need.

Does anyone knows how I can prevent Access from doing this conversion? Why does it do the conversion anyway? I wouldn't expect any conversion since I defined the column as text...

When I import the same txt file into a new table (created during import, where I also define those columns as text) everything goes fine, 0001 stays 0001, but I don't want to remove my table and redefine it during the import everytime I need to import new data.

Thanks in advance!

Lee

 

[Mr. B]

Have you tried creating an Import Specification and using that to do the import.

When defining the Import Specification you can define the fields that are text and other types of data.

 

[LeeIsAlive]

Hi Mr. B

Thanks, that sounds promissing.
Though I can't find "Import Specifications" back in Access.. Can you direct me to it?

Thanks,

Lee

 

[LeeIsAlive]

Ok, I found the import specification (when saving the settings of an import, save as...). But this doesn't help me.

When I searched google for import specification on Access I saw pictures where you have the following:
Field information:
Field Name: Data Type: Skip:

However, I only see:
Field Name: Skip:

There is no option for data type, I guess because I'm importing into an existing table and the data type should be known. But eventhough Access knows the correct data type, it isn't using it correctly as per my initial post

 

[Mr. B]

When you are using the Import Excel data option in Access the First screen of the wizzard lets you indicate that the first row contains column headinga, if you click the Next button that screen lets you click on each of the columns of the data you are about to import and set the data type along with other options. You will need to click on the column you need to specify the data type for.

Be sure you save your Import Specification and you should be able to make this work.

 

[LeeIsAlive]

Thx, but I don't get the option to define the data type when importing into an existing table. That option is only available when you import into a new table.

I've read about people having similar issues (leading zero's dissapearing after the import). One of them mentioned to add a dummy row in which the first column contained text.

Very amazing, when I do that, MS Access will remove that field as a type conversion and add the others which are of type number. This dispite the fact that I explicitely defined that column as being text.

Looks to me like a huge bug on MS side. Why does their code decides to overwrite what I have defined explicitely as the data type for a given column and even rejects data that is in the correct text format because the majority of the data looks like numbers!?

 

[spikepl]

Did you actually, in the Excel file, set the format of all the cells in the column that gives you trouble to Text? If not then do that.

 

[Solo712]

When I import the same txt file into a new table (created during import, where I also define those columns as text) everything goes fine, 0001 stays 0001, but I don't want to remove my table and redefine it during the import everytime I need to import new data.

Hi Lee,
if you get two different results depending on whether you import into an existing or a new table then I would look first at the data type of the field of the receiving table. If it is defined as "Number" Access will still import the string cell from Excel if it looks like a number, i.e. there are no alpha characters in the entry. And it will convert into a number. If the field is "Text" then it will preserve the format of the data in the Excel sheet, i.e. 0001 in your case. So, all you probably have to do is to convert the field definition of the existing table.

Best,
Jiri

 

[Pat Hartman]

When importing from Excel (and importing in the absence of an import spec from text formats), Access analyzes the first 30 rows of data. If a column contains only numbers in those rows, Access Assumes the column should be defined as numeric. You can alter this in several ways depending on how automated the process needs to be. One way is to open the spreadsheet and select all the text columns and define them specifically as text rather than general. This usually solves the problem. Another technique is to insert a dummy first row that contains all x's in the text fields or all 0's in the numeric fields and valid dates in the date fields. You could also write OLE automation code to make these changes if you are comfortable with VBA. But, in the long run, the easiest, no-code solution is to always append to an existing table rather than import as a new one. This way, you get to specify the column data types ahead of time.

 

[LeeIsAlive]

Hi All,

Many thanks for your replies. It solved my problem in the end!

If I use excel (vs txt) and I specifically define the columns as text (vs general), access is now importing correctly my data. A life saver!

I still find it strange that MS Access would look at the source format rather then giving priority to the format of the columns as how it is defined in Access. Perhaps something they can look at in the future...

Best regards,

Lee