-1

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.

thanh pham
  • 61
  • 9
  • 1
    You are currently import a file, and the file seems to have character in it which are not 'ascii'. I Think they might be 'utf8', see: [ASCII vs Unicode + UTF-8](https://stackoverflow.com/questions/21297704/ascii-vs-unicode-utf-8) – Luuk Feb 10 '22 at 15:40

2 Answers2

0

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 ...

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Warning: Something weird has happened to the column `Profit` in your CSV file. You should check if the csv file is created correctly. – Luuk Feb 10 '22 at 16:44
  • Wow, this is way more complicated than I thought. I don't expect importing data to be this hard. Despite your comprehensive answer, I'm sorry I don't know how to implement it. Could there be a simpler way? – thanh pham Feb 11 '22 at 07:55
  • I did use import wizard of MySQL Workbench, then received the error in question =(((. – thanh pham Feb 11 '22 at 11:53
  • Exactly! and because that import wizard su..s, you should try another one which might please your more! – Luuk Feb 11 '22 at 11:55
  • I'll try it, thank you for helping me. There is a lot for me to learn. – thanh pham Feb 11 '22 at 12:06
0

I found out why there is an error. The column Product_name of my raw data contains values with special characters. Simply remove the special characters solves the problem. You can watch how to remove special characters here

thanh pham
  • 61
  • 9