Text file manipulation into CSV
-
I have several text files that are organized like below
X: Y
B: C
G: Fand I need them to go into a csv file and look like
X,B,G
Y,C,FSeveral rows of information separated by a ":" I need to take what is on the left make them into the top row of a CSV and then what is on the right into the CSV data. I am not a huge PowerShell scripting guy and this is time sensitive or else I would figure this out on my own and I haven't done a lot of manipulating text in Linux. This can be a Linux or Windows solution. I am researching this but if any of you have a solution that would be great. I am getting close but like I said time sensitive matter. Also, I have thousands of files I need to process so doing this by hand is out of the question.
-
So you want only two rows in the CSV, no matter how many rows are in the original?
-
This is more difficult because you are not just manipulating the text, but pivoting the results.
-
This post is deleted! -
This post is deleted! -
Looks like datamash does this.
-
You'll have to take care of any delimiter operations you want to do afterward.
-
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
vsyum
vsapt
. 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
-
Also I have a feeling that was the most edited post on this site ha.
-
@stacksofplates Your kung fu is strong,
-
@stacksofplates said in Text file manipulation into CSV:
Looks like datamash does this.
so
datamash
is basically a transpose? -
@jaredbusch said in Text file manipulation into CSV:
@stacksofplates said in Text file manipulation into CSV:
Looks like datamash does this.
so
datamash
is basically a transpose?It has different functions. The transpose is one of them.
-
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.1994then I need them to be
Longitude,Latitude
38.6270,90.1994datamash transpose gives me:
Longitude: 38.6270 Latitude: 90.1994 -
@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.1994then I need them to be
Longitude,Latitude
38.6270,90.1994datamash transpose gives me:
Longitude: 38.6270 Latitude: 90.1994sed
-
@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.1994then I need them to be
Longitude,Latitude
38.6270,90.1994datamash transpose gives me:
Longitude: 38.6270 Latitude: 90.1994sed
I will give it a try.
-
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 -
@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.1994then I need them to be
Longitude,Latitude
38.6270,90.1994datamash transpose gives me:
Longitude: 38.6270 Latitude: 90.1994Yeah that's why that script I posted removes the colon. That makes it difficult.
-
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!
-
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
-
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