Load Data Local Infile SQL

Question

I have an extract of data that is separated by using this character |

I need to load this data into a MySQL database table but I'm struggling with the lines terminated by function.

Can anyone please help me identify the correct method to terminate these fields? My example data is below.

I've already tried '"|"' but this doesn't work for the first and last fields.

Example:

00000000|OLD TRUCK|9|13|02|Z |Z  |9999|111|99|ZZ|ZZ|ZZ|ZZ||ZZ|ZZ|99|999|2

Solution 1

Try something like this:

 LOAD DATA LOCAL INFILE 'example.txt' INTO TABLE example_table
 FIELDS TERMINATED BY '|'
 LINES TERMINATED BY ',';

Solution 2

This worked for me

LOAD DATA LOCAL INFILE 'd:/test.csv' INTO TABLE test

FIELDS TERMINATED BY '|'

LINES TERMINATED BY '\n';