MySQL Workbench不导入CSV的所有行 |
您所在的位置：网站首页 › mysql1205错误解决方案 › MySQL Workbench不导入CSV的所有行 |
MySQL Workbench不导入CSV的所有行
|
百度翻译此文
有道翻译此文
问题描述
I am trying to import a CSV file to a new table in MySql. The file has 1 million rows but MySql is only importing 847 rows. I tried saving the CSV file and importing various formats, utf-8, windows-1205, etc. The CSV file has an INDEX column with sequential numbers that can be used as a primary key. There are no invalid characters, such as commas. I copied the CSV file and deleted the first 847 and imported it again, and it imported the next 26 rows. This shows that there is nothing wrong with the data and it could have imported it originally.Why won't the MySql Workbench import all million rows?
UPDATE: I tried importing with MSSQL (Using SSMS) and that not only gave me an error but told me what the problem was! I wasn't allocating enough space for the char fields as some values had long strings of text. All I did in SSMS was change it to VARCHAR(max) and SSMS imported all million rows. This might have been a solution for MySql but since MySQL Workbench didn't tell me what the exact problem was, I already uninstalled it and will continue with SSMS and MSSQL. 其他推荐答案Instead of using PhpMyAdmin or MySQL Workbench, I recommend you to use command-line to import csv to MySQL, especially if it's a large file. It allows you to read data from text file or import data into database very fast, read this. Use this command: LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; IGNORE 1 ROWS; Add LOCAL if the file is located on local machine. Reference: link. Change table_name to destination table name. FIELDS TERMINATED BY By default, CSV uses comma to identify individual data value. ENCLOSED BY means double quote mark " " surround values. LINES TERMINATED BY specifies line-break. IGNORE 1 ROWS This command tells MySQL to skip first row. We want to use this if CSV contain column-header and we want to ignore it being imported to our table.For further details, you can check the manual. 其他推荐答案Do not use the "Import Wizard" that does not work well. Better backup the database in SQL format and import it complete. Use MainMenu->Server->DataImport https://www.youtube.com/watch?v=91hRSphTzdw |
【本文地址】
今日新闻 |
推荐新闻 |