ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    Text file manipulation into CSV

    IT Discussion
    powershell bash
    6
    21
    1.6k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • S
      scottalanmiller
      last edited by

      This is more difficult because you are not just manipulating the text, but pivoting the results.

      1 Reply Last reply Reply Quote 0
      • S
        stacksofplates
        last edited by stacksofplates

        This post is deleted!
        1 Reply Last reply Reply Quote 0
        • S
          stacksofplates
          last edited by

          This post is deleted!
          1 Reply Last reply Reply Quote 0
          • S
            stacksofplates
            last edited by

            Looks like datamash does this.

            0_1531436248970_datamash.png

            J 1 Reply Last reply Reply Quote 1
            • S
              stacksofplates
              last edited by

              You'll have to take care of any delimiter operations you want to do afterward.

              1 Reply Last reply Reply Quote 0
              • S
                stacksofplates
                last edited by stacksofplates

                If you want it all comma delimited here's a script. Not full pathed because I'm lazy. Also sorry didn't add logic for dnf vs yum vs apt. You can just remove that part if you don't want it.

                Run with ./script.sh <filename>

                #!/bin/bash
                
                FILE=$1
                
                if [ -z $(which datamash 2>/dev/null) ]; then
                echo "=========================="
                echo "Installing datamash"
                dnf install -y datamash
                else
                echo "datamash installed"
                fi
                
                
                cp $FILE $FILE.bak
                
                sed -i 's/:/,/g' $FILE
                
                datamash -t , transpose < $FILE > output.txt
                
                
                
                1 Reply Last reply Reply Quote 0
                • S
                  stacksofplates
                  last edited by

                  Also I have a feeling that was the most edited post on this site ha.

                  M 1 Reply Last reply Reply Quote 0
                  • M
                    momurda @stacksofplates
                    last edited by

                    @stacksofplates Your kung fu is strong,

                    1 Reply Last reply Reply Quote 1
                    • J
                      JaredBusch @stacksofplates
                      last edited by JaredBusch

                      @stacksofplates said in Text file manipulation into CSV:

                      Looks like datamash does this.

                      0_1531436248970_datamash.png

                      so datamash is basically a transpose?

                      S 1 Reply Last reply Reply Quote 0
                      • S
                        stacksofplates @JaredBusch
                        last edited by

                        @jaredbusch said in Text file manipulation into CSV:

                        @stacksofplates said in Text file manipulation into CSV:

                        Looks like datamash does this.

                        0_1531436248970_datamash.png

                        so datamash is basically a transpose?

                        It has different functions. The transpose is one of them.

                        1 Reply Last reply Reply Quote 0
                        • P
                          PenguinWrangler
                          last edited by

                          I found datamash right before I posted and I had been playing with it. In the text file what I am really trying to pull out are Longitude and Latitude coordinates.

                          Longitude: 38.6270
                          Latitude: 90.1994

                          then I need them to be

                          Longitude,Latitude
                          38.6270,90.1994

                          datamash transpose gives me:
                          Longitude: 38.6270 Latitude: 90.1994

                          J S 2 Replies Last reply Reply Quote 0
                          • J
                            JaredBusch @PenguinWrangler
                            last edited by

                            @penguinwrangler said in Text file manipulation into CSV:

                            I found datamash right before I posted and I had been playing with it. In the text file what I am really trying to pull out are Longitude and Latitude coordinates.

                            Longitude: 38.6270
                            Latitude: 90.1994

                            then I need them to be

                            Longitude,Latitude
                            38.6270,90.1994

                            datamash transpose gives me:
                            Longitude: 38.6270 Latitude: 90.1994

                            sed

                            P 1 Reply Last reply Reply Quote 0
                            • P
                              PenguinWrangler @JaredBusch
                              last edited by

                              @jaredbusch said in Text file manipulation into CSV:

                              @penguinwrangler said in Text file manipulation into CSV:

                              I found datamash right before I posted and I had been playing with it. In the text file what I am really trying to pull out are Longitude and Latitude coordinates.

                              Longitude: 38.6270
                              Latitude: 90.1994

                              then I need them to be

                              Longitude,Latitude
                              38.6270,90.1994

                              datamash transpose gives me:
                              Longitude: 38.6270 Latitude: 90.1994

                              sed

                              I will give it a try.

                              1 Reply Last reply Reply Quote 0
                              • J
                                JaredBusch
                                last edited by

                                Examples in guides:
                                https://mangolassi.it/topic/12501/installing-wordpress-on-centos-7-minimal
                                https://mangolassi.it/topic/16471/install-bookstack-on-fedora-27

                                # Remove Longitude completely
                                sed -i -e 's/Longitude: //' ~/your.csv
                                # Replace Latitude with a comma
                                sed -i -e 's/ Latitude: /,/' ~/your.csv
                                

                                If you need to escape the spaces or the colons, use \: and \ <--space after that

                                1 Reply Last reply Reply Quote 2
                                • S
                                  stacksofplates @PenguinWrangler
                                  last edited by

                                  @penguinwrangler said in Text file manipulation into CSV:

                                  I found datamash right before I posted and I had been playing with it. In the text file what I am really trying to pull out are Longitude and Latitude coordinates.

                                  Longitude: 38.6270
                                  Latitude: 90.1994

                                  then I need them to be

                                  Longitude,Latitude
                                  38.6270,90.1994

                                  datamash transpose gives me:
                                  Longitude: 38.6270 Latitude: 90.1994

                                  Yeah that's why that script I posted removes the colon. That makes it difficult.

                                  1 Reply Last reply Reply Quote 0
                                  • P
                                    PenguinWrangler
                                    last edited by

                                    So I think I am going to do this: Use sed to remove everything but the two cordinates and then get them transposed. Then take them and insert them into a new file in this format:

                                    <kml>
                                            <Placemark>
                                    		<Point>
                                    			<gx:drawOrder>1</gx:drawOrder>
                                    			<coordinates>45.0022544,35.35499874654,0</coordinates>
                                    		</Point>
                                    	</Placemark>
                                    </kml>
                                    

                                    Then save it as a KML file that google earth can read. Should have this down today now. Thanks guys!

                                    1 Reply Last reply Reply Quote 2
                                    • P
                                      PenguinWrangler
                                      last edited by

                                      Got it. First off thanks to @JaredBusch for suggesting sed. Thanks to everyone that suggested something as well. Don't know if it is the best way of doing it but here is what worked

                                      for i in *.txt
                                      do 
                                      sed -i '1d;2d;3d;4d;5d;6d;7d;8d;9d;10d;11d;14d;15d;16d;17d' $i
                                      sed -i 's/Longitude: /,/g' $i
                                      sed -i 's/Latitude: //g' $i
                                      tr '\n\r' ' ' < $i | sponge $i
                                      sed -i 's/[[:blank:]]//g' $i
                                      sed -i 's/[[:space:]]//g' $i
                                      sed -i '1i\
                                      <kml><Placemark><Point><gx:drawOrder>1</gx:drawOrder><coordinates>
                                      ' $i
                                      sed -i '${s/$/0\<\/coordinates\>\<\/Point\>\<\/Placemark\>\<\/kml\>/}' $i
                                      mv "$i" "$i".kml
                                      done
                                      
                                      1 Reply Last reply Reply Quote 2
                                      • P
                                        PenguinWrangler
                                        last edited by PenguinWrangler

                                        Okay actually this is what it ended up being. I didn't realize it but I had the long and lat reversed(everything kept showing up in the antartic so I knew something was amiss), so I had to add a line to reverse them.

                                        for i in *.txt
                                        do 
                                        sed -i '1d;2d;3d;4d;5d;6d;7d;8d;9d;10d;11d;14d;15d;16d;17d' $i
                                        sed -i 's/Longitude: //g' $i
                                        sed -i 's/Latitude: /,/g' $i
                                        tac $i | sponge $i
                                        tr '\n\r' ' ' < $i | sponge $i
                                        sed -i 's/[[:blank:]]//g' $i
                                        sed -i 's/[[:space:]]//g' $i
                                        sed -i '1i\
                                        <kml><Placemark><Point><gx:drawOrder>1</gx:drawOrder><coordinates>
                                        ' $i
                                        sed -i '${s/$/0\<\/coordinates\>\<\/Point\>\<\/Placemark\>\<\/kml\>/}' $i
                                        mv "$i" "$i".kml
                                        done
                                        
                                        1 1 Reply Last reply Reply Quote 2
                                        • 1
                                          1337 @PenguinWrangler
                                          last edited by

                                          @penguinwrangler
                                          Good work! I admit I would have been to lazy to go through all that. I would just have written a program to deal with it straight up instead instead of trying to use nix commands and scripting. Anything to avoid "escape hell" as I like to call it.

                                          1 Reply Last reply Reply Quote 0
                                          • 1
                                          • 2
                                          • 2 / 2
                                          • First post
                                            Last post