substring load data infile

[edwardoit]

hi all,

 

guys, question:

 

how can i use "load data infile" with mysql only not php code.

regarding this "load data infile", can i use substring to upload my files???

example: this single line from my file consists of 9 fields... 

 

0001GB000020000300050006700070008000100020040050060007008

 

so it would store something like this:

 

0001GB 000020000 3000 500067 00070008 0001 0002004 00500 60007008

 

thanks in advance...

[aschk]

You need to split that string up using SUBSTRING, SUBSTRING_INDEX, and probably LOCATE also.

[edwardoit]

Thanks for the reply men...

 

but i just newly read this somewhere else. maybe this would help others too

 

it says something like this:

 

- CREATE TABLE temp_data (dataline VARCHAR(255));

- LOAD DATA INFILE 'data.txt' INTO TABLE temp_data
FIELDS TERMINATED BY '' ENCLOSED BY '';

INSERT INTO test (server_time, usr, sys, wio, idle)
SELECT
TRIM( SUBSTRING(dataline, 1,  ),
TRIM( SUBSTRING(dataline, 9,  ),
TRIM( SUBSTRING(dataline, 17,  ),
TRIM( SUBSTRING(dataline, 25,  ),
TRIM( SUBSTRING(dataline, 33,  )
FROM temp_data;

TRUNCATE TABLE temp_data;

 

Good luck to me.