I’ve filled my table of instruments using LOAD INTO FILE. It fills the rows successfully but then doesn’t enclose the final column (status) with a vertical line. I didn’t think this was an issue until I ran a query to check the number of column entries = “commissioning”.
SELECT COUNT(*) FROM instrument WHERE status = ‘commissioning’;
All 60 rows contain “commissioning” so it should return 60, but instead it returns 0?
Perhaps something is going on when I imported from csv file, because a LENGTH(status) query returns 14 when “commissioning” is only 13 characters. Has anyone encountered this before or know what character could be causing this?
The problem you are having is produced because Windows uses
'rn' instead of
'n'. As you are telling the import statement to finish lines with
'n' you have an extra
'r' character in every line. You need to change your import statement as:
LOAD DATA INFILE 'instruments.csv' INTO TABLE instruments FILEDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'rn' IGNORE 1 ROWS;