MySQL table broken when some values in right hand column are empty

Question

I'm new to SQL and using it for something at work.

I have the following table I made:

CREATE TABLE PC_Contacts
(
POC VARCHAR(255) PRIMARY KEY NOT NULL,
Phone_1 VARCHAR(255),
Phone_2 VARCHAR(255)
);

I import some data from a CSV into the table using the following command in powershell: cmd /c 'mysql -u root -p network < CSVImport.sql'. This is what is contained within the CSVImport file:

USE Network
LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\PC_Contacts.csv'
INTO Table PC_Contacts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

After populating the table, something I noticed is when the last column (Phone_2) doesn't have a phone number populated, instead of inputting a NULL value, it is blank. In addition, several characters on the POC column are cut off whenever a phone number is omitted. So I input xxx-xxx-xxxx into the columns, repopulated it, and everything looked clean. How can I make it so I don't have to do this and the table can just populate itself with NULL values?

+------------------+--------------+--------------
| POC | Phone_1 | Phone_2
+------------------+--------------+--------------
|April Wilson| 123-456-7890 | xxx-xxx-xxxx
|Anton Watson | 234-567-8901| 567-890-1234
|Ashley Walker | 345-678-9012 | 456-789-0123

Names and phone have been altered, of course . If I were to take xxx-xxx-xxxx out though, you would see in the POC column something like 'lson' instead of the full name. Any thoughts?

You could try to write a condition in your populating query that replaces blanks with nulls — Le Woogush, Feb 02 '16 at 20:24

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

This bit of code , found from this answer, should solve your problem I think.It will check each value and if its not present assign '' to it.

To me it seem what is happening is when a value is missing in PC_Contacts.csv the import gets confused and that is why you get some data trimed off the other columns.

USE Network
LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\PC_Contacts.csv'
INTO Table PC_Contacts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@vone, @vtwo, @vthree, @vfour, @vfive)
SET
POC = nullif(@vone,''),
Phone_1 = nullif(@vtwo,''),
Phone_2 = nullif(@vthree,'')
;

edited May 23 '17 at 11:52

Community

1
1

answered Feb 02 '16 at 20:35

justinf

1,246
2
19
39

See above ^ Just added in contacts of CSV import. – Winner1235813213455 Feb 02 '16 at 20:57
Try this out its from a similar problem , it should solve your problem . If it does not work you might want to take a look at the ENCLOSED BY '"' , part cause if some values in your file are enclosed in " and some are not it can cause problems . – justinf Feb 02 '16 at 21:14
Unfortunately, did not work. I found out I am looking in the wrong place though. Moving to this thread: http://stackoverflow.com/questions/35167420/mysql-issue-when-importing-specific-csv-files-with-blank-values-in-random-rows – Winner1235813213455 Feb 03 '16 at 02:33

MySQL table broken when some values in right hand column are empty

1 Answers1