0

I have a hard time loading the data below with datetime into mysql workbench. The system always flag out "Error Code: 1193. Unknown system variable 'lastmodified' 0.000 sec". It would great if anyone can help me out. Thanks!

Here is my data:

id  categoryname        categoryorder   categorytype    lastmodified    lastmodby
1   Company Information 0   Articles    8/6/2002 0:00   
2   Checkout Process    0   Articles    8/6/2002 0:00   
3   Home / Welcome Pages    0   Articles    8/6/2002 0:00   
4   Affiliate System    0   Articles    8/6/2002 0:00   
5   Order Finished      0   Articles    8/6/2002 0:00   
6   Other Articles      0   Articles    8/6/2002 0:00   
7   Sample Articles     0   Articles    8/6/2002 0:00   
8   Reserved For Future Use 0   Articles    8/6/2002 0:00   
9   Order Status        10  Help    3/25/2016 16:36 7
10  Shipping and Delivery   20  Help    3/25/2016 10:58 7
11  Returns/Return Policy   30  Help    3/25/2016 10:11 7
12  Defective/Flawed Products   40  Help    3/25/2016 10:12 7
13  Affiliate/Distributor   50  Help    3/25/2016 10:59 7
14  Pricing and Billing 60  Help    3/25/2016 11:00 7
17  Guarantees      90  Help    3/25/2016 10:14 7
19  Donations       0   Articles        
20  Additional Support  232 Help    3/25/2016 10:14 7

Here is my codes:

CREATE TABLE articlecategories (
id INT NOT NULL AUTO_INCREMENT,
categoryname TEXT,
categoryorder INT NOT NULL,
categorytype VARCHAR(126),
lastmodified DATETIME,
lastmodby TEXT,
PRIMARY KEY (id)
) ENGINE InnoDB;


LOAD DATA INFILE "path/articlecategories.csv"
INTO TABLE ArticleCategories
FIELDS TERMINATED by ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, categoryname, categoryorder, categorytype, @lastmodified, lastmodby);
SET lastmodified = STR_TO_DATE(@lastmodified, '%m/%d/%Y %H:%i');
Barmar
  • 741,623
  • 53
  • 500
  • 612
sunlong ngouv
  • 1
  • 1
  • You have an extra `;` at the end of the line before `SET` – Barmar Dec 24 '21 at 04:57
  • That doesn't look like the actual data. The fields aren't terminated by comma and enclosed in quotes. – Barmar Dec 24 '21 at 04:58
  • From your first comment, I have deleted the extra ";" but the issue is still there. – sunlong ngouv Dec 24 '21 at 18:07
  • It is the actual one but I store it in .csv format when executed the codes. I wonder if there is any code I can apply in order to check the datetime format in "lastmodified" column? Thanks – sunlong ngouv Dec 24 '21 at 18:19
  • SQL is not a great language for complex string processing. But it looks like all your dates are in the same format, so that shouldn't be a problem. – Barmar Dec 24 '21 at 18:29
  • Just fix the `;` typo and your code should work. – Barmar Dec 24 '21 at 18:29

0 Answers0