Hi, there is a UNIX/Linux tool called awk which is great for manipulating text formatted files into something more meaningful. I have written a small awk application which will convert the file contents to a CREATE and INSERT statements.
Firstly we have your text file
Code:
$ cat test.txt
red blue green
apple
8 2 3
42 5 3
0 2 4
1 2 3
orange
2 3 3
5 6 11
2 2 4
2 9 6
mango
5 8 3
5 6 8
4 5 4
4 9 8
The we also have the awk program file, which is quite small:
Code:
$ cat test.awk
BEGIN {
line=0;
}
{
// Get the field names from the first line
if(line==0) {
for(i=0; i<NF; i++) {
fields[i] = $(i+1);
}
} else {
if(NF == 1) {
tablename = $1;
printf("CREATE TABLE %s (", tablename);
for(i=0;i<length(fields); i++) {
if(i==0) {
printf("%s int", fields[i]);
} else {
printf(", %s int", fields[i]);
}
}
printf(");\n");
} else {
printf("INSERT INTO %s VALUES(%s, %s, %s);\n", tablename, $1, $2, $3);
}
}
line++;
}
END {
}
To execute this issue the following command and view the results below
Code:
$ awk -f test.awk test.txt
CREATE TABLE apple (red int, blue int, green int);
INSERT INTO apple VALUES(8, 2, 3);
INSERT INTO apple VALUES(42, 5, 3);
INSERT INTO apple VALUES(0, 2, 4);
INSERT INTO apple VALUES(1, 2, 3);
CREATE TABLE orange (red int, blue int, green int);
INSERT INTO orange VALUES(2, 3, 3);
INSERT INTO orange VALUES(5, 6, 11);
INSERT INTO orange VALUES(2, 2, 4);
INSERT INTO orange VALUES(2, 9, 6);
CREATE TABLE mango (red int, blue int, green int);
INSERT INTO mango VALUES(5, 8, 3);
INSERT INTO mango VALUES(5, 6, 8);
INSERT INTO mango VALUES(4, 5, 4);
INSERT INTO mango VALUES(4, 9, 8);
dBforums
>
Automatic table create from text file
04-01-10, 11:44
Linear Mode
