Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Unanswered: Automatic table create from text file

    Hello . . .
    I am new in mySQL. I am facing a problem.
    Is it possible to create table from reading a text file?
    say i have a text file which contains:

    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

    Now i want to create 3 tables (sakib, sajib & aftab) & each tables field will be (red, blue & green).
    So I want the DB table will be as follows:

    table apple:
    red green blue
    8 2 3
    42 5 3
    0 2 4
    1 2 3

    table orrange:
    red green blue
    2 3 3
    5 6 11
    2 2 4

    table mango:
    red green blue
    5 8 3
    5 6 8
    4 5 4
    4 9 8


    Where the database will be predefined.
    actually the above is an example, my data is huge so i need to find an automated way.

    I can load the table value using "LOAD DATA INFILE". But i need to create the table also. Is there any way to do this?
    Also the sample text file is attached.

    Thanks in advance.


    Sakibnaz.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2008
    Posts
    28
    Not sure if you found your answer yet or not... but you should be able to create a text file of SQL commands that would create the table(s), and then source that (or cut-n-paste from the file into phpMyAdmin), and have it load from your data file. I'd make a small version of your data file and try it with that first.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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);
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •