Load data from .csv applying regex before insert into table

Question

I know that there is a code to import .csv data into a mysql table, and I'm using this one:

LOAD DATA INFILE "file.csv" INTO TABLE foo FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";

The data inside this .csv are lines like this example:

08/e0/Breast_Cancer_Res_2001_Nov_2_3(1)_55-60.tar.gz Breast Cancer Res. 2001 Nov 2; 3(1):55-60 PMC13900
b0/ac/Breast_Cancer_Res_2001_Nov_9_3(1)_61-65.tar.gz Breast Cancer Res. 2001 Nov 9; 3(1):61-65 PMC13901

I just want the first part (the .tar.gz path), always on the pattern

(letter or number)(letter or number) / (letter or number)(letter or number)/...

and the part starting by 'PMC', always on the pattern

PMC(number...)

where 'number' means a number between 0 to 9 and a letter means a letter between a to z (both upper and lower case)

So, applying the LOAD DATA, and the regex, and inserting the result entries on my sql table, the result table should be:

1      08/e0/Breast_Cancer_Res_2001_Nov_2_3(1)_55-60.tar.gz     PMC13900
2      b0/ac/Breast_Cancer_Res_2001_Nov_9_3(1)_61-65.tar.gz     PMC13901

What should be the SQL command to do all this?

Nicola Cossu · Accepted Answer · 2011-03-21 15:02:09Z

2

I made a test with your two lines

create table mytest(
id int not null auto_increment primary key,
descr varchar(250),
code varchar(50)
) engine = myisam;

load data infile 'E:\file.txt'
into table mytest 
fields terminated by ';'
ignore 1 lines
(@descr,@code)
set descr = substring(@descr,1,locate('tar.gz',@descr)+5),
code = substring(@code,locate('PMC',@code))

Hope that it helps

edited Mar 21, 2011 at 15:02

answered Mar 17, 2011 at 23:10

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

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

8 Comments

Gabriel L. Oliveira Over a year ago

@nick It's getting the file path ok, but not the PMCID. The result of the @code variable is: '3(1):55-60 PMC13900'

Nicola Cossu Over a year ago

Very strange. Can you upload a sample file with some rows on myfreefilehosting.com ?

Gabriel L. Oliveira Over a year ago

@nick myfreefilehosting.com/f/d10c218404_0.01MB Please ignore the first line (I'm deleting it by hand. But, if able, add to sql code?)

Nicola Cossu Over a year ago

Hi. I've added to the code the option to ignore first line. It seems to me that everything works fine. I've uploaded you the dump of my table after I've loaded it. myfreefilehosting.com/uplFinished/3b77c268d5

Gabriel L. Oliveira Over a year ago

@nick Thank you too much to answer. I'll ask my friend to retest, with your edit, and see if it help. Also, thank you to the dump database. I'll come back here with more details

|

Collectives™ on Stack Overflow

Load data from .csv applying regex before insert into table

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related