CODEWITHSUNDEEP
mysql
mehedimec
mehedimec

Reputation: 23

Multiple possibilities for FIELDS TERMINATED BY - MySQL

I am trying to import a .csv file to mysql using LOAD DATA INFILE.

In the.csv file, fields are separated by space but in a certain field there are multiple information separated by , (comma).

I would like to also parse that field.

How to add multiple possibilities space or comma for FIELDS TERMINATED BY in mysql?

A sample single row of the .csv file I am working with is provided:

1565067700.891 0 yyy.yyy.yyy.yyy TCP_DENIED/403 0 CONNECT tunnel://xxx.xxx.xxx.xxx/ - NONE/- - BLOCK_ADMIN_CONNECT_12-DefaultGroup-DefaultGroup-NONE-NONE-NONE-NONE-NONE <-,-,-,"-",-,4,-,-,"-",-,6.5,-,"-",-,1,"-","-",-,1,-,-,"-","-","low","-","-","-",0.00,0,-,"-","-",-,"-",-,-,"-","-",-,-,"-",-> - "06/Aug/2019:11:01:40

I would like to parse the content between < > in the above line along with rest of the fields those are separated by space

So far I have tried:

LOAD DATA LOCAL INFILE '/home/user/myfile.csv' INTO TABLE `mytable` 
FIELDS TERMINATED BY ' ' 
LINES TERMINATED BY '\n' 
(@col1, @col2, @col3) 
set id=@col1, name=@col2, company=@col3;

Upvotes: 1

Views: 540

Answers (1)

Rob Streeting
Rob Streeting

Reputation: 1735

You could consider pre-processing the file to replace the commas with spaces so that you can use LOAD DATA without any further tweaking. Since it doesn't appear that commas are expected in any of the data, this could be as simple as tr -s ',' ' ' < file.csv > processed.csv on linux. Similarly, you could replace all spaces with commas i.e. tr -s '[:blank:]' ',' < file.csv > processed.csv and then set FIELDS TERMINATED BY ','.

Upvotes: 1

Related Questions