I've built a database that auto-imports a pipe delimited text file each day which may contain thousands of rows. Sometimes, this causes an error because the import is expecting that each text string should be between "" quotation marks. This source text report (which is generated by a separate system I have no control over) sometimes gets truncated if a line runs too long, which then causes Access to drop out that record and sometimes all subsequent records.
Here's an illustration of that:
"text"|"text"|"text"|"text"|number|number|"text"|" text"
But like I said, if the row runs too long (a limitation of the source system) then a line might get truncated like this:
"text"|"text"|"text"|"text"|number|number|"text"|" text"|"text"|"tex
So when this happens I am having to manually look through this text file and find a line that is missing a quote off of the end, and either fix it or delete the text back to the previous complete string in quotes.
I would love for VBA to do this for me. I already have VBA that can detect an ImportError like this, so now all I need is some VBA that can scan through the text file, find any line(s) in which the ending character is not a quotation mark, then backspace up to the previous quote. Does anyone have anything like this in their repertoire to help get me started?
Thanks in advance!