Text file manipulation into CSV

PenguinWrangler

I have several text files that are organized like below

X: Y
B: C
G: F

and I need them to go into a csv file and look like
X,B,G
Y,C,F

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

scottalanmiller

So you want only two rows in the CSV, no matter how many rows are in the original?

scottalanmiller

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

stacksofplates

This post is deleted!

stacksofplates

This post is deleted!

stacksofplates

Looks like datamash does this.

stacksofplates

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

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