MS Access: Unparsable Record

Factual Questions
1

I’m trying to import a text file into Access and I keep getting the error “unparsable record”. The help file says that this is caused by the appearance of the delimiter in the field, but this is simply not the case. I can not for the life of me figure out what is wrong with this particular record. The record loads just fine into Excel but Access refuses to take it. I’ve even tried saving just that one record as an Excel file and importing the Excel file into Access – no luck, same error.

Has anyone seen this before? What can I do to fix this error?

Thanks in advance!

2

Can you post the record in question, and other records from the same file that do work?

Alternatively, can you import it into Excel and then move it from Excel to Access?

3

The most I can figure is that there’s some non-alphanumeric ascii character somewhere in there. If you’ve got a hex editor, check it and make sure there’s nothing unusual.

4

No clue what the problem is. I have a potential solution though.

Print (or otherwise preserve) the row that doesn’t work. Delete it from the the file, then retype it in manually. If it works now, you had some weird character (chr(0)? chr(255)?) in there. If it still doesn’t work, then it’s not an unprintable character, it’s an invalid character.

In such a case, if you post it here, I’m sure we’d be able to figure out the invalid character.

5

Is this a one-time problem?

I would load the record into Excel and then cut and paste it field by field into Access. You may find out what field is giving the record that way. In any case, you will have the record in Access.

6

Here’s the record. As you can see there are a large number of fields (it’s not my data - I know it’s not normalized, I have to live with it). I’ve also included a record which loaded successfully.

I really can’t do it manually. There are just too many fields and too many errored records to make that feasible.
Good record:

HCGS830228LB,5189,Site 36 ABN AMRO/AASC,No,National Frame Relay Service,ICB,A,RC: Access Charge (high speed),175,RC: Port Charge (high speed),267.4,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),3,8K,RC: Standard PVC (high speed),9,128K,RC: Standard PVC (high speed),11,256K, , , , , ,Y
Bad Record:

HFGS794598LB,N25220,DOC-DR1-STATEVILLE R&C,PON 62450,Yes,Managed Router Service (Frame Relay),RC: AADS Local Loop (DS3),980,RC: SOI Port Charge - DS3 (high speed),3675,RC: Additional PVC/DLCI - DS3 256K CIR,45,310 HFGS789464LB 310,Additional PVC/DLCI - DS3 256K CIR,RC: Additional PVC/DLCI - DS3 256K CIR,45,744 HFGS789515LB 744,Additional PVC/DLCI - DS3 256K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,099 HCGS800980LB 266,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,410 HFGS789514LB 410,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,800 HCGS798826LB 800,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,801 HCGS802983LB 801,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,802 HCGS810009LB 802,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,803 HCGS801142LB 803,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,804 HCGS801144LB 804,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,820 HCGS791232LB 820,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,823 HCGS803127LB 823,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,824 HCGS802454LB 824,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,842 HCGS802451LB 842,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,843 HCGS831973LB 843,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,844 HCGS828693LB 844,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,845 HCGS791032LB 845,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,805 04QGDA760456GTIL 805,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,822 12QGDA760376GTIL 822,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,825 12QGDA760373GTIL 825,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,826 10QGDA760423GTIL 826,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,841 12QGDA760371GTIL 841,Additional PVC/DLCI - DS3 8K CIR,RC: Additional PVC/DLCI - DS3 8K CIR (high speed),0,846 12QGDA760378GTIL 846,Additional PVC/DLCI - DS3 8K CIR, , , , , ,Y

Quite a mess right?
If there was a way to attach a file I would but I don’t think it’s possible.

Any other ideas?

7

Is it the ampersand (&)?

8

Using MS Word to get a character count, that bad record has over 2200+ characters, whereas the good one has less than 1000. I didn’t find any limitation on the Transfertext command regarding maximum record lengths in the help file, but that doesn’t mean there isn’t one.

9

Never mind my previous two posts. It is trivially easy to recreate your exact error, for anyone interested. Copy and paste both records into word, making sure to force a hard return between them. Save it to a text file.

Open Access, select import, text files, and pick the file. Next through the entire wizard, accepting all defaults. It will import, but throw you 18 exceptions. As a bonus, it tells you which fields (and records, but they’re all on record 2) have a problem.

The first is the most puzzling. In Field96, it balks at the data: “RC: Additional PVC/DLCI - DS3 8K CIR (high speed)”. Now, that string is all over the place in the file, and is in no way unique. Field96 is a 255 character text field, so it’s certainly not too long or the wrong type.

The records don’t seem to be too long, and there are clearly not ‘invalid characters’, as that exact string is brought in successfully in 12 different fields earlier in record 2.

Very strange. Sorry I couldn’t help.

10

Well at least I know I’m not nuts :smiley:

It’s odd as hell isn’t it!?

I swear I can’t see ANYTHING wrong with this damn record! :frowning:

There HAS to be an answer, doesn’t there? :mad:

11

Can you program? Some very quick, down & dirty pseudocode:


Dim strFields() As String

Open input.txt as #1
Do While EndOfFileNotReached
    strLine = WhateverTheGetWholeLineFunctionIs()
    strFields = Split(strLine,",")
    For i = 1 to 227
        MyTable.Fields(i) = strFields(i)
    Next
Loop

Fleshing it out, you’d be looking at less than an hour of work.

12

Thanks for the help Ellis Dee.

I really need to figure out why this is happening and fix it. I need to automate this whole process and unless I can get over this hurdle I don’t see how I’m going to accomplish that.

I wonder if this is just an Access bug (or “Feature” :rolleyes: )

13

If you’re looking to automate it, then coding is probably the best way to go. If that’s not an option, and you must use the transfertext macro, then you may be SOL.

If it were my problem, and for some reason was compelled not to use code, I’d do the following:

  1. Remove all data after field96 (by using just commas) and see if field96 imports properly when the line is much shorter. If it works, you’re doomed, because it means that the line is too long, and so you need code to handle it.

  2. Remove all data (keeping required fields) before field96 as well, so you basically get 227 commas with that one string in the middle, and see if it imports.

  3. If it still doesn’t work, start subtley playing with the text. First removed the parenthesized text. Then shorten it down. Try to identify the closest possible text that works. Then consider a global serach and replace on the raw file.

All 3 of those steps could be done much more easily using your original (failed) idea of using Excel as the intermediary.

Honestly, it smells like an MS feature to me. If I had to make an official determination, my inclination would be that the transfertext action is simply incapable of handling this. But who knows? Maybe it really is a correctable problem without code. I wouldn’t bet on it, though.

14

I think I may have found a MicroSoft “feature” :dubious:

15

Thanks again Ellis Dee.

I don’t think there’s a real solution to this issue.

Thanks anyway! :slight_smile:

16

I found that you can “Open” (instead of Import) an Excel table in Access and all the data appears to remain intact. The table becomes a “Linked Table” instead of a regular table, but might that still work for you?

17

HOLY CR@P! I think that worked! I’ll have to check it more thoroughly, but I think you may have saved the day Reply!