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:
phone
11111111
22222222
33333333
If I cannot use LOAD DATA LOCAL INFILE, is there another method to do this in mysql?
Thanks!
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 |
See also this db<>fiddle
Solution was adapted from different answers to this SO question and this answer.