Loading ...
Sorry, an error occurred while loading the content.

Creating a C.S.V. file query.

Expand Messages
  • John Fitzsimons
    Hi, I know this could probably be done with a clip but after years of using NoteTab I still haven t got around to mastering them. Besides, I am not overly
    Message 1 of 5 , Dec 4, 2004
    • 0 Attachment
      Hi,

      I know this could probably be done with a clip but after years
      of using NoteTab I still haven't got around to mastering them.
      Besides, I am not overly eager to invest the time learning a
      programming language to do what is IMO a simple task.

      Here is what I start with. The first two items of my
      input text is....

      0743202236

      A MIND AT A TIME - TPB

      LIVINE M

      $26.95

      0895948575

      A WOMAN'S I CHING - TPB

      STEIN Diane

      $29.95

      NYP

      I want the end result to be something like........


      0743202236, A MIND AT A TIME - TPB, LIVINE M, $26.95
      0895948575, A WOMAN'S I CHING - TPB, STEIN Diane, $29.95,
      NYP

      Some of the problems to overcome are ;

      (1) Each column can be a different width.

      (2) The ISBN needs to be the first item on each line.

      (3) The number of columns of info can change. The first is four for
      example and the second is five.

      My original may have thousands of items so I want to be able to
      do this easily.

      My first thought was to change all carriage returns to something like
      ";". NoteTab does that rather well so that takes care of a lot of the
      job. Starting each line with the ISBN is the hard part.

      Is there a "search and replace" syntax that will recognise any string
      of numbers only ? My thinking is that I could replace all number
      strings to a carriage return AND number string. Resulting in all ISBNs
      starting on a new line.

      Can anyone help here please ? Other than saying "learn clips" ?

      TIA.

      Regards, John.
    • Don - htmlfixit.com
      privately email me a bunch of this text (you sent 2 items -- send me 100) this is really easily solvable with a clip ... I ll even write it for you (if someone
      Message 2 of 5 , Dec 4, 2004
      • 0 Attachment
        privately email me a bunch of this text (you sent 2 items -- send me 100)
        this is really easily solvable with a clip ... I'll even write it for
        you (if someone doesn't beat me to the punch) but I cannot post it here,
        I have to post to the clips list
        do you by any chance belong to that list?

        The clips list is very very friendly and often someone will write the
        clip for you or come awful close if you ask nicely how to solve your
        problem.

        I have the following questions (that will be better answered by a bigger
        set of data)

        1. is the isbn number the only thing that has only numbers in it ... and
        is it always numbers only
        2. are there ever commas in the data itself? if so then you need to go
        to a different delimiter, or enclose text with commas in quotes before
        comma separating them

        Some of the problems to overcome are ;

        (1) Each column can be a different width. - that doesn't really matter much

        (2) The ISBN needs to be the first item on each line - if it is always a
        number that can be done very easily in clips and could be done with a
        regex ... but regexes in notetab can be very slow/klunky ... so a clip
        solution might be better.

        (3) The number of columns of info can change. The first is four for
        example and the second is five. -- if you solve #2 above, then this
        doesn't matter.

        My original may have thousands of items so I want to be able to
        do this easily. -- that is why a clip might be a better solution ...
        because you need to use a regex otherwise to do it right.

        I'll put together a series of commands if I can that will do this
        without a clip, but this really will need to move to the clip list
        rapidly or we will get moderators telling us we are too complex for the
        basic list.

        cc: clip list
      • Don - htmlfixit.com
        Try this ... 1. with the cursor at the top of the document click on the replace dialog (small binoculars) and check regular exp box (no other boxes should be
        Message 3 of 5 , Dec 4, 2004
        • 0 Attachment
          Try this ...
          1. with the cursor at the top of the document click on the replace
          dialog (small binoculars) and check regular exp box (no other boxes
          should be checked), in the top box put this without the quotes:
          "^{\d+}\n" and that will look for one or more numbers at the start of
          the line followed by a new line
          2. in the bottom box put this without the quotes:
          "\n\1\n" an that will precede each isbn number with a line feed ... so
          it is a line feed (\n) followed by the number that was matched (\1) and
          followed by a new line (\n)
          3. hit replace all

          4. uncheck regular exp box, and then with the cursor at the top of the
          file again and type this in the top box of the replace dialog:
          "^P^T^P" - that is a line feed (^P), a tab (^T), and a line feed (^P)
          5. in the bottom put a comma ","
          6. hit replace all

          7. again with regular exp box unchecked and with the cursor at the top
          of the file, type ",^P" in the top box
          8. in the bottom box put ^P
          9. hit replace all

          10. move the cursor to the top of the file, delete the leading line feed
          (put there by steps 1-3).

          11. go to the end and if there is an extra line feed or anything delete it.

          12. save it under a different name than the original file or back up the
          original file before saving.

          That should do it.

          0743202236,A MIND AT A TIME - TPB,LIVINE M,$26.95
          0895948575,A WOMAN'S I CHING - TPB,STEIN Diane,$29.95,NYP
        • Don - htmlfixit.com
          tip for getting to the top of a file in notetab when searching or searching and replacing: with the search or search and replace dialog open double click on
          Message 4 of 5 , Dec 4, 2004
          • 0 Attachment
            tip for getting to the top of a file in notetab when searching or
            searching and replacing:

            with the search or search and replace dialog open double click on any
            "blank" space in the dialog box -- not in a text box where you would
            type something and not on a button or check box

            the cursor will be transported to the beginning of your file

            very handy for repetitive seaching like that suggested here


            if your file is too big it may choke on the regex in my recommended
            steps ... if so either do chunks of the file at a time, or see me on the
            clips list for a better "one step" solution -- ie one clip I tried it
            on 450 items (copying those two over and over) and it worked fine in a
            matter of seconds.

            Happy not clipping :-)

            Don at htmlfixit.com
          • John Fitzsimons
            On Sat, 04 Dec 2004 20:15:05 -0500, Don - htmlfixit.com wrote: Hi Don, ... Okay, that got an extra line between ISBN and the previous field. ... Again.
            Message 5 of 5 , Dec 4, 2004
            • 0 Attachment
              On Sat, 04 Dec 2004 20:15:05 -0500, Don - htmlfixit.com wrote:

              Hi Don,

              >Try this ...
              >1. with the cursor at the top of the document click on the replace
              >dialog (small binoculars) and check regular exp box (no other boxes
              >should be checked), in the top box put this without the quotes:
              >"^{\d+}\n" and that will look for one or more numbers at the start of
              >the line followed by a new line
              >2. in the bottom box put this without the quotes:
              >"\n\1\n" an that will precede each isbn number with a line feed ... so
              >it is a line feed (\n) followed by the number that was matched (\1) and
              >followed by a new line (\n)
              >3. hit replace all

              Okay, that got an extra line between ISBN and the previous field.

              >4. uncheck regular exp box, and then with the cursor at the top of the
              >file again and type this in the top box of the replace dialog:
              >"^P^T^P" - that is a line feed (^P), a tab (^T), and a line feed (^P)
              >5. in the bottom put a comma ","
              >6. hit replace all

              Again. Perfect. :-)

              >7. again with regular exp box unchecked and with the cursor at the top
              >of the file, type ",^P" in the top box
              >8. in the bottom box put ^P
              >9. hit replace all

              Okay.

              >10. move the cursor to the top of the file, delete the leading line feed
              >(put there by steps 1-3).

              >11. go to the end and if there is an extra line feed or anything delete it.

              >12. save it under a different name than the original file or back up the
              >original file before saving.

              >That should do it.

              >0743202236,A MIND AT A TIME - TPB,LIVINE M,$26.95
              >0895948575,A WOMAN'S I CHING - TPB,STEIN Diane,$29.95,NYP

              It certainly "did it". Not only are you very clever but you explained
              things very well. Your help is very much appreciated. Many thanks.

              Regards, John.

              P.S. I was looking at your pages with FireFox. It looked odd. Check
              out ; http://members.optushome.com.au/jfnews/htmlfixit.jpg


              --
              ****************************************************
              ,-._|\ (A.C.F FAQ) http://clients.net2000.com.au/~johnf/faq.html
              / Oz \ John Fitzsimons - Melbourne, Australia.
              \_,--.x/ http://www.vicnet.net.au/~johnf/welcome.htm
              v http://clients.net2000.com.au/~johnf/
            Your message has been successfully submitted and would be delivered to recipients shortly.