How can I read in a TXT file in Access that is over 255 char/line and contains control char?












-2















I am running Access 2010. I need to read in a TXT file into a string. Each line can be anywhere from 40 to 320 char long, ending in a CR. The biggest problem is the TXT file of various lines contains comma's (,) and quotations (") as part of the data.



Is there a trick to doing this? Even if it is getting each char, and testing to see if it is a CR....










share|improve this question

























  • Just to clarify, are you referring to the Carriage Return (CR) char?

    – mdialogo
    Nov 22 '18 at 23:31











  • Please show the code you are using, and indicate which line is giving the error, and what that error is. If there has been no error, then show the input lines and table rows that demonstrate the problem.

    – donPablo
    Nov 22 '18 at 23:37











  • I have not written any code yet. The issue I will have is importing a text line that looks like this: 1234Smith John 123 Anystreet YourTown, ST 12344-1234 "Cheers" None of the " or , are deliminators, they are all valid input characters. The area where "Cheers" is might or might not have the " on the line. When I get the line entered, I will be able to parse it and know what to do with each group of fields.

    – Gregory West
    Nov 22 '18 at 23:40













  • yes CR is Carriage Return.

    – Gregory West
    Nov 22 '18 at 23:43











  • Quote marks and apostrophes will probably only be an issue if you use string in an SQL statement but that can be dealt with. Commas should not be an issue regardless. Why read file? Import wizard not adequate?

    – June7
    Nov 23 '18 at 7:40


















-2















I am running Access 2010. I need to read in a TXT file into a string. Each line can be anywhere from 40 to 320 char long, ending in a CR. The biggest problem is the TXT file of various lines contains comma's (,) and quotations (") as part of the data.



Is there a trick to doing this? Even if it is getting each char, and testing to see if it is a CR....










share|improve this question

























  • Just to clarify, are you referring to the Carriage Return (CR) char?

    – mdialogo
    Nov 22 '18 at 23:31











  • Please show the code you are using, and indicate which line is giving the error, and what that error is. If there has been no error, then show the input lines and table rows that demonstrate the problem.

    – donPablo
    Nov 22 '18 at 23:37











  • I have not written any code yet. The issue I will have is importing a text line that looks like this: 1234Smith John 123 Anystreet YourTown, ST 12344-1234 "Cheers" None of the " or , are deliminators, they are all valid input characters. The area where "Cheers" is might or might not have the " on the line. When I get the line entered, I will be able to parse it and know what to do with each group of fields.

    – Gregory West
    Nov 22 '18 at 23:40













  • yes CR is Carriage Return.

    – Gregory West
    Nov 22 '18 at 23:43











  • Quote marks and apostrophes will probably only be an issue if you use string in an SQL statement but that can be dealt with. Commas should not be an issue regardless. Why read file? Import wizard not adequate?

    – June7
    Nov 23 '18 at 7:40
















-2












-2








-2








I am running Access 2010. I need to read in a TXT file into a string. Each line can be anywhere from 40 to 320 char long, ending in a CR. The biggest problem is the TXT file of various lines contains comma's (,) and quotations (") as part of the data.



Is there a trick to doing this? Even if it is getting each char, and testing to see if it is a CR....










share|improve this question
















I am running Access 2010. I need to read in a TXT file into a string. Each line can be anywhere from 40 to 320 char long, ending in a CR. The biggest problem is the TXT file of various lines contains comma's (,) and quotations (") as part of the data.



Is there a trick to doing this? Even if it is getting each char, and testing to see if it is a CR....







vba ms-access text access-vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 23 '18 at 6:53









Cindy Meister

14.9k102234




14.9k102234










asked Nov 22 '18 at 21:50









Gregory WestGregory West

32




