Text file manipulation into CSV

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

stacksofplates

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

momurda

@stacksofplates Your kung fu is strong,

JaredBusch

@stacksofplates said in Text file manipulation into CSV:

Looks like datamash does this.

so datamash is basically a transpose?

stacksofplates

@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.

PenguinWrangler

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

JaredBusch

@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

PenguinWrangler

@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.

JaredBusch

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

stacksofplates

@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.

PenguinWrangler

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!

PenguinWrangler

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

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

1337

@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.