0

I have a CSV file containing user information:

'Arlington', '1,3,5,7,9'
'StackExchange', '2,3'

And I will need the above information imported like this:

"User" table:

id | name
1  | 'Arlington'
2  | 'StackExchange'

"User groups" table: 

id | user_id | group_id
1  | 1       | 1
2  | 1       | 3
3  | 1       | 5
4  | 1       | 7
5  | 1       | 9
6  | 2       | 2
7  | 2       | 3

What's the easiest way to do this? I have imported the data with a temp column holding the CSV values:

id | name            | tmp_group_ids
1  | 'Arlington'     | '1,3,5,7,9'
2  | 'StackExchange' | '2,3'

I am thinking if I import it this way, I will know exactly what id gets assigned for the user (the id column in the users table is auto_increment), and so I can use that id as user_id for the "user groups" table.

But now how do I get values from tmp_group_ids into the "User groups" table?

Would appreciate any help! Thanks!

Arlington
  • 253
  • 1
  • 3
  • 5

4 Answers4

1

the easy way would be a php or perl script.

alinoz
  • 2,822
  • 22
  • 38
0

You can use the MySQL SUBSTRING() function to split the string and insert the different values into the table. You can do this by writing a function or using a stored procedure.

Jan S
  • 1,831
  • 15
  • 21
  • If there's no characters in the CSV fields that are escaped, at least. Given that, it would be the most straightforward solution. – G_H Oct 25 '11 at 10:06
0

I had recently a similar problem, I used the function SUBSTRING_INDEX(str,delim,count), using "," as delimiter

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

INSERT INTO tableUserGroup (userid, groupid)
SELECT 
    t1.id
    , substring_index(t1.tmp_group_ids,',',2)
    , substring_index(t1.tmp_group_ids,',',3)
FROM table1 t1
Andrew Strathclyde
  • 327
  • 4
  • 10
  • 23
  • Found your similar question on SE: [link](http://stackoverflow.com/questions/7647499/mysql-extract-data-from-csv-string-and-insert-into-a-second-table) So I guess I can call this N times if I know where will be N values at most in the CSV column, as something quick and dirty? `INSERT INTO user_group (user_id, group_id) SELECT id, substring_index(tmp_group_ids,',',[N]) FROM users` – Arlington Oct 25 '11 at 10:19
0

First, insert the names into the User table - with id autonumber, this will work:

INSERT INTO User
  (name)
SELECT DISTINCT
    name
FROM TempTable

Then:

--- Create a "numbers" table:
CREATE TABLE num
( i INT PRIMARY KEY
) ;

--- Populate it with numbers:
INSERT INTO num
  (i)
VALUES
  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Then, you can use FIND_IN_SET() function which is handy for this situation (splitting comma-separated fields), like this:

INSERT INTO User_Groups
  (user_id, group_id)
SELECT
    u.id  AS user_id
  , num.i AS group_id
FROM User AS u
  JOIN TempTable AS t
    ON t.name = u.name
  JOIN num
    ON FIND_IN_SET(num.i, t.tmp_group_ids) > 0
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235