How to prevent unparsable records from importing

[respree]

Hello all.

I am trying to import a .txt file from an external source (which happens regularly). Inevitably, I will get import errors due to unparsable records (usually, one of the fields in the source file has misplaced quotes, which is confusing Access).

Of course, Access reports these unparsable records into a separate table, but goes and head and imports them anyway to the table it creates when the import takes place.

Question: Is there a way (setting?) to prevent these unparsable records from being imported into the main file, while still reporting the errors in a separate table?

Any help or insight would be greatly appreciated.

 

[dk]

Although I can imagine what your data looks like, can you post a few sample records?

 

[respree]

Sure, I'll be happy to. Here are two records from a .txt file that were unparsable.

"BRE","1481","Brent","Jungle Panel I "sharing Fun"","0"
"BRE","1482","Brent","Jungle Panel II "say Hello"","0"

Please note 2 quotes within the record (sharing fun, say Hello). Could even be a a single quote in the record.

 

[dk]

Hmmm,

See what you mean. If no one else answers then I'll have a look over the weekend. I assume that Jungle Panel 1 and say hello should be parsed as seperate fields?

 

[respree]

I would appreciate it. I'm not getting a lot of feedback, so I appreciate yours very much.

No, actually, the problem here is no a missing comma, its the extra quotes within the field. In these examples,

Jungle Panel I "sharing Fun"

should be read into one field as:

Jungle Panel I sharing Fun



"Jungle Panel II "say Hello""

should be read into one field as:

"Jungle Panel II say Hello"

 

[respree]

sorry, that should have read "problem here is not a missing comma"

 

[dk]

Hello,

Here's one possibility but it's not that slick.

When you import the file make sure that the Text Qualifier (on the second page of the Text Import wizard) is set to none. Then when you've imported your data do a Find and Replace replacing " with nothing.

Does that work for you?