3

I have a big non-standard xhtml file that I'm using sed (about 4 times) to iterate down to just the basics I need to throw the data into a MySQL database. My last bit I'm struggling with. The file is formatted like:

 Tue Aug 18 2015
0,0,0,0,0
0,0,0,2,275
0,0,0,3,287
0,0,0,0,327
0,0,0,3,335
0,0,0,0,413
 Wed Aug 19 2015
0,0,0,0,0
0,0,0,2,275
0,0,0,3,287
0,0,0,2,308
 Thu Aug 20 2015
0,0,0,0,0
0,0,0,2,458
0,0,0,3,469
0,0,0,0,472
0,0,0,3,503
0,0,0,2,534

There is always a space before the day. There can be any number of CSV value lines after the date.

What I'm hoping can be achieved is:

Tue Aug 18 2015,0,0,0,0,0
Tue Aug 18 2015,0,0,0,2,275
Tue Aug 18 2015,0,0,0,3,287
Tue Aug 18 2015,0,0,0,0,327
Tue Aug 18 2015,0,0,0,3,335
Tue Aug 18 2015,0,0,0,0,413
Wed Aug 19 2015,0,0,0,0,0
Wed Aug 19 2015,0,0,0,2,275
Wed Aug 19 2015,0,0,0,3,287
Wed Aug 19 2015,0,0,0,2,308
Thu Aug 20 2015,0,0,0,0,0
Thu Aug 20 2015,0,0,0,2,458
Thu Aug 20 2015,0,0,0,3,469
Thu Aug 20 2015,0,0,0,0,472
Thu Aug 20 2015,0,0,0,3,503
Thu Aug 20 2015,0,0,0,2,534

and if it's possible, to strip the day and throw in a few more commas for easier manipulation in a PHP script, like:

Aug,18,2015,0,0,0,0,0
Aug,18,2015,0,0,0,2,275
Aug,18,2015,0,0,0,3,287
Aug,18,2015,0,0,0,0,327
Aug,18,2015,0,0,0,3,335
Aug,18,2015,0,0,0,0,413
Aug,19,2015,0,0,0,0,0
Aug,19,2015,0,0,0,2,275
Aug,19,2015,0,0,0,3,287
Aug,19,2015,0,0,0,2,308
Aug,20,2015,0,0,0,0,0
Aug,20,2015,0,0,0,2,458
Aug,20,2015,0,0,0,3,469
Aug,20,2015,0,0,0,0,472
Aug,20,2015,0,0,0,3,503
Aug,20,2015,0,0,0,2,534

Is there a few commands that can be used?

  • Just to be clear, the day/date line never contains any comma whereas all other lines contain at least one comma ? – don_crissti May 25 '16 at 21:59
  • Yep. That's the way the files been edited down to. Thanks! – Nick Twigg May 25 '16 at 22:00
  • 1
    Also, if you're using sed 4 times already then something's not right... You should be able to do pretty much anything in 2 passes. – don_crissti May 25 '16 at 22:33
  • In addition to what @don_crissti said about 2 passes, the xml2 package is a LOT less fussy about improperly formatted XML or XHTML than xmlstarlet (and easier to use too). xml2's purpose is to convert XML/HTML data into a line-oriented FLAT format suitable for use with tools like awk, sed, grep, etc. It can also convert to/from CSV files. Useful examples here: blog.mydream.com.hk/howto/linux/convert-xml-to-csv-using-xml2 (ignore the stuff about compiling it, it's probably already packaged for your distro) – cas May 26 '16 at 5:52
  • This is a one-time batch process that I was running - I'll no doubt have to make something similar in the future where I'll hopefully use what I've learned here as a starter and get the amount of runs down! Thanks for the assistance! – Nick Twigg Jun 3 '16 at 23:19
3

Here's one way to do it:

sed '/,/!{                       # if there's no comma on this line
y/ /,/                           # translate spaces to commas
h                                # copy pattern space over the hold buffer
d                                # delete pattern space
}
//{                              # if the line contains commas
G                                # append hold space content to pattern space
s/\(.*\)\n,[^,]*,\(.*\)/\2,\1/   # swap lines removing newline, the day part and
}                                # first two commas and adding a comma after year
' infile

If you prefer a gnu sed one-liner:

sed -E '/,/!{y/ /,/;h;d};//{G;s/(.*)\n,[^,]*,(.*)/\2,\1/}' infile

It's similar with awk:
If the line contains no commas you could format the date via sprintf, save the result into a variable e.g. dt and then go to next record. Else just prepend dt to $0 (that is the current line):

awk '!/,/{dt=sprintf("%s,%s,%s,", $2, $3, $4);next};$0=dt$0' infile
0
awk -F, -v OFS=, '/^[[:blank:]]+/ {
                      str=gensub(/ /,",","g",$0);
                      sub(/^,+[^,]+,/,"",str);
                      next
                  };

                  !/^[[:blank:]]+/ {print str,$0}' nick.txt

(this can, of course, be all on one line. I wrote and tested it as a one-liner, and then added line-feeds and indentation to make it more readable here)

For lines beginning with one or blank characters (i.e. spaces or tabs), this awk script, converts all spaces to commas, saves the modified line in a variable called str, and then removes the initial comma(s) and all text up to and including the next comma.

For lines not beginning with blank character(s), it prints the line prefixed with the current value of str.

Warning: if there are any CSV data lines before the first date line, those lines will be printed with only a single comma as the prefix.

Output:

Aug,18,2015,0,0,0,0,0
Aug,18,2015,0,0,0,2,275
Aug,18,2015,0,0,0,3,287
Aug,18,2015,0,0,0,0,327
Aug,18,2015,0,0,0,3,335
Aug,18,2015,0,0,0,0,413
Aug,19,2015,0,0,0,0,0
Aug,19,2015,0,0,0,2,275
Aug,19,2015,0,0,0,3,287
Aug,19,2015,0,0,0,2,308
Aug,20,2015,0,0,0,0,0
Aug,20,2015,0,0,0,2,458
Aug,20,2015,0,0,0,3,469
Aug,20,2015,0,0,0,0,472
Aug,20,2015,0,0,0,3,503
Aug,20,2015,0,0,0,2,534
  • btw, why don't you just use a date field in your mysql database? Then you wouldn't need to munge the date with extra commas. – cas May 26 '16 at 5:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.