ansaurus

Question

Extract Data from CSV in Bash script (Sed, AWK, Grep?)

Answer 1

+2 A:

I suggest you use PHP to de-serialize the structure. However, here's a quick and dirty version of what you want using sed and tr. Certainly you can do this much much better:

cat file.csv | \
tr ",;" "\n" | \
sed -e 's/[asbi]:[0-9]*[:]*//g' -e '/^[{}]/d' -e 's/""//g' -e '/^"{/d' | \
sed -n -e '/^"/p' -e '/^print_value$/,/^option_id$/p' | \
sed -e '/^option_id/d' -e '/^print_value/d' -e 's/^"\(.*\)"$/\1/' | \
tr "\n" "," | \
sed -e 's/,\([0-9]*-[0-9]*-[0-9]*\)/\n\1/g' -e 's/,$//' | \
sed -e 's/^/"/g' -e 's/$/"/g' -e 's/,/","/g'

The explanation:

split by commas and semicolons
remove remove the php structure syntax s:X:Y, b:X, ... and remove lines starting with { or } or "{
extract the section from print_value to the next option_id, also keep the date (line start with ")
remove those labels (print and option), and remove quotations around the date
concat all lines with commas
seperate lines (starting with date pattern), and remove extra comma at end
add quotations around all fields

Wow, I know it's embarrassing :)

Amro 2009-09-21 04:43:31

Works perfectly, and thank you for the explanation.

BassKozz 2009-09-21 14:37:35

Answer 2

A:

Here is my anwser:


cat TestData \
    | grep -o -P "print_value\"\";.*?:\"\".*?\"\";" \
    | perl -pe 's|print_value.*:\"\"(.*?)\"\";|\1|'

The first line show the data (stored in TestData).

The second line asks grep to separate each match from print_value to the nearest '"";'. Notice that I use '.*?' for non greedy match (needs to use '-P' with it).

The last line use perl to strip all un-needed. See that I use '(.*?)' to match the needed group and use '\1' to show the group.

Hope this helps.

NawaMan 2009-09-21 04:48:34

This just put each answer on it's own line. 1 column. Doesn't work for a comma-separated CSV.

BassKozz 2009-09-21 14:33:47

Sorry. I forgot the part to put them back together. This is just the extracting part.

NawaMan 2009-09-21 14:42:04

ansaurus

tags:

views:

answers:

Extract Data from CSV in Bash script (Sed, AWK, Grep?)

related questions