Need to insert CSV values in MySQL column to another table

Question

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!

alinoz · Accepted Answer · 2011-10-25 09:45:51Z

1

the easy way would be a php or perl script.

answered Oct 25, 2011 at 9:45

alinoz

2,82223 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jan S · Accepted Answer · 2011-10-25 10:01:24Z

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.

answered Oct 25, 2011 at 10:01

Jan S

1,83715 silver badges21 bronze badges

1 Comment

G_H Over a year ago

If there's no characters in the CSV fields that are escaped, at least. Given that, it would be the most straightforward solution.

Andrew Strathclyde · Accepted Answer · 2011-10-25 10:12:36Z

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

answered Oct 25, 2011 at 10:12

Andrew Strathclyde

3374 gold badges10 silver badges23 bronze badges

1 Comment

Arlington Over a year ago

Found your similar question on SE: link 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

ypercubeᵀᴹ · Accepted Answer · 2011-10-25 10:18:39Z

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

answered Oct 25, 2011 at 10:18

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

Need to insert CSV values in MySQL column to another table

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related