how to insert tab delimited file into mysql with relation

What i want is to insert data in mysql table but i unable to find the way to make relationship from one row

suppose i have a file file.tab it contains data in like

parent_1    parent_details_1    child_1.1   child_details_1.1   child_1.2   child_details_1.2 parent_2    parent_details_2    child_2.1   child_details_2.1 parent_3    parent_details_3    child_3.1   child_details_3.1   child_3.2   child_details_3.2   child_3.3   child_details_3.3 

what i want to achive is to insert data in two table like

            parent_table +---+-----------+-------------------+ |id |   name    |      details      | +---+-----------+-------------------+ | 1 |  parent_1 |  parent_details_1 | | 2 |  parent_2 |  parent_details_2 | | 3 |  parent_3 |  parent_details_3 | +---+-----------+-------------------+                 child_table +---+-----+-----------+-------------------+ |id | pid |   name    |      details      | +---+-----+-----------+-------------------+ | 1 |  1  | child_1.1 | child_details_1.1 | | 2 |  1  | child_1.2 | child_details_1.2 | | 3 |  2  | child_2.1 | child_details_2.1 | | 4 |  3  | child_3.1 | child_details_3.1 | | 5 |  3  | child_3.2 | child_details_3.2 | | 6 |  3  | child_3.3 | child_details_3.3 | +---+-----+-----------+-------------------+ 

first two columns is for parent and after that two-two columns belongs to child but i don't know how many child a parent has.

i have tried to load file that way.

LOAD DATA INFILE '/tmp/file.tab INTO TABLE ...  

but what do i do next i have no idea.

so kindly help me out in this question.

3 Answers

Answers 1

Create a table (Staging) with lots of columns. Have empty (NULL) columns for parent_id and ids for the children.

Hope that the 'short' lines will put nulls in the missing children columns during the LOAD DATA.

INSERT .. SELECT .. to get the parent and parent_detail into the Parents table. Pull back the ids from Parents into Staging.parent_id. The details on the two SQLs for these are in http://mysql.rjweb.org/doc.php/staging_table#normalization

Now do something similar for each possible "child" set of columns: child1 and child1_detail (possibly NULL pair) and the currently NULL child1_id. Ditto for child2*, etc. Note that when populating the Children table, you already have parent_id available.

This is an all-SQL way of doing the task. It is only slightly less messy than writing Perl/PHP/Java/VB/whatever code to do the task.

Answers 2

Assuming both Children and Parents are a Person I'd create a Person table only with the id_parent being optional.

CREATE TABLE person (   id int(11) NOT NULL AUTO_INCREMENT,   name varchar(50) DEFAULT NULL,   details varchar(255) DEFAULT NULL,   id_parent1 int(11) DEFAULT NULL,   id_parent2 int(11) DEFAULT NULL,   PRIMARY KEY (id) ); 

How you load the data very much depends on your favourite language. Load data infile requires a static result table.

The number of rows you have can differ and you would need to pretty much import each row as a single column. You could then use a stored procedure to iterate over it:

• Look at cursors to see how to iterate over each row of such a single column staging table.
• Using replace on the tab delimiter you could figure out the number of columns in each row.
• Using the while loop you could then start to import the children first and then the parents.

In all fairness, this would be a fairly complex stored procedure and for a beginner probably quite hard to write. If you are familiar with any programming language and its means of connecting to MySQL you probably can do this much more elegantly.

Answers 3

If the solution doesn't need to be all in SQL I find that preprocessing is often simpler. In this case split data into two files that can be trivially loaded with LOAD DATA INFILE (one for the parent table and one for the child table).

Here is one way to do the preprocessing using perl

my ( $parent_id, $child_id ) = ( 0, 0 ); my ( @parent_table, @child_table ); while (<>) {                    # for each line of input   chomp;   # split on tabs   my ( $parent_name, $parent_detail, @child_id_detail_pairs ) = split /\t/;   # create a row and parent_id for the parent table   push @parent_table, [ ++$parent_id, $parent_name, $parent_detail ];    while (@child_id_detail_pairs) { # while we have child names & details     # remove a name and details     my $child_name    = shift @child_id_detail_pairs;     my $child_details = shift @child_id_detail_pairs;      # create a row and child_id for the child table     push @child_table, [ ++$child_id, $parent_id, $child_name, $child_details ];   } }  # write this to one file to load into the parent table print "parent_table\n"; for my $row (@parent_table) {   print join( "\t", @$row ), "\n"; }  # write this to one file to load into the child table print "child_table\n"; for my $row (@child_table) {   print join( "\t", @$row ), "\n"; } 

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati