Question Unparsable Record (1 Viewer)

tgroneck · Apr 12, 2013

I'm using VBA to parse a text file report and reformat it into a comma-delimitted file that I can import into a table.

The issue is that when I append each record to a text file and then import it, about half of them do not import due to "Unparsable Record". If I write each record to its own file and import one by one, they all import fine. It is much faster to append all records to one file and then import one big file.

Any ideas on why it would work with many single record files and not a multi-record file?

plog · Apr 12, 2013

Really not enough to go on, but here's some shots in the dark:

1. Stray single or double quote in a record. This might make the first line work, but might screw up the next record.

2. Weird/inconsistent end of record character. There might be an extra character that could be invisible when you look at it at the end of the bad records.

3. Unescaped delimiter within a field. I work with mail lists and addresses like this "123 Main, #7" always screw up because of that comma. If the file doesn't have quotes around the address and simply uses commas to delimit, the import process thinks that comma signifies the end of the field, thus making the record have an additional field.

You need to step through your import processing to find out. Rewrite your VBA code to tell you if the last row was successfully imported, if not have it spit out exactly what its trying to put in.

tgroneck · Apr 12, 2013

plog said:
You need to step through your import processing to find out. Rewrite your VBA code to tell you if the last row was successfully imported, if not have it spit out exactly what its trying to put in.

How do you step through the import process so that you can tell what record is causing the error? Out of 33 records, the first imports and then the next 16 fail and the last 16 import.

Currently, I'm importing via an import specification. Also, I should add that manually importing the large file results in a "Subscript Out of Range" error.

Here's my import code. How can I step through as you mentioned?

Code:

With DoCmd
     .SetWarnings False
     .Close acTable, "Wires", acSaveYes
     .TransferText acImportDelim, "WiresImportSpecification", "Wires", tempName, False
     .SetWarnings True
End With

pr2-eugin · Apr 12, 2013

Look into your TEXT File, on how the data is.. If it is something like..

Code:

INSERT INTO Transactions VALUES (104,'2012-09-24','2012-09-24',9,'Mrs','Lorraine O'Connor','Crosby','1 Devonshire Road')
INSERT INTO Transactions VALUES (105,'2012-09-07','2012-09-07',83,'Mr','Ronald O 'Donald','Davison','3 Sheppards Croft')
INSERT INTO Transactions VALUES (106,'2012-09-17','2012-09-03',9,'Mrs','Gillian','Dyson','44','Highstone')

None of them will be imported, as the first String O'Connor is wrong, which puts all the other following lines not usable..

tgroneck · Apr 12, 2013

:banghead:

I was missing a close quote at the end of the record...

Thanks for your time & answers!

Vassago · Apr 12, 2013

Please do not delete threads that are solved. Others may benefit from the knowledge.

Question Unparsable Record (1 Viewer)

tgroneck

Registered User.

plog

Banishment Pending

tgroneck

Registered User.

pr2-eugin

Super Moderator

tgroneck

Registered User.

Vassago

Former Staff Turned AWF Retiree

Similar threads

Users who are viewing this thread