How to insert tab delimited file in mysql with relation

Question

I want to embed data in a mysql table, but I cannot find a way to make relationships from a single row

Suppose I have a file.tab file, it contains data in the format

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 achieve is to insert data into two tables, for example

  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 | +---+-----+-----------+-------------------+ 

the first two columns are for the parent, and after that two or two columns refer to the child ones, but I don’t know how many parents the parent has.

I tried to upload the file this way.

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

but what to do next, I have no idea.

so kindly help me in this matter.

Answer 1

Create a table ( Staging ) with a large number of columns. You have empty columns ( NULL ) for parent_id and IDs for children.

Hopefully the “short” rows put zeros in the columns of the missing children during LOAD DATA .

INSERT .. SELECT .. to get the parent and parent_detail in the Parents table. Pull ids from Parents to Staging.parent_id . Details of these two SQL for them 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 a NULL pair) and currently NULL child1_id . Same for child2 * etc. Note that when populating the Children table, you already have parent_id .

This is an all-SQL way to accomplish a task. This is a little less messy than writing Perl / PHP / Java / VB / any code to complete a task.

Answer 2

Assuming that children and parents are a Person, I would create a Person table only with an optional id_parent.

 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 download data is very dependent on your favorite language. Load data infile requires a static result table.

The number of rows you have may vary, and you will need to import each row as a single column. Then you can use the stored procedure to iterate over it:

• See cursors to see how to iterate over each row of such a column layout table.
• Using replace in the tab delimiter, you can determine the number of columns in each row.
• Using the while , you can start importing the children first and then the parents.

In fairness, it would be a rather complicated stored procedure, and for a beginner it is probably quite difficult to write. If you are familiar with any programming language and its means of connecting to MySQL, you can probably do it much more elegantly.

Answer 3

If the solution does not have to be in SQL, I find preprocessing often easier. In this case, split the data into two files that can be trivially loaded using LOAD DATA INFILE (one for the parent table and one for the child table).

Here is one way to do preprocessing with 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"; }