Data not import correctly

[roxy031577]

Hi Alex,

I'm new to the class and am currently working on the data cleaning section with the world_layoffs data. However, I have an issue where I import the data but only get 564 returns. I tried searching online but came to a dead end. I ended up uploading JSON file instead of CSV to get all those data, it worked, but I hit a wall where for the JSON file it cant recognize NULL in the data.
Would really need some help here to see how can I fully import those data to MySQL. My contact email is id031577@hotmail.cojm

Any suggestion will be appreciated
thank you
Roxanne

[badreddine013]

same problem I'm having

[BNinja95]

Same problem here...

[Shanmukha2526]

Hi Alex

I am having the same issue as above mentioned, when trying to import the data through 'Table data import wizard' only 564 returns were shown out of 2362. When checked the imported data seemed to not have any NULLs or blanks or duplicates. Tried different methods found online - disabled strict mode, restarted SQL server and workbench multiple times, tried to convert the format of the file and upload, even tried to remove NULLs and blanks through data cleaning in excel and then tried import the converted file to the workbench. Nothing seems to work. Would really appreciate any help or suggestions to upload the entire file.

UPDATE: Just found that converting the CSV to JSON file uploads all the date however converts NULLs into 'None'. So while looking for NULLs we should be using the word None. But is there any other way without converting into JSON and keeping the CSV?

Thank you
Shan

[roxy031577]

Hi there, people above,

This is Roxanne, I don't think Alex will ever get back, but I've tried to sort out myself how to address that issue.
First off, I'm using MAC so not so sure it this will work with others.

1. Don't use import Wizard, if data contain decimal, sometimes it doesn't recognize it. Instead, return to the command area and create a table. Something like this:
   CREATE TABLE layoffs (
   company VARCHAR(255),
   location VARCHAR(255),
   industry VARCHAR(255),
   total_laid_off int,
   percentage_laid_off decimal(4,2),
   date text,
   stage VARCHAR(255),
   country VARCHAR(255),
   funds_raised_millions int
   );

2. check if null is enabled and local file is ON, command you can check is below:
   DESCRIBE layoffs;
   SHOW GLOBAL VARIABLES LIKE 'local_infile';
   SET GLOBAL local_infile = 1;

3. LOAD DATA LOCAL INFILE '/Users/rox/Desktop/Data Analyst/CSV/layoffs.csv' *(this is where your file is)
   INTO TABLE layoffs FIELDS
   TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   IGNORE 1 ROWS;