mysql table formatting and infile issue

Question

Each time I go and create my table and then add the .csv file, it keeps not fully loading in all of the data. Below is my code:

CREATE TABLE Parts_Maintenance (
Vehicle_ID BIGINT(20),
State VARCHAR(255),
Repair VARCHAR(255),
Reason VARCHAR(255),
Year YEAR,
Make VARCHAR(255),
Body_Type VARCHAR(255),
PRIMARY KEY (Vehicle_ID)
);

LOAD DATA INFILE '/home/codio/workspace/FleetMaintenanceRecords.csv'
INTO TABLE Parts_Maintenance
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
IGNORE 1 ROWS;

SELECT * FROM Parts_Maintenance;

Here is a photo of what it looks like in Codio:

And here is a photo of some of the data being brought in:

Could someone please help me pinpoint what I am doing wrong?

Tried to create table and bring in a .csv file. Table was created but the data is not all there and the table looks messed up

Answer 1

I agree with @barmer Your LINES TERMINATED BY is probably wrong, it's probably \r

You May use

LOAD DATA INFILE '/home/codio/workspace/FleetMaintenanceRecords.csv'
INTO TABLE Parts_Maintenance
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

Alternatively, you can use another method with workbench.

• Right Click on Table
  - Table data import wizard
  - Choose the file path
• Select the destination table
  - Check and configure import settings
  - Click on Next to execute.

This is the best and simple solution.