5

I have several files, each one with different number of columns. I want to convert them to insert them in a database

For example the file test01:

0001    000000000000001 john smith  45  500
0002    000000000000002 peter jackson   20  80
0003    000000000000002 robert brown    35  100
0004    000000000000007 sarah white 40  300

My desired output is:

('0001','000000000000001','john smith','45','500'),
('0002','000000000000002','peter jackson','20','80'),
('0003','000000000000002','robert brown','35','100'),
('0004','000000000000007','sarah white','40','300');

to achive this I use the following script:

cat test01 |awk -F'\t' '{print "('\''"$1"'\'','\''"$2"'\'','\''"$3"'\'','\''"$4"'\'','\''"$5"'\''),"}' |sed '$ s/.$/;/' 

And it works fine, the problem is when I find another file with a different number of columns, so I have to modified the script manually.

I know I can get the number of column withe the AWK's variable NF, but how to combine this variable with a for loop in the script?

When I try

cat test01 | awk '{for (i = 1; i <= NF; i++){print $i"'\'','\''"}}'

I get this result:

0001','
000000000000001','
john','
smith','
45','
500','
0002','
000000000000002','
peter','
jackson','
20','
80','
0003','
000000000000002','
robert','
brown','
35','
100','
0004','
000000000000007','
sarah','
white','
40','
300','
2
  • In the original file, is there a tab delimiter, or is it just "some whitespace"? Commented Sep 3, 2017 at 16:32
  • It is a tab delimiter Commented Sep 3, 2017 at 17:07

4 Answers 4

5

If your input file is tab separated you can try the following:

awk -F"\t" -v q="'" -v OFS="','" '$1=$1 {print "(" q $0 q ");"}' filename

Or embed quotes in the print function:

awk -F"\t" -v OFS="','" '$1=$1 {print "(" "\x27" $0 "\x27" ");"}' filename
3
  • Watch the commas at the end of all lines except for the last one...
    – Kusalananda
    Commented Sep 3, 2017 at 14:44
  • 2
    Good notice there @Kusalananda. I did a quick fix as in awk -F"\t" -vq="'" -vOFS="','" '$1=$1 {print "(" q $0 q "),"}' filename | sed '$s/,$/;/'. Any better suggestion? Commented Sep 3, 2017 at 14:58
  • 1
    No, that's a good fix.
    – Kusalananda
    Commented Sep 3, 2017 at 14:59
4

Using GNU sed:

$ sed -e "s/^/('/" -e "s/\t/','/g" -e "s/$/'),/" -e '$s/.$/;/' file
('0001','000000000000001','john smith','45','500'),
('0002','000000000000002','peter jackson','20','80'),
('0003','000000000000002','robert brown','35','100'),
('0004','000000000000007','sarah white','40','300');

The sed script is in four parts:

  1. s/^/('/ replaces start of line with ('.
  2. s/\t/','/g replaces tabs with ','. This is the bit that requires GNU sed. For other sed implementations, insert a literal tab in place of \t.
  3. s/$/'),/ replaces end of line with '),.
  4. $s/.$/;/ replaces the comma at the end of the last line (only) with ;.
2
  • I run the script and it prompts ">" as expecting for something Commented Sep 3, 2017 at 14:37
  • @user3333911 I had mismatched quotes, from an earlier version. Fixed now, and tested.
    – Kusalananda
    Commented Sep 3, 2017 at 14:43
0

To reach the same behaviour as you want with your initial script you can use "printf" method of awk. It makes possible to get rid of newlines which are put by "print". I am guessing, your script should be rewritten like this:

cat test01 | awk '{for (i = 1; i <= NF; i++){printf $i"'\'','\''"}; printf "\n";}'
0

Here is my stab at it....

My statement consists of 3 parts: cat, awk, sed

The awk and sed statement can definitely be improved, but I am still learning them both. I made test01 to test2.txt

cat test2.txt | awk -F "\\t| " 'BEGIN {ORS=""}{print "("}{ORS=","}{for (i = 1; i <= NF; i++){print "'\''"$i"'\''"}}{print ")\n"}{ORS=""}' | sed 's/,)/),/' | sed 's/^,//' | sed '$ s/),/);/'

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .