CODEWITHSUNDEEP
mysqllinuxshellcsvredhat
kevin lowenhaupt
kevin lowenhaupt

Reputation: 57

Uploading CSV file to MySQL but one field does not show up in the table

When uploading a csv file to a mysql table, all fields will appear in the table except one. When i do a select distinct on that one column, only blanks show up and the header of the field.
However when viewing the table through an external tool (PBi) the field shows up but with what appears to be incorrect values.

I double checked my script that manipulates the csv file prior to upload and it appears to be working correctly.
I start with a csv from AWS and manipulate it to get down to the columns that I need.
I can check each column individually and see the values are correct prior to uploading the csv through the bash command below.

[KL@SERVER]$awk -F , {print $FIELDNUM} FILE

Code used to upload the file is run in MySQL from the server that has established a connection.

LOAD DATA LOCAL INFILE
'FILEPATH'
INTO TABLE TABLENAME
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field1,field2,field3,etc);

This results in blanks for the FIELDNAME field when querying for distinct FIELDNAME from the database table The query selects the correct amount of distinct values somehow, it shows 189 rows in the set. But for some reason the actual results are blank. I would expect to see 189 different results for FIELDNAME, such as when i do a select distinct on any other column in the table.

Upvotes: 0

Views: 55

Answers (1)

kevin lowenhaupt
kevin lowenhaupt

Reputation: 57

For anyone in the future uploading a csv to mysql with linux. I figured out that there is a ^M Character in the csv that is not visible with most text editors. Use a tr command to replace it before uploading to mysql and you should be good.

Upvotes: 1

Related Questions