32













  • Just to clarify, are you referring to the Carriage Return (CR) char?

    – mdialogo
    Nov 22 '18 at 23:31











  • Please show the code you are using, and indicate which line is giving the error, and what that error is. If there has been no error, then show the input lines and table rows that demonstrate the problem.

    – donPablo
    Nov 22 '18 at 23:37











  • I have not written any code yet. The issue I will have is importing a text line that looks like this: 1234Smith John 123 Anystreet YourTown, ST 12344-1234 "Cheers" None of the " or , are deliminators, they are all valid input characters. The area where "Cheers" is might or might not have the " on the line. When I get the line entered, I will be able to parse it and know what to do with each group of fields.

    – Gregory West
    Nov 22 '18 at 23:40













  • yes CR is Carriage Return.

    – Gregory West
    Nov 22 '18 at 23:43











  • Quote marks and apostrophes will probably only be an issue if you use string in an SQL statement but that can be dealt with. Commas should not be an issue regardless. Why read file? Import wizard not adequate?

    – June7
    Nov 23 '18 at 7:40





















  • Just to clarify, are you referring to the Carriage Return (CR) char?

    – mdialogo
    Nov 22 '18 at 23:31











  • Please show the code you are using, and indicate which line is giving the error, and what that error is. If there has been no error, then show the input lines and table rows that demonstrate the problem.

    – donPablo
    Nov 22 '18 at 23:37











  • I have not written any code yet. The issue I will have is importing a text line that looks like this: 1234Smith John 123 Anystreet YourTown, ST 12344-1234 "Cheers" None of the " or , are deliminators, they are all valid input characters. The area where "Cheers" is might or might not have the " on the line. When I get the line entered, I will be able to parse it and know what to do with each group of fields.

    – Gregory West
    Nov 22 '18 at 23:40













  • yes CR is Carriage Return.

    – Gregory West
    Nov 22 '18 at 23:43











  • Quote marks and apostrophes will probably only be an issue if you use string in an SQL statement but that can be dealt with. Commas should not be an issue regardless. Why read file? Import wizard not adequate?

    – June7
    Nov 23 '18 at 7:40



















Just to clarify, are you referring to the Carriage Return (CR) char?

– mdialogo
Nov 22 '18 at 23:31





Just to clarify, are you referring to the Carriage Return (CR) char?

– mdialogo
Nov 22 '18 at 23:31













Please show the code you are using, and indicate which line is giving the error, and what that error is. If there has been no error, then show the input lines and table rows that demonstrate the problem.

– donPablo
Nov 22 '18 at 23:37





Please show the code you are using, and indicate which line is giving the error, and what that error is. If there has been no error, then show the input lines and table rows that demonstrate the problem.

– donPablo
Nov 22 '18 at 23:37













I have not written any code yet. The issue I will have is importing a text line that looks like this: 1234Smith John 123 Anystreet YourTown, ST 12344-1234 "Cheers" None of the " or , are deliminators, they are all valid input characters. The area where "Cheers" is might or might not have the " on the line. When I get the line entered, I will be able to parse it and know what to do with each group of fields.

– Gregory West
Nov 22 '18 at 23:40







I have not written any code yet. The issue I will have is importing a text line that looks like this: 1234Smith John 123 Anystreet YourTown, ST 12344-1234 "Cheers" None of the " or , are deliminators, they are all valid input characters. The area where "Cheers" is might or might not have the " on the line. When I get the line entered, I will be able to parse it and know what to do with each group of fields.

– Gregory West
Nov 22 '18 at 23:40















yes CR is Carriage Return.

– Gregory West
Nov 22 '18 at 23:43





yes CR is Carriage Return.

– Gregory West
Nov 22 '18 at 23:43













Quote marks and apostrophes will probably only be an issue if you use string in an SQL statement but that can be dealt with. Commas should not be an issue regardless. Why read file? Import wizard not adequate?

– June7
Nov 23 '18 at 7:40







Quote marks and apostrophes will probably only be an issue if you use string in an SQL statement but that can be dealt with. Commas should not be an issue regardless. Why read file? Import wizard not adequate?

– June7
Nov 23 '18 at 7:40














2 Answers
2






active

oldest

votes


















1














To accomplish this task, you will need to write your own import code that will read directly from the file. The Microsoft Access import features will not handle a file like this very well, and since you want to analyze each line in code, it is better to handle reading it yourself.



There are many approaches you can take, and all will involve File handles and Opening the file. But, the best approach is to use a class that does all of the dirty work for you.



One such class is the LargeTextFile class that can be found in any of the Microsoft Access Developer's Handbooks (Volume 1) for Access 97, 2000, 2002 or 2003, written by Getz, Litwin, and Gilbert (Sybex), if you have access to one of them.



Another option would be the clsReadTextFile class, available for free on the Access MVP Site (The Access Web) site:



http://www.theaccessweb.com/downloads/clsReadTextFile.txt



Using clsReadTextFile you can process your file, line by line using code similar to this:



Dim file As New clsReadTextFile
Dim line As String

file.FileName = "C:MyFile.txt"

file.cfOpenFile

Do While Not file.EndOfFile
file.csGetALine
line = file.Text
If InStr(line, "MySearchText") Then
'Do something
End If
Loop
file.cfCloseFile


The line string variable will contain the text of the line just read, and you can write code to parse it how you need and process it appropriately. Then the loop will go on to read the next line. This will allow you to process each line of the file manually in your code.






