Results 1 to 6 of 6
  1. #1
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62

    Edit a text file with VBA

    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!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If each line ends in a carriage return/line feed (vbCrLf) possibly you could back up one character and test if it is a double quote? Sorry that I don't have anything like that in my stash. I recently was able to detect crlf's in a table field but don't know if your file contains them. Not sure why you want to back up a line rather than just add the " at the end where it's missing. Hope that gives you some ideas.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by Micron View Post
    If each line ends in a carriage return/line feed (vbCrLf) possibly you could back up one character and test if it is a double quote? Sorry that I don't have anything like that in my stash. I recently was able to detect crlf's in a table field but don't know if your file contains them. Not sure why you want to back up a line rather than just add the " at the end where it's missing. Hope that gives you some ideas.
    That sounds like the right track, but I don't know how to code it. I've been Googling but most similar issues involve a find and replace. I can't predict the actual string at the end, only that it'll be missing a closing quote. I would prefer to back up rather than add a quote because each of these are codes that map to something in the finished report that the database produces. It's simpler to omit the truncated code than have a downstream mapping error with the finished product, which would certainly confuse my end users.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not seeing a need to know what is at the end of a line, other than a carriage return or line feed character. There are lots of examples available on how to retrieve a text file line by line. If you could employ one of them, you're half way there. It might be prudent to test your file to determine the last character by copying lines from a file and testing for what that is. You could try
    asc(right("yourString",1)) where yourString is your line within quotes, to see what that character is (as a decimal number). Chr(the number) will print the character for you if it's printable.

    I'm not understanding the reluctance to replace "data/data/data/data with "data/data/data/data".
    I used / as a delimiter because I haven't figured out how to use the pipe on my keyboard. It's there, but requires some particular key combination. Anyway, I suppose this might all be moot if the real problem is related to the length of your line. IIRC, the length of a string variable is around 2 billion characters, so if length is the problem, it must be related to how you're dealing with the lines.
    Last edited by Micron; 12-04-2018 at 02:16 PM. Reason: clarification

  5. #5
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62
    Quote Originally Posted by Micron View Post
    I'm not seeing a need to know what is at the end of a line, other than a carriage return or line feed character. There are lots of examples available on how to retrieve a text file line by line. If you could employ one of them, you're half way there. It might be prudent to test your file to determine the last character by copying lines from a file and testing for what that is. You could try
    asc(right("yourString",1)) where yourString is your line within quotes, to see what that character is (as a decimal number). Chr(the number) will print the character for you if it's printable.

    I'm not understanding the reluctance to replace "data/data/data/data with "data/data/data/data".
    I used / as a delimiter because I haven't figured out how to use the pipe on my keyboard. It's there, but requires some particular key combination. Anyway, I suppose this might all be moot if the real problem is related to the length of your line. IIRC, the length of a string variable is around 2 billion characters, so if length is the problem, it must be related to how you're dealing with the lines.
    The character(s) at the end of the line will be a fragment of a code, and I cannot predict what that fragment will look like. To explain in better terms, a valid line might look like:

    "data"|"data"|"data"|"data"|number|number|"data"|" code_mnemonic"|"code_mnemonic"|"code_mnemonic"

    Whereas a line with this issue would appear as:

    "data"|"data"|"data"|"data"|number|number|"data"|" code_mnemonic"|"code_mnemonic"|"code_mnemonic"|"co de_mn

    These code mnemonics themselves are gibberish and need to be mapped to a description that an end user can work with, which is one of the functions of this database. If I just added a quote to the end, the database would accept the truncated code - I don't want that; it won't properly map and will appear as an invalid or unknown code, which will cause my users confusion. The better option is to simply erase the fragment of text, if possible.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would really help to see your import code, but if you read a line into a variable, you could check to see if the last character is a double quote.
    This is assuming the last part of the text line is ALWAYS a delimited text string.

    If the last character is not the double quote, use the "InStrRev" Function to find the last pipe symbol and use the "Left" function to truncate the variable at that position.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-02-2018, 03:12 PM
  2. VBA Code to Open/Edit XML File
    By Stan Denman in forum Modules
    Replies: 3
    Last Post: 05-01-2016, 09:10 AM
  3. Edit a publisher text box with VBA?
    By wackywoo105 in forum Programming
    Replies: 4
    Last Post: 06-26-2014, 02:18 PM
  4. Cannot edit Relationships in Access template file
    By LookingForHelp in forum Database Design
    Replies: 6
    Last Post: 01-27-2014, 07:35 PM
  5. Replies: 2
    Last Post: 07-05-2012, 05:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums