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

Find and Replace in a csv-file (only a specified column)

Expand Messages
  • Bauke P. de Vries
    Hello, I m looking for a clip that makes it possible to do a find & replace in one or more columns within a csv-file. I ve looked in several libraries but I
    Message 1 of 12 , Sep 1, 2004
    • 0 Attachment
      Hello,

      I'm looking for a clip that makes it possible to do a 'find & replace'
      in one or more columns within a csv-file.
      I've looked in several libraries but I cannot find it.

      Thanks in advance

      Bauke (Netherlands; sorry for my English)

      --

      Met vriendelijke groet,

      *Bauke P. de Vries
      *



      [Non-text portions of this message have been removed]
    • Don - htmlfixit.com
      ... For all things CSV, Jody s noteblock library is the first place to turn. He has a replace in block clip there: ; Last updated on 07-27-2001 ^!IfTrue
      Message 2 of 12 , Sep 1, 2004
      • 0 Attachment
        Bauke P. de Vries wrote:
        > Hello,
        >
        > I'm looking for a clip that makes it possible to do a 'find & replace'
        > in one or more columns within a csv-file.
        > I've looked in several libraries but I cannot find it.
        >
        > Thanks in advance
        >
        > Bauke (Netherlands; sorry for my English)
        >
        For all things CSV, Jody's noteblock library is the first place to turn.
        He has a replace in block clip there:


        ; Last updated on 07-27-2001
        ^!IfTrue ^$IsEmpty(^$GetSelection$)$ Next else Skip_2
        ^!Prompt You need to select your column and all the rows you want to
        replace text in. You can select all the columns if that is easier for
        you. The delimiter does not matter.
        ^!Goto End
        ^!Set %Find%=^?{&Find what in selected in rows of delimited text};
        %Replace%=^?{&Replace with}; %Options%=^?{Options=THAS}

        ^!SetScreenUpdate Off
        ^!Set %Cursor%=^$GetRowStart$:^$GetColStart$
        ^!Replace "^%Find%" >> "^%Replace%" ^%Options%
        ^!IfError Next else Skip_-1
        ^!SetCursor ^%Cursor%
        ^!Sound ^$GetSoundPath$Phaser.wav

        It is called replace in block. Give that a try. You can find his
        libraries I believe via http://www.notetab.net or from the clips list at
        http://www.notetab.com
      • Patricia Tidmarsh
        I have data bases with individuals whose dates of birth and death in various forms -- 15 May 1900 May 15, 1900 May 1900 1900 I would like to sort the data by
        Message 3 of 12 , Sep 1, 2004
        • 0 Attachment
          I have data bases with individuals whose dates of birth and death in various forms --

          15 May 1900
          May 15, 1900
          May 1900
          1900

          I would like to sort the data by year and would like to separate the day,
          month and year into separate columns. The purpose would be to sort the
          material by date of birth.

          TIA
        • Don - htmlfixit.com
          ... what format do you have it in now? is it csv formatted? I assume date isn t the only field why don t you attach one or two sample lines from the file even
          Message 4 of 12 , Sep 1, 2004
          • 0 Attachment
            Patricia Tidmarsh wrote:

            > I have data bases with individuals whose dates of birth and death in various forms --
            >
            > 15 May 1900
            > May 15, 1900
            > May 1900
            > 1900
            >
            > I would like to sort the data by year and would like to separate the day,
            > month and year into separate columns. The purpose would be to sort the
            > material by date of birth.
            >
            > TIA

            >
            what format do you have it in now?
            is it csv formatted?
            I assume date isn't the only field
            why don't you attach one or two sample lines from the file

            even though it is in different formats, date is alway in one consistent
            field?
          • Jeff Scism
            In sorting by Date of Birth I recommend the following format: YYYY-MM-DD This will order them in descending order on sort. I was provided the below clip for
            Message 5 of 12 , Sep 1, 2004
            • 0 Attachment
              In sorting by Date of Birth I recommend the following format:

              YYYY-MM-DD


              This will order them in descending order on sort.

              I was provided the below clip for "flipping" names from Surnaame, First
              to First Surname.

              ^!Replace "{.*},\s+{.*}" >> "\2 \1" RH

              Perhaps with a little work it can be adapted to make your date swaps?

              Otherwise you may need a IF/Then Loop to make the changes.

              It can be complicated when you are dealing with so many variables.
              --
              ~~

              Jeffery G. Scism. IBSSG

              "Just the facts, maam."
              Sgt. Joe Friday,
              LAPD Badge #714

              Do your civic duty, Know your candidates and
              VOTE for the candidate of your choice.

              Jimmy Hoffa, 1960
            • Jeff Scism
              Modern Military Julian Calendar (ZULU or Coordinated Universal Time (old GMT)) The Military Dating system is a modernized version of the Julian system with
              Message 6 of 12 , Sep 1, 2004
              • 0 Attachment
                Modern Military Julian Calendar (ZULU or Coordinated Universal Time (old
                GMT))

                The Military Dating system is a modernized version of the Julian system
                with the EXCEPTION that the year counts reflect the Gregorian year (Anno
                Domini) vs, the original Julian start date.

                The Date format is as follows YYYYDDDHHMM:SS

                Y= The Calendar year, (Currently 2004)
                D= The nmumerical sequence of days in that year, beginning with January
                1, at 0000:01 hrs in ZULU time. (ZULU is the Military's reference time
                zone which corresponds with 0 degrees Longitude.)

                Often the Z suffix "(Z)" will be appended to indicate the ZULU time is
                the reference, if the Time indicated is the Time zone whewre the event
                occurs, a L "(L)"is suffixed to indicate "Local Time"

                The New calendar year begins at YYYY0000:01(Z). Often the Local Time
                will also be referenced, usually by indicating a +\- (number 1-12) to
                indicate how much adjustment is needed to make a local reference.

                The H= HOURS on a 24 hour clock, There is no AM/PM in military time it
                is sequential hours after Midnight, (0000:01 HRS)(Contrary to popular
                belief there IS NO 2400 Hours, it is 0000:00 Hrs. (The first minute of
                the NEW day) Even people in the Military get this confused, and still
                "say" 2400 Hrs for Midnight. It is also accepted to avoid unneeded
                argument. (Is it the LAST second of the previous day or the First second
                of the new day?) Logically there has to be a "zero" point.).

                M= Minutes of the successive Hour, 1-60.

                S= Seconds of the successive Minute, 1-60.

                This sytem has several benefits when dealing with a Global reference. It
                puts the whole world on a single time reference. If the ZULU clock is
                used world wide coordination is possible.

                It also allows true chronological filing of date referenced events, the
                YEAR is referred, then the Month, Day, Hour, Minute, Second....

                This system is used to clock almost everything computer driven,
                Messages sent through the Internet are clocked in this manner, and
                (referenced to Coordinated Universal Time, the "old" GMT) and traceable
                in that manner, It is in the "home" computer that the conversions occur
                that appear in your VISABLE message header. (That is why you have to set
                your local time zone and clock correctly in your computer).



                --
                ~~

                Jeffery G. Scism. IBSSG

                "Just the facts, maam."
                Sgt. Joe Friday,
                LAPD Badge #714

                Do your civic duty, Know your candidates and
                VOTE for the candidate of your choice.

                Jimmy Hoffa, 1960
              • Jody
                Hi Bauke, ... The Replace in Block... had some problems , so I reworked it. You can only do one column at a time. I still need to do more work, because as
                Message 7 of 12 , Sep 1, 2004
                • 0 Attachment
                  Hi Bauke,

                  >I'm looking for a clip that makes it possible to do a 'find & replace'
                  >in one or more columns within a csv-file.

                  The Replace in Block... had some problems <g>, so I reworked it.
                  You can only do one column at a time. I still need to do more
                  work, because as is, it deals with the whole field in each row.
                  That is to say that it won't replace parts of a field, but the
                  whole field. For instance, if you have a field with the date in
                  it and only wanted to change the year from "2004" to "04" it
                  won't do that right now, but will later after I do some other
                  things. :) It will only change the full date 08/10/2004 >
                  08/10/04 Just download the noteblock.zip w/o the Clipbar below
                  for now.

                  You might be interested in trying out my Library called NoteBlock
                  which works specifically with delimited files. You can sort by
                  fields (column), delete fields, reorder fields, and more... As
                  always, use on a test document first to ensure there are no bugs. ;)

                  http://www.notetab.net/zip/noteblock.zip <-- Contains only the Library
                  http://www.notetab.net/zip/noteblockbar.zip <-- Contains the Library plus
                  Clipbar w/ buttons

                  Block, field, and column are used interchangeably in the Library.

                  >Bauke (Netherlands; sorry for my English)

                  What's to be sorry for; it's good! :)


                  Happy Clip'n!
                  Jody

                  www.clean-funnies.com, http://www.fookes.us/maillist.htm

                  Subscribe: mailto:ntb-Clips-Subscribe@yahoogroups.com
                  UnSubscribe: mailto:ntb-Clips-UnSubscribe@yahoogroups.com
                  Options: http://groups.yahoo.com/group/ntb-clips
                • Bauke P. de Vries
                  Hello, I m sorry, but the Replace in block clip in the NoteBlock lib is not what I need. I get (from a MySQL database) csv-files that look like this:
                  Message 8 of 12 , Sep 1, 2004
                  • 0 Attachment
                    Hello,

                    I'm sorry, but the 'Replace in block' clip in the NoteBlock lib is not
                    what I need.

                    I get (from a MySQL database) csv-files that look like this:

                    "Oli";"Full name shgdfhgdh";"Address";"1";"City";"Shortname";"textfield";"0"
                    "MdV";"Full name";"Address";"2";"City";"Shortname";"textfield-can be
                    long";"1"

                    What I would like to do is to take away the quotes in field 4 (find &
                    replace just in field 4), but I have more files and sometimes I want to
                    do a F&R in field 3 and 7 for example.

                    Can this be done? And how?

                    Thanks again,

                    Bauke (Netherlands)


                    Met vriendelijke groet,

                    *Bauke P. de Vries
                    *



                    Don - htmlfixit.com wrote:

                    >Bauke P. de Vries wrote:
                    >
                    >
                    >>Hello,
                    >>
                    >>I'm looking for a clip that makes it possible to do a 'find & replace'
                    >>in one or more columns within a csv-file.
                    >>I've looked in several libraries but I cannot find it.
                    >>
                    >>Thanks in advance
                    >>
                    >>Bauke (Netherlands; sorry for my English)
                    >>
                    >>
                    >>
                    >For all things CSV, Jody's noteblock library is the first place to turn.
                    > He has a replace in block clip there:
                    >
                    >
                    >; Last updated on 07-27-2001
                    >^!IfTrue ^$IsEmpty(^$GetSelection$)$ Next else Skip_2
                    >^!Prompt You need to select your column and all the rows you want to
                    >replace text in. You can select all the columns if that is easier for
                    >you. The delimiter does not matter.
                    >^!Goto End
                    >^!Set %Find%=^?{&Find what in selected in rows of delimited text};
                    >%Replace%=^?{&Replace with}; %Options%=^?{Options=THAS}
                    >
                    >^!SetScreenUpdate Off
                    >^!Set %Cursor%=^$GetRowStart$:^$GetColStart$
                    >^!Replace "^%Find%" >> "^%Replace%" ^%Options%
                    >^!IfError Next else Skip_-1
                    >^!SetCursor ^%Cursor%
                    >^!Sound ^$GetSoundPath$Phaser.wav
                    >
                    >It is called replace in block. Give that a try. You can find his
                    >libraries I believe via http://www.notetab.net or from the clips list at
                    >http://www.notetab.com
                    >
                    >
                    >
                    >
                    >
                    >Yahoo! Groups Links
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    >


                    [Non-text portions of this message have been removed]
                  • Jody
                    Hi Patricia, ... That s a tall order! :) I don t know if I will have the time for this one - it d be pretty difficult or at least time consuming to do, but I
                    Message 9 of 12 , Sep 1, 2004
                    • 0 Attachment
                      Hi Patricia,

                      >I have data bases with individuals whose dates of birth and death
                      >in various forms --
                      >
                      >15 May 1900
                      >May 15, 1900
                      >May 1900
                      >1900
                      >
                      >I would like to sort the data by year and would like to separate
                      >the day, month and year into separate columns. The purpose would
                      >be to sort the material by date of birth.

                      That's a tall order! :)

                      I don't know if I will have the time for this one - it'd be
                      pretty difficult or at least time consuming to do, but I would
                      need to see a few rows of the data. You can dummie the names or
                      any other data you don't want published, but leave the fields
                      intact. I also need the fields that you send in shown as you want
                      it corrected to. In the rows as they are, I need one example of
                      each date format that you have like if the four above it all of
                      them, then I'd need examples of those four full rows. The dates
                      will need to be reformatted into the same format (as much as
                      possible) and the month converted into a number (and then back
                      after sorting). All that would be done by the Clip of course.

                      Is now:
                      15 May 1900
                      May 15, 1900
                      May 1900
                      1900

                      Needs to be something like:
                      1900 May 15
                      1900 May
                      1900

                      Happy Clip'n!
                      Jody

                      www.clean-funnies.com, http://www.fookes.us/maillist.htm

                      Subscribe: mailto:ntb-Clips-Subscribe@yahoogroups.com
                      UnSubscribe: mailto:ntb-Clips-UnSubscribe@yahoogroups.com
                      Options: http://groups.yahoo.com/group/ntb-clips
                    • Jody
                      Hi Bauke, ... The quotes there are part of the delimiter. Is that what you want to replace or is there actually other text where 1 is and it is within that
                      Message 10 of 12 , Sep 1, 2004
                      • 0 Attachment
                        Hi Bauke,

                        >"Oli";"Full name shgdfhgdh";"Address";"1";"City";"Shortname";"textfield";"0"
                        >"MdV";"Full name";"Address";"2";"City";"Shortname";"textfield-can be
                        >long";"1"
                        >
                        >What I would like to do is to take away the quotes in field 4
                        >(find & replace just in field 4), but I have more files and
                        >sometimes I want to do a F&R in field 3 and 7 for example.

                        The quotes there are part of the delimiter. Is that what you want
                        to replace or is there actually other text where "1" is and it is
                        within that that you want the quotes removed? That is what I was
                        speaking of when I gave the date example in my previous post that
                        I need to still do yet, that is, replace text within each field,
                        not the whole field. As it is now it will replace the whole field
                        with something else if the field is what is searched for in that
                        column.

                        Happy Clip'n!
                        Jody

                        www.clean-funnies.com, http://www.fookes.us/maillist.htm

                        Subscribe: mailto:ntb-Clips-Subscribe@yahoogroups.com
                        UnSubscribe: mailto:ntb-Clips-UnSubscribe@yahoogroups.com
                        Options: http://groups.yahoo.com/group/ntb-clips
                      • Jody
                        Hi Bauke, ... I have NoteBlock:Replace in Block fixed now so that it will make replacements within a field/column, at least with common delimiters. It s really
                        Message 11 of 12 , Sep 1, 2004
                        • 0 Attachment
                          Hi Bauke,

                          >"Oli";"Full name shgdfhgdh";"Address";"1";"City";"Shortname";"textfield";"0"
                          >"MdV";"Full name";"Address";"2";"City";"Shortname";"textfield-can be
                          >long";"1"
                          >
                          >What I would like to do is to take away the quotes in field 4 (find &
                          >replace just in field 4), but I have more files and sometimes I want to
                          >do a F&R in field 3 and 7 for example.

                          I have NoteBlock:Replace in Block fixed now so that it will make
                          replacements within a field/column, at least with common
                          delimiters. It's really a pain doing some of the things when
                          quotes are involved due to a bug in NoteTab dealing with
                          variables and quotes, delimiters also.

                          I didn't hear back from you about whether you wanted to actually
                          remove the quotes around the "1" in your field 4 or not. It is
                          actually part of the delimiter and I did not make the Clip to
                          remove those. That can be done with a regular expression easier
                          probably so long as your file isn't super large.

                          You might be interested in trying out my Library called NoteBlock
                          which works specifically with delimited files. You can sort by
                          fields (column), delete fields, reorder fields, replace in
                          fields, and more... As always, use on a test document first to
                          ensure there are no bugs and you should have a backup. ;)

                          http://www.notetab.net/zip/noteblock.zip <-- Contains only the Library
                          http://www.notetab.net/zip/noteblockbar.zip <-- Contains the Library plus
                          Clipbar w/ buttons

                          Block, field, and column are used interchangeably in the Library.

                          Happy Clip'n!
                          Jody

                          www.clean-funnies.com, http://www.fookes.us/maillist.htm

                          Subscribe: mailto:ntb-Clips-Subscribe@yahoogroups.com
                          UnSubscribe: mailto:ntb-Clips-UnSubscribe@yahoogroups.com
                          Options: http://groups.yahoo.com/group/ntb-clips
                        • Alec Burgess
                          Bauke & Patricia (with Jody s forbearance I hope) Both these tasks can be done with Jody s clip stuff and/or purpose-built clips I think, but if what you
                          Message 12 of 12 , Sep 1, 2004
                          • 0 Attachment
                            Bauke & Patricia
                            (with Jody's forbearance I hope)

                            Both these tasks can be done with Jody's clip stuff and/or purpose-built
                            clips I think, but if what you really want is to get the job done without
                            necessarily becoming a clip maven :-) there is a freeware program designed
                            to work with CVS files which amongst many other very neat manipulations will
                            do find and replace in specific columns.

                            Have a look at:
                            CVSed http://home.hccnet.nl/s.j.francke/software/software.htm

                            FWIW: this was originally recommended to me by David Elliot on one of the
                            Notetab lists (probably OFF-TOPIC) where any future discussion of this
                            program should more properly be directed.

                            Regards ... Alec
                            --


                            ---- Original Message ----
                            From: "Patricia Tidmarsh" <patricia@...>
                            To: <ntb-clips@yahoogroups.com>
                            Sent: Wednesday, September 01, 2004 10:48
                            Subject: [gla: Re: [Clip] Find and Replace in a csv-file
                            (only a specified column)
                          Your message has been successfully submitted and would be delivered to recipients shortly.