Convert flat file to sql script 1

[evkruining]

Hi,

I would like to automatically convert a plain text file containing a playlist to a sql script to import the proper data into an existing table. I'm running a linux server with all these powerful built-in tools like awk, sed and grep but I don't know how to use them proper. I guess a fancy shell script could do the job. Any suggestions?

The filename is 63.txt with something like this:

01 This Is The Artist Name - And the Song
02 Another Artist - Bla-Di-Bla Song
03 Cool new Band - Sing Sing Sing
04 The Singer-Songwriter - I'll write you a new song
05 etc - etc etc
..
..
..

The resulting sql script should look like this:

INSERT INTO playlist VALUES (63, 01, 'This Is The Artist Name', 'And the Song');
INSERT INTO playlist VALUES (63, 02, 'Another Artist', 'Bla-Di-Bla Song');
INSERT INTO playlist VALUES (63, 03, 'Cool new Band', 'Sing Sing Sing');
INSERT INTO playlist VALUES (63, 04, 'The Singer-Songwriter', 'I'll write you a new song');
INSERT INTO playlist VALUES (63, 05, 'etc', 'etc etc');
..
..
..

Thanks in advance for your suggestions.

 

[stefanwagner]

I don't think you would like this:

INSERT INTO playlist VALUES (63, 04, 'The Singer-Songwriter', 'I'll write you a new song');
[code]
Creat a sed-script pl.sed:
[code]
s/^\(..\) /INSERT INTO playlist VALUES (63, \1, "/g
s/ - /", "/g
s/$/");/g
s/'/''/g
s/"/'/g
[code]
and call it like this:
[code]
sed -f pl.sed 63.txt
[code]
Afaik most databases interpret '' as a masked '.
Perhaps you should grep for " before, and replace it with a character neither used by artists-names, nor by song-titles.

seeking a job as java-programmer in Berlin: [URL unfurl="true"]http://home.arcor.de/hirnstrom/bewerbung[/URL]

 

[stefanwagner]

(Sorry - broke the tags.)

I don't think you would like this:

INSERT INTO playlist VALUES (63, 04, 'The Singer-Songwriter', 'I'll write you a new song');

Creat a sed-script pl.sed:

s/^\(..\) /INSERT INTO playlist VALUES (63, \1, "/g
s/ - /", "/g
s/$/");/g
s/'/''/g
s/"/'/g

and call it like this:

sed -f pl.sed 63.txt

Afaik most databases interpret '' as a masked '.
Perhaps you should grep for " before, and replace it with a character neither used by artists-names, nor by song-titles.

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[KenCunningham]

Is this not what sqlloader is for? Or have I missed the point?

 

[KenCunningham]

Sorry - thought I was in an Oracle forum!!

 

[evkruining]

I've tweaked Stefans code a bit to make it a one liner that I can include into another script that does the mysql import too. The thing with the double qoutes also works out very well. The line I use now is:

sed "s/^\(..\) /INSERT INTO playlist VALUES ($1, \1, \"/g;s/ - /\", \"/g;s/$/\");/g" $1.txt >>result.sql

Works great, even with the extended characters in the text strings.

Thanks for helping out!