'How to solve Unhandled exception error when importing csv file in MysqlWorkbench?

When I imported an .csv file using import wizard of MySQL Workbench (utf8), the following error popped out.

Unhandled exception: 'ascii' codec can't decode byte 0xc2 in position 444: ordinal not in range(128)

I'm new to SQL so I don't know how to fix this. Please help me.

mysql


Solution 1:[1]

I do not know how your table was created, but when I create it using:

CREATE TABLE `office_supplies` (
  `Order ID` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Order Date` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Ship Mode` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Region` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Product ID` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Category` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Sub-Category` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Product Name` varchar(245) CHARACTER SET utf8 DEFAULT NULL,
  `Sales` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Quantity` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Discount` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Profit` varchar(45) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

(which is, of course, not want you want, but .... ?)

I can import your CSV:

Order ID,Order Date,Ship Mode,Region,Product ID,Category,Sub-Category,Product Name,Sales,Quantity,Discount,Profit,
CA-2015-110870,12/12/2015,First Class,West,TEC-AC-10002926,Technology,Accessories,Logitech Wireless Marathon Mouse M705,299.94,6,0,
CA-2015-110870,12/12/2015,First Class,West,OFF-SU-10001225,Office Supplies,Supplies,Staple remover,25.76,7,0,
CA-2014-143210,01/12/2014,First Class,East,TEC-PH-10004434,Technology,Phones,Cisco IP Phone 7961G VoIP phone - Dark gray,271.9,2,0,

using:

LOAD DATA 
INFILE 'D:/MySQL Server 8.0/Uploads/office_supplies.csv'
INTO TABLE office_supplies
COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
IGNORE 1 LINES

Note: I only had to add one ',' at the end of line 1.

More info on the LOAD DATA ...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions