8 Replies Latest reply on Apr 11, 2016 12:20 PM by TKnTexas

    How to Delimit Text

    TKnTexas

      I have a report, the AR Aging, printed from the accounting system (Great Plains).  The "SmartLists" do not extract the data as we need it.  This would be great because we could get it into Excel.  So I can write a report as a text file.  It is exported as Tab-Delimited. 

       

      The lines of DATA I can identify as such using PatternCount.  I then can use MiddleWords to parse it.  My difficulty comes in the data that the company Name and ID is written, as well as the Contact Name/Phone.

       

      The data is TAB-DELIMITED.  But I cannot see how to determine the positions of the Tabs in the line.  I cannot use MiddleWords as the Customer ID and Customer Name may be one, two or more words.  How to do I determine the Tabs position?

        • 1. Re: How to Delimit Text
          coherentkris

          Code ( 9 ) is the tab char.

          use it with Position(text;searchString;start;occurrence)

          • 2. Re: How to Delimit Text
            user19752

            correction: Char(9) is tab char. Code("<<here is a tab>>") get 9.

            • 3. Re: How to Delimit Text
              siplus

              If you have a tab delimited text, you don't drag it on the Excel icon. You open Excel and go to File -> Open, you enable "All files" and choose your text. Excel will start the Text Import Wizard. In step 1 you choose Delimited, then you click on next. In step 2 you specify tab as delimiter. In step 3 you can choose for each column whether it's Text, Date, or General (numeric).

               

              When you click on finish, you get your columns the way you wanted them.

              • 4. Re: How to Delimit Text
                beverly

                might it depend on the platform? I think I can drag a file and drop on Excel icon. And I can right-click the mouse to choose Open with...

                 

                beverly

                • 5. Re: How to Delimit Text
                  siplus

                  try before you buy. Export a simple database to .tab and do it your way, then do it my way, What counts is what really counts, i.e. client problem solved, possibly with no magic but easy, clearly understandable events happening and the outcome he had in his mind.

                  • 6. Re: How to Delimit Text
                    TKnTexas

                    I have FMPA14.  I am using it to "read" a printed report in Tab-delimited format.  My problem is with certain lines of the report, the lines that have the customer name and customer ID.  Middlewords will not work because the CustomerID may be more than two words, the same for the Customer Name.  These are separated by the tab character, hence needing to know what position that is in. 

                     

                    I do appreciate the input.  I wish Great Pains worked better about exporting data.

                    • 7. Re: How to Delimit Text
                      beverly

                      The TAB character is Char(9). You can use PatternCount() and Position() and Middle() to parse out tab delimited text. I'm not sure how you get the data in a field with Tab characters, unless you are importing another format?

                       

                      -- sent from myPhone --

                      Beverly Voth

                      --

                      • 8. Re: How to Delimit Text
                        TKnTexas

                        Using PatternCount I have determined the "characteristic" of the line.  So I have an identifier on the line with the contact name and info.  Knowing the position of the tabs I can then use the middle function.