Mysql - Import a csv file with multiple fields into a single field using LOAD DATA LOCAL INFILE

Question

I have a csv file in the following format:

phone1,phone2,phone3
11111111,22222222,333333333

I have a table in a mysql database called "phone" with a single field called phone.

How can I import all three columns from the csv file to the phone field? Basically:

select * from phone;

should return:

If I cannot use LOAD DATA LOCAL INFILE, is there another method to do this in mysql?

Thanks!

Does this answer your question? [Can mysql import a csv or other txt file into one column?](https://stackoverflow.com/questions/18554099/can-mysql-import-a-csv-or-other-txt-file-into-one-column) — nbk, Jun 12 '21 at 21:02
Unfortunately, it does not. It seems that this requires additional steps. I was hoping to do this with one step. I know I can create a temp table, insert the data there and then insert the data from the temp table to "phone" table. — ccs_2000, Jun 12 '21 at 21:18
in can't see antither option and the solution is very flexible, you can switch to a stored procdure or do it in python or other laguages — nbk, Jun 12 '21 at 21:25
yes, I thought of using stored procedure but when I tried it, I could not run LOAD DATA LOCAL INFILE in sp because mysql does not allow it. As you said, I might have to just do this with other languages. Thanks! — ccs_2000, Jun 12 '21 at 21:41

score 1 · Accepted Answer · answered Jun 12 '21 at 22:37

You can import your data into an intermediary table first and then split it into rows.

Let's say you have your whole csv line in a table import

line
11111111,22222222,33333333

You can do this by importing with newline (\n) as delimiter

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE import
FIELDS TERMINATED BY '\n'
LINES  TERMINATED BY '\n'
IGNORE 1 LINES

You can now use substring_index to split the imported csv line. What you need to know for this is the number of entries. You can get this number easily by calculating the difference in length of the csv line and itself with all the comma delimiters removed.

From that you create a sequence of numbers which in turn you can use with substring_index to extract each entry.

This can be done e.g. like this:

insert into phone (num)
select
  SUBSTRING_INDEX(SUBSTRING_INDEX(import.line, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN import
  on CHAR_LENGTH(import.line)
     -CHAR_LENGTH(REPLACE(import.line, ',', ''))>=numbers.n-1
order by
  n

You can reach the same goal using the recursive CTE

insert into phone (num)
with recursive
    N as ( select 1 as n union select n + 1 from N inner join import
        on n <= length(import.line) - length(replace(import.line, ',', '')))
    select distinct substring_index(substring_index(import.line, ',', n), ',', -1)
num from N inner join import

Another interesting approach is using json_table. For that to work, you first need to transform your csv line into a json_array

insert into phone
select j.line
from import i
join json_table(
  replace(json_array(i.line), ',', '","'),
  '$[*]' columns (line varchar(50) path '$')
) j

All of these yield the result you wanted

select * from phone

number
11111111
22222222
33333333

Mysql - Import a csv file with multiple fields into a single field using LOAD DATA LOCAL INFILE

1 Answers1