Convert flat file to sql script

[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.

[wheelie34]

hi

I run a similar script, first setup DB login details and location of text file, then you will need to open text file explode it to seperate the parts you want remembering to addslashes if the file contains any 's then send the data to the DB tables you have set.

example

$user = "you";
$password = "secret";
$db = "dbname";
$server = "localhost";
$table_name = "tablename";
$file = "/route to file/63.txt";
$link = connected or die etc

$textfile = file("$file");
while (list($key, $val) = each ($textfile)){
$data = explode("?", $val); #? your trigger
if($data[0] >= 1){
$blurb = addslashes($data[4]); #if needed
$insert = mysql_query ("INSERT into $table_name values('$data[0]', '$data[1]', '$data 2]', '$blurb', '$listingdata[5])");
}
}
mysql_close($link);

[evkruining]

Thanks for your suggestion Wheelie, looks very interesting. I'll have a look at the code and results. I found another solution by using a unix shell script command. It's a one liner that I can include into another script that does the mysql import too. 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 but I'll test your solution too.

Thanks for helping out!