CODEWITHSUNDEEP
phpmysqlcsvimport
Edmond Dantes
Edmond Dantes

Reputation: 1

Import CSV file into Mysql with Multiple Delimiters/Field Separators

I'm trying to import a large csv file into Mysql. Unfortunately, the data within the file is separated both by spaces and tabs.

As a result, whenever I load the data into my table, I end up with countless empty cells (because Mysql only recognizes one field separator). Modifying the data before importing it is not an option.

Here is an example of the data:

# 1574    1 1 1
$ 1587    6 6 2
$115 1878    8 9 23

(Where the second and third value of every row are separated by a tab)

Any ideas?

Upvotes: 0

Views: 2115

Answers (2)

user1864610
user1864610

Reputation:

Assuming you're using LOAD DATA INFILE try this:

load data local infile 'c:/somefile.txt' into table tabspace 
columns terminated by ' '
(col1, @col23, col4, col5)
set col2 = left(@col23, instr(@col23,char(9))-1),
    col3 = substr(@col23,instr(@col23,char(9))+1);

Note that the separator is a space so the second column contains the col2/col3 data. This is assigned to a variable @col23 which is then split up and the parts assigned to col2 and col3.

Upvotes: 1

prototype
prototype

Reputation: 7970

If my goal were just to import the file, i'd use sed -i 's/,/ /g' *.txt to create just one delimiter to worry about.

I like CSVs, but perhaps there's a string encased in double quotes that contains a comma or space, in which case this isn't perfect. It'd still import, just would modify those strings.

In that case, another approach I've used in production is Stat/Transfer. There's a syntax language to create a shell script to convert the file and specify multiple delimiters.

MySQL import CSV file using regex delimiter

Upvotes: 1

Related Questions