This content originally appeared on Level Up Coding - Medium and was authored by Shubham Metkar
This article is going to be a quick guide on “How to import CSV file data into MySQL table correctly” while taking care off all the possible error you may encounter with using MySQL workbench.
It is assumed that you’ve your create table statement ready and ran it already. So there are quite frequent occurence where you might want to bulk upload entire CSV file data into the table, so one major possiblity is you’ll be using MySQL workbench’s import data wizard, but considering you’ve very very large data there is huge possibility that only few starting rows will be imported and that too the process is very slow by using import wizard hence this is less likely to choose method, instead use proper SQL query to load data into your table.
Let’s now see that query :
LOAD DATA LOCAL INFILE 'D:/Download_PP/ReceipeData.csv'
INTO TABLE recipe
-- table name which has columns same as column headers in
-- CSV(replace the word "recipe" with your table name)
FIELDS TERMINATED BY ','
-- separator for your csv
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
-- to ignore the first line in csv
-- which holds the headers(column names)
If you don’t use “LOCAL” keyword in the above load data statement you may receive the following error, so make sure to include that.
Post this if you now try to run then it is possible that you’ll enter the following error:
Reason behind this error is in MySql 8.0, the capability to use LOCAL is set as False by default. As your server and client should be configured to have LOCAL permited. So to resolve this run the following query :
show global variables like 'local_infile';
Now you’ll get result like this:
We now need to make this variable in ON state, hence run following query:
set global local_infile=true;
Now if you try to run our load data query there are also high chances to receive Error 3948 like this:
To resolve this, we’ve already set client side to ON in the previous query so to enable from server side we need to make some changes in workbench:
- Go to Database → Manage Conncetions…
- Select your connection → Go to Advanced → Write OPT_LOCAL_INFILE=1 in others textarea → Test Connection → Close
Conclusion:
By handling above errors we’re now able to populate all of our records(rows) within few seconds.
Level Up Coding
Thanks for being a part of our community! Before you go:
- 👏 Clap for the story and follow the author 👉
- 📰 View more content in the Level Up Coding publication
- 💰 Free coding interview course ⇒ View Course
- 🔔 Follow us: Twitter | LinkedIn | Newsletter
🚀👉 Join the Level Up talent collective and find an amazing job
How to import CSV file data into MySQL table correctly was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Shubham Metkar
Shubham Metkar | Sciencx (2023-04-23T14:46:48+00:00) How to import CSV file data into MySQL table correctly. Retrieved from https://www.scien.cx/2023/04/23/how-to-import-csv-file-data-into-mysql-table-correctly/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.