share|improve this answer

































    0














    It is not clear from your post as to whether or not you can - or have tried - to use the tools available in the product for this task. Access 2010 offers linking to a .txt file as well as appending a .txt file to a table. These are standard features in the External tab of the ribbon.



    The Large Text (formerly Memo) field type allows ~4K characters. Not sure if you wish to attempt to bring in all the txt data into a single field - if so then this limit is important.



    If the CRs of the text document imply a new record/row of data - rather than a continuous string for the entire document - - AND - - - if there is any consistent structure within all rows of data - then the import wizard can use either character count or symbols (i.e. comma if they exist) - as the means to separate/segregate each individual row of data into separate fields in a single row of a table.






    share|improve this answer























      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53438355%2fhow-can-i-read-in-a-txt-file-in-access-that-is-over-255-char-line-and-contains-c%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      1














      To accomplish this task, you will need to write your own import code that will read directly from the file. The Microsoft Access import features will not handle a file like this very well, and since you want to analyze each line in code, it is better to handle reading it yourself.



      There are many approaches you can take, and all will involve File handles and Opening the file. But, the best approach is to use a class that does all of the dirty work for you.



      One such class is the LargeTextFile class that can be found in any of the Microsoft Access Developer's Handbooks (Volume 1) for Access 97, 2000, 2002 or 2003, written by Getz, Litwin, and Gilbert (Sybex), if you have access to one of them.



      Another option would be the clsReadTextFile class, available for free on the Access MVP Site (The Access Web) site:



      http://www.theaccessweb.com/downloads/clsReadTextFile.txt



      Using clsReadTextFile you can process your file, line by line using code similar to this:



      Dim file As New clsReadTextFile
      Dim line As String

      file.FileName = "C:MyFile.txt"

      file.cfOpenFile

      Do While Not file.EndOfFile
      file.csGetALine
      line = file.Text
      If InStr(line, "MySearchText") Then
      'Do something
      End If
      Loop
      file.cfCloseFile


      The line string variable will contain the text of the line just read, and you can write code to parse it how you need and process it appropriately. Then the loop will go on to read the next line. This will allow you to process each line of the file manually in your code.






      share|improve this answer






























        1














        To accomplish this task, you will need to write your own import code that will read directly from the file. The Microsoft Access import features will not handle a file like this very well, and since you want to analyze each line in code, it is better to handle reading it yourself.



        There are many approaches you can take, and all will involve File handles and Opening the file. But, the best approach is to use a class that does all of the dirty work for you.



        One such class is the LargeTextFile class that can be found in any of the Microsoft Access Developer's Handbooks (Volume 1) for Access 97, 2000, 2002 or 2003, written by Getz, Litwin, and Gilbert (Sybex), if you have access to one of them.



        Another option would be the clsReadTextFile class, available for free on the Access MVP Site (The Access Web) site:



        http://www.theaccessweb.com/downloads/clsReadTextFile.txt



        Using clsReadTextFile you can process your file, line by line using code similar to this:



        Dim file As New clsReadTextFile
        Dim line As String

        file.FileName = "C:MyFile.txt"

        file.cfOpenFile

        Do While Not file.EndOfFile
        file.csGetALine
        line = file.Text
        If InStr(line, "MySearchText") Then
        'Do something
        End If
        Loop
        file.cfCloseFile


        The line string variable will contain the text of the line just read, and you can write code to parse it how you need and process it appropriately. Then the loop will go on to read the next line. This will allow you to process each line of the file manually in your code.






        share|improve this answer




























          1












          1








          1







          To accomplish this task, you will need to write your own import code that will read directly from the file. The Microsoft Access import features will not handle a file like this very well, and since you want to analyze each line in code, it is better to handle reading it yourself.



          There are many approaches you can take, and all will involve File handles and Opening the file. But, the best approach is to use a class that does all of the dirty work for you.



          One such class is the LargeTextFile class that can be found in any of the Microsoft Access Developer's Handbooks (Volume 1) for Access 97, 2000, 2002 or 2003, written by Getz, Litwin, and Gilbert (Sybex), if you have access to one of them.



          Another option would be the clsReadTextFile class, available for free on the Access MVP Site (The Access Web) site:



          http://www.theaccessweb.com/downloads/clsReadTextFile.txt



          Using clsReadTextFile you can process your file, line by line using code similar to this:



          Dim file As New clsReadTextFile
          Dim line As String

          file.FileName = "C:MyFile.txt"

          file.cfOpenFile

          Do While Not file.EndOfFile
          file.csGetALine
          line = file.Text
          If InStr(line, "MySearchText") Then
          'Do something
          End If
          Loop
          file.cfCloseFile


          The line string variable will contain the text of the line just read, and you can write code to parse it how you need and process it appropriately. Then the loop will go on to read the next line. This will allow you to process each line of the file manually in your code.






          share|improve this answer















          To accomplish this task, you will need to write your own import code that will read directly from the file. The Microsoft Access import features will not handle a file like this very well, and since you want to analyze each line in code, it is better to handle reading it yourself.



          There are many approaches you can take, and all will involve File handles and Opening the file. But, the best approach is to use a class that does all of the dirty work for you.



          One such class is the LargeTextFile class that can be found in any of the Microsoft Access Developer's Handbooks (Volume 1) for Access 97, 2000, 2002 or 2003, written by Getz, Litwin, and Gilbert (Sybex), if you have access to one of them.



          Another option would be the clsReadTextFile class, available for free on the Access MVP Site (The Access Web) site:



          http://www.theaccessweb.com/downloads/clsReadTextFile.txt



          Using clsReadTextFile you can process your file, line by line using code similar to this:



          Dim file As New clsReadTextFile
          Dim line As String

          file.FileName = "C:MyFile.txt"

          file.cfOpenFile

          Do While Not file.EndOfFile
          file.csGetALine
          line = file.Text
          If InStr(line, "MySearchText") Then
          'Do something
          End If
          Loop
          file.cfCloseFile


          The line string variable will contain the text of the line just read, and you can write code to parse it how you need and process it appropriately. Then the loop will go on to read the next line. This will allow you to process each line of the file manually in your code.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 28 '18 at 13:36

























          answered Nov 27 '18 at 14:59









          Jericho JohnsonJericho Johnson

          6041410




          6041410

























              0














              It is not clear from your post as to whether or not you can - or have tried - to use the tools available in the product for this task. Access 2010 offers linking to a .txt file as well as appending a .txt file to a table. These are standard features in the External tab of the ribbon.



              The Large Text (formerly Memo) field type allows ~4K characters. Not sure if you wish to attempt to bring in all the txt data into a single field - if so then this limit is important.



              If the CRs of the text document imply a new record/row of data - rather than a continuous string for the entire document - - AND - - - if there is any consistent structure within all rows of data - then the import wizard can use either character count or symbols (i.e. comma if they exist) - as the means to separate/segregate each individual row of data into separate fields in a single row of a table.






              share|improve this answer




























                0














                It is not clear from your post as to whether or not you can - or have tried - to use the tools available in the product for this task. Access 2010 offers linking to a .txt file as well as appending a .txt file to a table. These are standard features in the External tab of the ribbon.



                The Large Text (formerly Memo) field type allows ~4K characters. Not sure if you wish to attempt to bring in all the txt data into a single field - if so then this limit is important.



                If the CRs of the text document imply a new record/row of data - rather than a continuous string for the entire document - - AND - - - if there is any consistent structure within all rows of data - then the import wizard can use either character count or symbols (i.e. comma if they exist) - as the means to separate/segregate each individual row of data into separate fields in a single row of a table.






                share|improve this answer


























                  0












                  0








                  0







                  It is not clear from your post as to whether or not you can - or have tried - to use the tools available in the product for this task. Access 2010 offers linking to a .txt file as well as appending a .txt file to a table. These are standard features in the External tab of the ribbon.



                  The Large Text (formerly Memo) field type allows ~4K characters. Not sure if you wish to attempt to bring in all the txt data into a single field - if so then this limit is important.



                  If the CRs of the text document imply a new record/row of data - rather than a continuous string for the entire document - - AND - - - if there is any consistent structure within all rows of data - then the import wizard can use either character count or symbols (i.e. comma if they exist) - as the means to separate/segregate each individual row of data into separate fields in a single row of a table.






                  share|improve this answer













                  It is not clear from your post as to whether or not you can - or have tried - to use the tools available in the product for this task. Access 2010 offers linking to a .txt file as well as appending a .txt file to a table. These are standard features in the External tab of the ribbon.



                  The Large Text (formerly Memo) field type allows ~4K characters. Not sure if you wish to attempt to bring in all the txt data into a single field - if so then this limit is important.



                  If the CRs of the text document imply a new record/row of data - rather than a continuous string for the entire document - - AND - - - if there is any consistent structure within all rows of data - then the import wizard can use either character count or symbols (i.e. comma if they exist) - as the means to separate/segregate each individual row of data into separate fields in a single row of a table.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Dec 1 '18 at 18:47









                  Cahaba DataCahaba Data

                  300123




                  300123






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53438355%2fhow-can-i-read-in-a-txt-file-in-access-that-is-over-255-char-line-and-contains-c%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      To store a contact into the json file from server.js file using a class in NodeJS

                      Marschland

                      Wiesbaden