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

    Text file manipulation into CSV

    Scheduled Pinned Locked Moved IT Discussion
    powershellbash
    21 Posts 6 Posters 1.9k Views
    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.
    • stacksofplatesS
      stacksofplates
      last edited by

      Looks like datamash does this.

      0_1531436248970_datamash.png

      JaredBuschJ 1 Reply Last reply Reply Quote 1
      • stacksofplatesS
        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
        • stacksofplatesS
          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
          • stacksofplatesS
            stacksofplates
            last edited by

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

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

              @stacksofplates Your kung fu is strong,

              1 Reply Last reply Reply Quote 1
              • JaredBuschJ
                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?

                stacksofplatesS 1 Reply Last reply Reply Quote 0
                • stacksofplatesS
                  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
                  • PenguinWranglerP
                    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

                    JaredBuschJ stacksofplatesS 2 Replies Last reply Reply Quote 0
                    • JaredBuschJ
                      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

                      PenguinWranglerP 1 Reply Last reply Reply Quote 0
                      • PenguinWranglerP
                        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
                        • JaredBuschJ
                          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
                          • stacksofplatesS
                            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
                            • PenguinWranglerP
                              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
                              • PenguinWranglerP
                                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
                                • PenguinWranglerP
                                  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
                                    • 1 / 2
                                    • First post
                                      Last post