Has anyone ever encountered additional unknown characters appending to your column entries after importing from a .csv file?

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?

I retried the query with a wildcard search and returned the right result here (You can also see the table is not enclosed)

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?

Heres the import from the csv file code for further clarity – but it worked fine with my other tables

Answer

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;

Leave a Reply

Your email address will not be published. Required fields are marked *