MS Access - Some fields are not imported in the table

Question

MS Access - Some fields are not imported in the table

Anonymous

Hi,

There is a bug in Ms Access when importing the text files into a table.

I have a pipe delimiter text file separated into 3 columns and the file has 26 records in it. The first 25 records in the text file have blank field in the third column (or field) and make sure the 26th record has a value in third column. Copy the text below in a text file to reproduce the error.

NoError|24137|

ErrorField|577|015RECORD

Note: ErrorField is the 26th record in the file which has the value populated in 3rd column as ' 015RECORD'.

Right click on any table from 'All Access Object' and select Import > Text File.
Select the text file and click OK
Select 'Delimited' option and click 'Next'
Select 'Other' and enter the pipe '|' symbol (The first 25 records are displayed in the box and you can see only two columns are available in the box)
Click Next
Click Finish

Open the imported table and note that the value '015RECORD' from the 26th record is not displayed in the table. It is because the file is imported with only 2 columns eliminating the 3rd one, as there are no values present in the 3rd column for first 25 records.

The issue is not specific to pipe delimiter. We are facing a lot of problems due to this issue. The only workaround here is to cut and copy the 26th record to the first line. This resolves the issue. But this is just a temperory workaround.

Regards

Basha

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Answer 1

ScottGem 68,830 Volunteer Moderator

I just tested this. You are correct, once you save the specs, it does seem to revert. However, I created a macro and VBA module to do an import of text and specified the Specification name I saved and it imported correctly.

0 comments

Answer 2

Scott, I am sure it is a bug. Because I have tried the Advanced import specifications option when importing the file. In the beginning it shows just 2 fields (Field 1 & Field 2), then I have added Field 3 manually, saved the settings and clicked OK (Refer the below image).

I clicked on Advanced button again to verify the changes. Only the first 2 fields are displayed again.

You can able to recreate the issue if you use the above data when importing.

Answer 3

ScottGem 68,830 Volunteer Moderator

This is not a bug, and the way around it is to setup an Import Specification. Do one import manually and use the Advanced button to create an Import specification that indicates the three fields. That should import correctly. The Import wizard looks at the first x records to determine how to import.

0 comments

Answer 4

Anonymous

Can you please brief it. I think you misunderstood the bug.

0 comments

Answer 5

Anonymous

Then your source file should have nulls indicating 3 column and row should look like:

|Column2_Value|Column3_Value

This would avoid data mismatch error for columns.

0 comments

Share via

MS Access - Some fields are not imported in the table

8 answers