Tuesday, May 7, 2024
Homepage · command-line
 Popular · Latest · Hot · Upcoming
8
rated 0 times [  8] [ 0]  / answers: 1 / hits: 2387  / 3 Years ago, wed, august 25, 2021, 3:17:12

I would like to convert a text file into CSV format.


Here is an excerpt from the file:


{"Outdated":false,"Watt":233,"Timestamp":1669647142,"A_Plus":6523.896,"A_Plus_HT":4494.82,"A_Plus_NT":2029.076,"A_Minus":8841.63}

{"Outdated":false,"Watt":235,"Timestamp":1669647152,"A_Plus":6523.896,"A_Plus_HT":4494.82,"A_Plus_NT":2029.076,"A_Minus":8841.63}

{"Outdated":false,"Watt":235,"Timestamp":1669647596,"A_Plus":6523.896,"A_Plus_HT":4494.82,"A_Plus_NT":2029.076,"A_Minus":8841.63}

{"Outdated":false,"Watt":233,"Timestamp":1669651191,"A_Plus":6524.496,"A_Plus_HT":4495.42,"A_Plus_NT":2029.076,"A_Minus":8841.63}

{"Outdated":false,"Watt":276,"Timestamp":1669654797,"A_Plus":6524.816,"A_Plus_HT":4495.74,"A_Plus_NT":2029.076,"A_Minus":8841.63}

{"Outdated":false,"Watt":437,"Timestamp":1669658393,"A_Plus":6525.901,"A_Plus_HT":4496.825,"A_Plus_NT":2029.076,"A_Minus":8841.63}

{"Outdated":false,"Watt":362,"Timestamp":1669661992,"A_Plus":6526.732,"A_Plus_HT":4497.656,"A_Plus_NT":2029.076,"A_Minus":8841.63}

{"Outdated":false,"Watt":471,"Timestamp":1669665603,"A_Plus":6527.062,"A_Plus_HT":4497.986,"A_Plus_NT":2029.076,"A_Minus":8841.63}

The structure resembles a JSON file, but unfortunately the structure does not quite match. I have already used this tool (https://github.com/alingse/jsoncsv), but unfortunately I get error messages because of the wrong structure.


How can I turn it into a CSV file?


Thanks for your help!


More From » command-line
 Answers
7

For me it seems easier and safer to first convert to proper json, then use a parser (e.g. jq or jsoncsv that you have tried) instead of writing your own parser.


Convert to json by simply adding [ in then first row and append ] after the last row and a comma to every except the last row.


sed '1s/^/[/;$!s/$/,/;$a]' file

Then convert to csv using jq


jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ]])[] | @csv'

And put it together:


$ sed '1s/^/[/;$!s/$/,/;$a]' file | jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ]])[] | @csv'

"Outdated","Watt","Timestamp","A_Plus","A_Plus_HT","A_Plus_NT","A_Minus"

false,233,1669647142,6523.896,4494.82,2029.076,8841.63

false,235,1669647152,6523.896,4494.82,2029.076,8841.63

false,235,1669647596,6523.896,4494.82,2029.076,8841.63

false,233,1669651191,6524.496,4495.42,2029.076,8841.63

false,276,1669654797,6524.816,4495.74,2029.076,8841.63

false,437,1669658393,6525.901,4496.825,2029.076,8841.63

false,362,1669661992,6526.732,4497.656,2029.076,8841.63

false,471,1669665603,6527.062,4497.986,2029.076,8841.63

[#133] Friday, August 27, 2021, 3 Years  [reply] [flag answer]
Only authorized users can answer the question. Please sign in first, or register a free account.
rieency
Add To Favorites
Follow
Total Points: 299
Total Questions: 116
Total Answers: 111
Location: Wales
Member since Tue, Dec 14, 2021
2 Years ago
rieency questions
Wed, Jun 23, 21, 04:37, 3 Years ago
Fri, Sep 30, 22, 12:07, 2 Years ago
Thu, Feb 24, 22, 00:50, 2 Years ago
Mon, Mar 28, 22, 13:28, 2 Years ago
Tue, Sep 20, 22, 15:25, 2 Years ago
View All
;