Unparsable Record Issue - txt file import into Access

[PaulDixie]

Hi,

I've never had troubles within importing data into Access via a txt file, but I have one file which is causing me a significant amount of frustration and I'm hoping someone will know the answer!!!

I am trying to import a txt file, but continue to get Import Errors with 'Unparsable Record'.

The text file has approx 38,000 rows and 35 columns and the unparasable record row is always 30,592 - after ROW 30,591 nothing imports into the table. The specifications are Delimited and fields are seperated with an '=' i.e.

'706259=ABC=3 ABC Road=01202565858=Yes===No'

I thought at first it could be a data issue, but if I strip out the first 30,591 ROWs and import the remaining ROWs that won't import, it works fine and all the data is in the right place. All rows have '===' which seperates into a few blank columns, but again it works for all the first 30,592 rows and not the remaining.

Any Ideas why this is happening?

Thanks
Paul

 

[Joe4]

Could be bad data on row 30591 or 30592.
What happens if you remove JUST those two rows and try importing the file?
If that works, then there is a data issue with those particular rows.

 

[PaulDixie]

Hi - Thanks for coming back to me.

This is the strange thing - I could understand if it was bad data, but if I remove these rows, it stops at the same point with a different record.
The other strange thing is that if delete all the data (only leaving the data in ROW 30591 and 30592 - which would then be ROW 1 and 2) - it uploads fine....!!!

So I just can't understand the issue!?

 

[Joe4]

Its really hard to say without being able to play around with the data file.
Sometimes, things like delimiters and text qualifiers can cause problems, if they are also used in the data itself.
Or, sometimes extra delimiters, or special characters can cause problems.

What happens if you break the text file into 2 or 3 files are try importing those?