CODEWITHSUNDEEP
mysql
jackie21
jackie21

Reputation: 337

NULLS appearing after importing table and renaming columns in MySQL

In the code below, I am creating a table and then importing data from a local file. I would like to also rename some columns when importing. Also I do not want to import all the columns. The code below seems to satisfy those requirements, however when I run it, I only see NULLS everywhere. I tried not trying to rename the columns so basically ending the code after IGNORE 1 LINES and then data is being imported correctly Can someone please suggest a way for my original method to work - importing some columns, and renaming them ?

CREATE TABLE SALES_AUG_t (
   `Country` VARCHAR(50),
   `GL Category` VARCHAR(30),
   `Measure Names` VARCHAR(30),
   `P3 BFS Description` VARCHAR(50),
   `P4 Sub Brand Description` VARCHAR(50),
   `P6 Sub Franchise` VARCHAR(20),
   `P7 Franchise` VARCHAR(20),
   `Product Number` VARCHAR(50),
   `Region` VARCHAR(13),
   `Measure Values` DECIMAL(10,3)
);

LOAD DATA LOCAL INFILE 'L:/JNJCHLL/DEPARTEMENT/MEDOS INTL - GSC/SPINE/01. S&OP & BP/01. Demand Planning/04. KPI - MAPE & Bias/2018/08. August/Extract - Source/Extract Tableau/Lag1_SKU_Level_data_Tableau_August.csv' REPLACE INTO TABLE SALES_AUG_t
CHARACTER SET Latin1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES

 (
   @`Country` ,
   @`GL Category`,
   @`Measure Names`,
   @`P3 BFS Description`,
   @`P4 Sub Brand Description`,
   @`P6 Sub Franchise`,
   @`P7 Franchise`,
   @`Product Number`,
   @`Region`,
   @`Measure Values`
  )

  set
  `Country` = @'Country Description',
  `Measure Names` = @'Profile',
  `P3 BFS Description` = @'Planning Hierarchy 3 Description',
  `P4 Sub Brand Description` = @'Planning Hierarchy 4 Description',
  `P6 Sub Franchise` = @'Planning Hierarchy 6 Description',
  `Product Number` = @'Planning Hierarchy 2',
  `Region` = @'Region Description',
  `Measure Values` = @'Quantity'
  ;

Upvotes: 0

Views: 31

Answers (1)

EternalHour
EternalHour

Reputation: 8631

Your problem exists because you are creating variables, then renaming only the variable names rather than the columns. Why not create the table with the column names you want then import the data?

CREATE TABLE SALES_AUG_t (
   `Country Description` VARCHAR(50),
   `GL Category` VARCHAR(30),
   `Profile` VARCHAR(30),
   `Planning Hierarchy 3 Description` VARCHAR(50),
   `Planning Hierarchy 4 Description` VARCHAR(50),
   `Planning Hierarchy 6 Description` VARCHAR(20),
   `P7 Franchise` VARCHAR(20),
   `Product Number` VARCHAR(50),
   `Region Description` VARCHAR(13),
   `Measure Values` DECIMAL(10,3)
);

LOAD DATA LOCAL INFILE 'L:/JNJCHLL/DEPARTEMENT/MEDOS INTL - GSC/SPINE/01. S&OP & BP/01. Demand Planning/04. KPI - MAPE & Bias/2018/08. August/Extract - Source/Extract Tableau/Lag1_SKU_Level_data_Tableau_August.csv' INTO TABLE SALES_AUG_t
CHARACTER SET Latin1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES

(
   @Country,
   @GL,
   @Profile,
   @P3,
   @P4,
   @P6,
   @P7,
   @Product,
   @Region,
   @Measure
)

SET
  `Country Description` = @Country,
  `Profile` = @Profile,
  `Planning Hierarchy 3 Description` = @P3,
  `Planning Hierarchy 4 Description` = @P4,
  `Planning Hierarchy 6 Description` = @P6,
  `Product Number` = @Product,
  `Region Description` = @Region,
  `Measure Values` = @Measure
  ;

EDIT: I also meant to point out, you don't need the REPLACE since you are creating a new table.

Upvotes: 1

Related Questions