string modification in bash script

Question

I'm trying to generate SQL table header from a csv file. As I had some trouble I wrote a test snippet, here's what I've got so far:

#!/bin/bash
function quote {
   for i ; do
#       echo "\`$i\`"
        echo "\`$i\` varchar (100),\n"
   done
}

quote $(head -1 Zustaendigkeit.csv | sed 's/;/ /g')

This is the csv line I use:

Netz;Strasse;PLZ_Ort;Land;Amt;Amt_Nr;PLZ;Ort

This is what I get with the above snippet:

[root@db ~]
 174# ./tst.sh
`Netz` varchar (100),\n
`Strasse` varchar (100),\n
`PLZ_Ort` varchar (100),\n
`Land` varchar (100),\n
`Amt` varchar (100),\n
`Amt_Nr` varchar (100),\n
`PLZ` varchar (100),\n
` varchar (100),\n

In the last line it's missing Ort at the beginning of the line. When I use only "echo "\$i`"" which is commented in the above snippet I get this:

[root@db ~]
 170# ./tst.sh
`Netz`
`Strasse`
`PLZ_Ort`
`Land`
`Amt`
`Amt_Nr`
`PLZ`
`Ort

Which is missing the last `

Hope someone has the missing link for me....

Greetz Mirco

choroba · Accepted Answer · 2012-02-09 12:32:25Z

2

You probably have a Windows line end at the end of the line. Remove it: sed 's/\r//g'.

Instead of using sed, you can use a loop and set IFS to ;. Removing of the \r is done by parameter expansion.

IFS=';'
read -a a < Zustaendigkeit.csv
for b in "${a[@]}" ; do
    echo "\`${b%$'\r'}\` varchar(100)"
done

answered Feb 9, 2012 at 12:32

choroba

245k27 gold badges221 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mirco Schmidt Over a year ago

Thx that was my Prob which is now solved... Added a comma after varchar but I need the last echo'd line to be without a comma: any hint is very appreciated

Mirco Schmidt Over a year ago

stackoverflow.com/questions/3576139/… did the trick...

Zsolt Botykai · Accepted Answer · 2012-02-09 12:28:37Z

1

Why not awk? I used to do this:

 awk -v Q="'" -F "," 'NR==1 { print "create table SOMETABLE ("
                              for (i=1;i<NF;i++) {
                                  print $i " varchar2(100), "
                              }
                              print $NF " varchar2(100) );"
                      }
                      NR%1000 == 0 { print "commit;" }
                      { print "insert into SOMETABLE values ( "
                        for (i=1;i<NF;i++) {
                             print Q $i Q ", "
                        }
                        print Q $NF Q " );"
                      }
                      END { print "commit;"
                            print "exit;"
                      }' INPUT.CSV > SQL.sql

answered Feb 9, 2012 at 12:28

Zsolt Botykai

52k14 gold badges90 silver badges111 bronze badges

2 Comments

Mirco Schmidt Over a year ago

To load the data I use: LOAD DATA INFILE '/path/to/Zustaendigkeit.csv' INTO TABLE Zustaendigkeit FIELDS TERMINATED BY ';';

Zsolt Botykai Over a year ago

Well then you can use only the NR==1 part of the awk script to generate the CREATE TABLE statement.

Collectives™ on Stack Overflow

string modification in bash script

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related