Shell Programming and Scripting

Hi everyone,
I have a large text file that is output of mysql select, like:
pet:
+--------------+-------------+---------+------+------------+
| name | owner | species | sex | birth |
+--------------+-------------+---------+------+------------+
| Mickey Mouse | Walt Disney | mouse | m | NULL |
| Tom | Walt Disney | cat | m | 1960-03-03 |
| Jery | Walt Disney | mouse | m | NULL |
| Spoky | Trey Parker | fish | NULL | 2001-06-13 |
| Fluffy | Harold | Hamster | f | 1993-02-04 |
.....skip......
Now I wanna to insert the same data into another MySQL table, but I have only this text file output.
I believe it is possible to make this file suitable for using MySQL's 'load data' statement by means either awk or sed, it means suitable format like
Mickey Mouse Walt Disney mouse m \N
Tom Walt Disney cat m 1960-03-03
.......
with tab as a delimiter,ending each line with EOL character and replace each 'NULL' value with '\N'
My knowledge of awk is weak, please any help?

Hi, that's quite easy

Let's suppose that the file is as you told with a header of 4 lines 'pet:\n+--...' and has been put in the file "pet_1.txt"

then a simple small script will do it (not clever, nor optimised, but it works) :

Code:

sed -e '1,4d' -e 's/[ ]*|[ ]*/|/g' -e "s/^|//" -e "s/|$//" pet_1.txt | awk -F "|" '\
{ \
  s = ""; \
  for( i = 1; i <= NF; i++) { \
    if ($i == "NULL") $i = "\\N"; \
    if (s == "" ) s = $i; \
    else s = sprintf("%s %s", s, $i); \
  } \
  print s; }' > new_pet.txt

but beware, there are items here that contain spaces ! don't know the MySQL load format, but you should do somthing for it because it won't load as you want (maybe by enclosing each element with '"' cars thus preceeding the code

Code:

if (s == "") s = $i; \
else  s = sprintf("%s %s", s, $i); \

with that small condition

Code:

p = match($i,"[ ]"); \
if ( p != 0 ) $i = sprintf("\"%s\"", $i); \

awk '{gsub(/[ ]*\|[ ]*/,"\t"); gsub(/NULL/,"\\N")}; {print }' file

P.S. : Use nawk on Solaris.

Thanks Locki! It works great.
You save me a lot of time and give important hints about further learn sed & awk scripting

I think my code produced what you want but not what MySQL is expecting when there blanks within the fields. If MySQL accept compound-strings surrounded by "double quote" cars. In that case, you should use

Code:

sed -e '1,4d' -e 's/[ ]*|[ ]*/|/g' -e "s/^|//" -e "s/|$//" pet_1.txt | awk -F "|" '\
{ \
  s = ""; \
  for( i = 1; i <= NF; i++) { \
    if ($i == "NULL") $i = "\\N"; \
\
    p = match($i,"[ ]"); \
    if ( p != 0 ) $i = sprintf("\"%s\"", $i); \
\
    if (s == "" ) s = $i; \
    else s = sprintf("%s\t%s", s, $i); \
  } \
  print s; }' > new_pet.txt

sudhamacs, clever solution but :

If the field contains "NULL" string then u can do this :

awk '{gsub(/\|[ ]*NULL[ ]*/,"|NULL"); gsub(/\|NULL/,"|\\N"); gsub(/[ ]*\|[ ]*/,"\t");}; {print }' file

in that case, you would better use a single sed script lol

Code:

content for "script.sed" :
----------------------------
1,4d
s/[ ]*|[ ]*/|/g
s/|NULL|/|\\N|/g
s/|$//
s/^|//
s/|/    /g

command to issue:
---------------------
sed -f script.sed pet.txt > new_pet.txt

(notice : for the last line of the script it's a tab car, not 3 blanks; writing '\t' won't do it)