MYSQL Tutorial: Efficiently Importing Large CSV Files into MySQL Database with LOAD DATA INFILE
Вставка
- Опубліковано 25 чер 2024
- Dataset link: drive.google.com/uc?export=do...
MYSQL server Setup
Video 1: • How to install MySQL 8...
Video 2: • How to Install MySQL 8...
Explore more on MYSQL CSV import:
1) using python and pandas (ua-cam.com/video/5IU7PZZ0LM8/v-deo.htmlsi=ayhprBcnipS8-RJM)
2) using table data import wizard (ua-cam.com/video/pmLrjtnUEBc/v-deo.htmlsi=a4C2Mxb0UaAEajBk)
Find Error 1290 "secure-file-priv error" solution here (ua-cam.com/users/shortsn4xjOUnfOMY?si=2ACk_9oOw0WfStyr)
Finally! A detailed video for this process.
Thank you so much 🙏
Glad it was helpful!
Thank you so much. I have watched a lot of videos, but after watching this video, I finally got it 😊. A new subscriber 🥳🥳
Really thankful for your feedback and motivation!
Hope you enjoy the upcoming content. If you have any suggested topics to discuss, please feel free to suggest it.
I was having a hard time finding the Program Data folder on my local disk, so for others who might be having the same issue here's a solution on Windows that worked for me: Click your disk, click the 3 dots next to view, click options, click view, and then click Show hidden files, folders, and drives. After I did that, the Program Data folder showed up!
Thank you you saved me
You have no idea how much this has helped me.
I've been stranded on this for a long while. Lol.
Thanks a ton man
Thank you so much for this insight. You're amazing.
Thank you so much brother... This was so helpful...wasted hours trying to do this
Great video! Was struggling with a dataset
Thanks!!! the form of the videos and the step by step are really nice!!!
Buddy, thank u so much for this video. I got a problem trying to resolve the secure-fiele-priv issue, but I read your comments and I could resolve it. THANKS A LOT.
Grateful that my video helped you!🙏
Thank you so much i needed it.
Its impressive, good job.
very good way to explain.
This is so helpful, thank you so much. God bless you.
Thanks aĺot for such feedback
Thank you so much, you solved my problem !!!!
Thanks for making this video.
THANK YOU SO MUCH!
Steps on importing the .csv file from a Mac. I am having trouble locating the same pathways on my Mac. I understand that Macintosh HD is equivalent to C Drive on Windows PC but unable to locate next steps. This has been a great video up to this point as I was struggling to import a very large Excel file.
Hi Sarah! I've been looking for someone that had the same problem as me! Would you mind explaining how you fixed it?
Hey I do also have same problem uploading my data on mac
THANK YOU!!!
Still having issues. My dataset does has lot of null values and its not a calculated field you can easily apply formula. How do I import this file with the nulls
Excellent.
Thank you for your feedback🙏
What if mysql server is not on local computer? I keep getting the error: "Access denied for user..."
So, how to do it on a MAC? it's hidden in the system set
Thank you for this video but I am getting this error when I am saving that file in mysql data folder " you don't have permission to save in this location contact the administrator to obtain permission" How to resolve this issue
Everything went well until the last step. While trying to execute load data I file step, getting error code 1290. Can anyone please help me with it.
Hello Suvarna,
You can find the solution in our short video here:
ua-cam.com/users/shortsn4xjOUnfOMY?si=rj1MxuCMHAI9lpQ5
Good luck and if you find it useful, don't forget to spread it with your colleagues!😀
as a data analyst,it’s possible that just import all excel data to mysql and query data as we need without learning excel ?
finally i got what i was looking ffrom past 3 hours!
doubt: i have imported data first, how can i assign datatype to the columns?
You can find the detailed steps to modify your columns datatype in this link
www.w3schools.com/sql/sql_alter.asp
Question, did you have to modify the config file for the secur file to an empty string?
Yes, this is to avoid the secure file priv error while importing
nicee
Thanakxx
Thank you for the video. But, I couldn't find "DATA" inside the mysql server. All I have are bin, docs, etc, include, lib, share … please how can I fix this?
It seems that you go to wrong directory.
You must go to local disk C - ProgramData - MYSQL - MYSQL server and there you will find your folder.
Is it possible to import data from excel to MYSQL on regularly ?
What if I have multiple tables? I have a large csv and my database has 9 tables. How do I go about inserting the appropriate data into the right tables?
You have to repeat the process explained in the video for each table until you insert all tables.
If you have some tables with few rows, you can use another quick method which is the table data import wizard. You can find its explanation in another video on my channel.
During importing, make sure to start by the tables with minimum dependents.
For example, if you have 2 tables. One has only a primary key and the other has a primary and foreign key. You must begin by importing the first.
Good luck!
Hi! what happens if I have blanks in my CSV file, this is because I still don't have any data to fill in those specific cells, this is going to affect my importing?
Thank you!
Hi there, yes it may affect your importing if the blanks cells contains unseen characters or spaces as it may be different from the data type you specify for the column. Try to clean your data as much as possible, then when you import your data try to use the update statement to set all blank cells to NULL and everything will be OK.
Good luck and inform me with the updates!
I am working on the Google analystics Case Study, there are over 1million rows in some of the CSV files. When I use MYsql Workbench to upload, this takes several hours. Is that normal?
1 million rows can be very large number indeed, but I guess several hours is a very big. You may divide the file into parts and load every part separately.
Make sure to use the load data infile statement or pandas method
Thank you greatly for your explanation! I have one problem running the last bit of code - it generates this error: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. Maybe you know anything about this? I'd be very grateful for any suggestions!
Thank you very much for your feedback and support!
You need to go to MYSQL Workbench and write "SHOW VARIABLEA LIKE "secure-file-priv"
This will return one row with 2 columns, in your case, you will a path in the second column.
You have to go to ProgramData folder in local disk C then go to MYSQL then MYSQL server and there you will find a file named 'my'. Open it in Notepad and using ctrl+F for "secure-file-priv" delete the path written and leave it with empty string. Then, you need to restart your device and here you can go ahead and import your data successfully.
Good luck and please inform me with the updates!
@@AnalyticsExplorers I am getting the below error when tried to save the doc after assigning secure-file-priv to empty string:
"You don't have permission to open this file. See the owner of the file or an admin to obtain permission"
I'm facing issue while importing CSV file but there is an error that it's is not accepting character like $€¥°^®© these are the charector is not accepting and showing error
Can we also use this method for importing few columns, not the full table?
Yes!
You simply can copy the specified columns to be imported and paste them into new separate file and save it as csv file to apply the video steps on it.
Good luck!
I have gone through all the steps, it's not returning an error, just '0 rows affected'
I've done everything.
how can I edit tha datas within the table?
I don't have the folder named 'Program Data' in the local disk C, how can I find it ?
The file may be hidden. Make sure to show all files.
You lost me at the part where you 'pasted' on the file path. What did you paste because I didn't see you copy anything prior to that?
I Just pasted the file path to be imported in my database. To do this go to the folder where your file is located and from the upper bar in your computer click on the folder name and copy it
Please confirm if any columns have blank space then will mysql successfully import data or not
Becuase i have 1 million records to import and cant check each column for blank space
It depends on the type of the column being imported.
If you are importing a string (Varchar) column it won't cause any problems but it can in case of integers for example.
You can use excel function to clean the columns before import.
For example, you can use the Trim function with numbers or integers to clean up any empty space which is unneeded.
When i clicked ctrl+enter this error is coming. Error Code: 29. File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\ProgramDataMySQLMySQL Server 8.0databasescustomers_dataset.csv' not found (OS errno 2 - No such file or directory). but the path is correct.
Make sure not to use the symbol "\" between folders in the file path. Use either "/" or "\\".
can DBeaver do this ?
I have a table with 34 columns and 600000 rows. Can I import this data into mysql?
You can do something good. you can divide your table into 2 parts, one part contains the columns' titles and few rows and the other part contains the remaining rows.
You can use the Table Data import wizard method to quickly import the first small part where MYSQL will define all columns data types by itself "You can watch this method's steps on my Channel by the way".
Now you have the table created and its columns are well defined. You can use this video method (LOAD DATA INFILE STATEMENT) to import the second remaining part.
Good Luck!
Hello, once "secure-file-priv" delete the path written and leave it with empty string, I can't get connection to the server. MySQL81 is down. what should I do? Please help. Thank you.
What exactly you get or what happened in detail?
"Could not connect, server may not be running, Unable to connect to localhost." Only thing I did is empty string for "secure-file-priv".
@@AnalyticsExplorers
Hello i've same problem, can you solved it ?
HELLO i HAVE TRIED YOUR STEP BUT THEY ARE GIVING ME THIS ERROR: Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Thank you for this video, I have a large data and I devided the table in 2 parts but I still can not import data wat's wrong, it takes a long time without any result
What type of error do you face?
Make sure to make the first part of the table with small records while using the data import wizard method to not consume a lot of time
No error but the import seems very late or impossible, ok I will try that because thé first part is long
Is program file same as program data?
But it is showing error saying server is running with secure server file priv option. What shld i do
Hi there,
Go watch this short video and I hope it will help
ua-cam.com/users/shortsn4xjOUnfOMY?si=qr6LFLij2jNRsBh2
Good luck!
what if we have 300+ columns of data then it wont be easy to writ a create table statement to create an empty table. any alternative?
You can do something good. you can divide your table into 2 parts, one part contains the columns' titles and few rows and the other part contains the remaining rows.
You can use the Table Data import wizard method to quickly import the first small part where MYSQL will define all columns data types by itself "You can watch this method's steps on my Channel by the way".
Now you have the table created and its columns are well defined. You can use this video method (LOAD DATA INFILE STATEMENT) to import the second remaining part.
Good Luck!
@@AnalyticsExplorers giving error Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
@@AnalyticsExplorers i foolowed your program data notepad solution but doesnt allow me to save as it is editable by administrator only
When I load the data infile, i'm getting this error
Error: Data truncated for column 'sales' at row 1
Error Code: WARN_DATA_TRUNCATED
How to deal with this?
Also, when we save the file in csv format the datatype we changed does not save. How does mysql understand the datatype then?
Hello,
Your problem can be because you use the actual delimiter of your file inside a certain column of the table.
I mean that if you have a csv file (comma separated), the problem will occur if you have a comma inside any column other than the delimiter.
That helped, I removed the column that contained commas. Thanks a lot. But I have a follow up question now.
Say I have a column where there are multiple words separated by comma's (just like in the above case). How can I import or export such a file with such a column through MySQL
One thing that I can think of is replacing the commas in the column with hyphen (or some other character). Do we have any better solution in this case?@@AnalyticsExplorers
@@new_moon_12 Great question!
We surely have another better situation which is to convert the csv file to semi-colon delimited for example. Changing the delimiter will truly solve all your problems. Give it a try and inform me with the updates. Good luck!
Hey I am getting error while importing " the MySQL server is running with the -secure-file-priv option so it cannot execute this statement
Help me
@@Sprinkle-ru3eb
Hello,
You can find your problem solution in this short video
ua-cam.com/users/shortsn4xjOUnfOMY?si=3jVUpEcwlGMheR-w
Best of luck!
Error Code: 1292. Incorrect date value: '2020' for column 'date_added' at row 1 I still keep getting this error even thought I updated my data dates from yyyy-mm-dd
It cannot work for me. Any other suggestion for importing large data.
Can you explain the problem you had in more detail?
Must I clean my data in excel before importing to MySQL
Yes of course, cleaning your data is very important to efficient importing to MYSQL.
if your create a table with a column that contains dates, and there are some cells that have incorrect date formats. This will raise an error while importing and more errors can happen for different situations.
So clean your data as much as you can.
What if there is foreign keys in table in sql? Please answer
Please try to explain your problem in more details
@@AnalyticsExplorers there is csv file with more than million records. I need to import it to database with different tables (year,country, etc.). They are connected to data table in the same database with foreign keys and it has its own columns too. How i can import it. I tried with "load data infile" but it is too complicated situation for me.
I keep having this error, how do I fix it
Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access
Hi,
sorry for our late reply.
Your problem can be solved through the following steps:
1- open MYSQL workbench
2- Go to the connection Tab.
3- Right Click and choose Edit Connection
4- Go to the "Advanced" Sub Tab
5- you will find a box "Others", Copy this sentence "OPT_LOCAL_INFILE=1" and paste it there.
6- Restart your device.
Now, your problem should be solved. Good luck and inform us with the updates.
what if there is 50 columns typing every one would be very annoying . is there a better way of doing it ?
You can do something good. you can divide your table into 2 parts, one part contains the columns' titles and few rows and the other part contains the remaining rows.
You can use the Table Data import wizard method to quickly import the first small part where MYSQL will define all columns data types by itself "You can watch this method's steps on my Channel by the way".
Now you have the table created and its columns are well defined. You can use this video method (LOAD DATA INFILE STATEMENT) to import the second remaining part.
Good Luck!
The second part will be seen as another table?
how can I get access to the server, I tried saving a file as you explained but it keeps saying I should contact the administrator to obtain permission. thanks
Try type the statement as follows:
LOAD DATA LOCAL INFILE 'file path on your computer without moving it to the server as in the video' into table
The error should go away
Good luck
@@AnalyticsExplorers thanks for the response but where should i type it? on workbench or command prompt.
@@oluwatoyinogunyemi on MYSQL Workbench
And don't forget to enter the full path of the file and replace all '/' with '//' or '\'
@@AnalyticsExplorers 0 16 00:26:24 LOAD DATA INFILE "C:\\Users\\Oluwatoyin\\Downloads\\SuperStoreOrdersdatas.csv" INTO TABLE store_wd
FIELDS TERMINATED BY ','
IGNORE 1 LINES Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.015 sec this is the error message i got. please help out. thanks
@@oluwatoyinogunyemi
You need to go to MYSQL Workbench and write "SHOW VARIABLEA LIKE "secure-file-priv"
This will return one row with 2 columns, in your case, you will a path in the second column.
You have to go to ProgramData folder in local disk C then go to MYSQL then MYSQL server and there you will find a file named 'my'. Open it in Notepad and using ctrl+F for "secure-file-priv" delete the path written and leave it with empty string. Then, you need to restart your device and here you can go ahead and import your data successfully.
Good luck and please inform me with the updates!
I am facing error while I write the
"LOAD DATA INFILE 'abc.csv' INTO abc
FIELDS TERMINITED BY ','
IGNORE 1 LINES;"
the error is Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'olist_orders_dataset FIELDS TERMINITED BY ',' IGNORE 1 LINES' at line 1
Please assist
Hi,
Hope you are doing well.
This is just a syntax error
Write your query as follows
"LOAD DATA INFILE 'abd.csv' INTO TABLE abc
FIELDS TERMINATED BY ','
IGNORE 1 LINES;"
Hope it will help. Inform us with the updates!
@@AnalyticsExplorers thanks a lot
but now another problem has occured. I am getting - Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Please assist
@@user-ul5to3zz2c
You need to go to MYSQL Workbench and write "SHOW VARIABLEA LIKE "secure-file-priv"
This will return one row with 2 columns, in your case, you will a path in the second column.
You have to go to ProgramData folder in local disk C then go to MYSQL then MYSQL server and there you will find a file named 'my'. Open it in Notepad and using ctrl+F for "secure-file-priv" delete the path written and leave it with empty string. Then, you need to restart your device and here you can go ahead and import your data successfully.
Good luck and please inform me with the updates!
@@AnalyticsExplorers After that I got this - Error Code: 1265. Data truncated for column 'payment_value' at row 1
@@user-ul5to3zz2c This is because of the wrong data type of the payment_value Column
What is the data type of this column in the database?
Hi sir, Issue is if iam importing 1009 rows data it's taking 196 records , why it's coming like that how can I rectify this
Tell me what you exactly get from the error?
@@AnalyticsExplorers My data contains 10,000 Rows If iam imported The data it's taking only 196 Rows I don't why even I have changed 50000 limit rows .
@@benis4913 do you receive any error?
Does the query end with a warning message or any type of errors? Or the query doesn't return any messages?
@@AnalyticsExplorers Hi What iam telling I can able to import the exact file It's perfectly imported the data the thing is It's not Taking fulll data to import
@@benis4913 Hi, Have you found a solution. I am facing the same kind of situation right now
wow amazing. but i wish mysql can crate table from file csv
Actually mysql can build table from a csv file using the table data import wizard methodology.
If you're interested to know how, go watch our first video in MYSQL playlist
@@AnalyticsExplorers wow
Everything worked fine until when I go to Program Data-- My SQL-- My SQL Server-- Data. Data folder is not opening only... What could be the reason ?
I got the same problem. Instead of opening the Data folder directly, follow the same path as he showing in the video. i.e, Excel file -- save as -- local disk c -- programdata -- mysql -- mysqlserver 8.0 -- Data . Then there will be a dialog box appear for getting the access . click yes. then we can open data folder .
that make insert or restore?
Can't understand your question, please clarify!
Hi, does it work for 1millon records too?
Yes for sure! It can work for any quantity of records
Why am i getting "OS errno 2 no such file found"? I deleted the secur-priv and saved csv in the correct schema i want it to upload. ahhhhhhhhhhhh
Did you put your csv file in the correct directory?
I think so. Program data - Mysql - mysql server 8.0 - data -tablename - table@@AnalyticsExplorers
load data infile 'game set.csv' into table gameset
Fields terminated by ','
ignore 1 lines;
i got
error 1290
Hi there,
You need to go to MYSQL Workbench and write "SHOW VARIABLEA LIKE "secure-file-priv"
This will return one row with 2 columns, in your case, you will a path in the second column.
You have to go to ProgramData folder in local disk C then go to MYSQL then MYSQL server and there you will find a file named 'my'. Open it in Notepad and using ctrl+F for "secure-file-priv" delete the path written and leave it with empty string. Then, you need to restart your device and here you can go ahead and import your data successfully.
Good luck and please inform me with the updates!
Couldn't execute Show variable like "secure-file-priv" because of the red line under variable.
2 IMPORTANT ISSUES:
1) You start showing that your CSV is already loaded in Excel, but what if it is in a different format? How to save it as a CSV making sure that data is changed properly?
2) What if I can't open the CSV cause it is heavy? How to load and change the DATE FORMAT without using excel ?????
Hello,
Actually this video is not intended for how to deal with csv files problems. As the video title indicates, it's mainly for the import process to MYSQL.
Hope you got me.
@@AnalyticsExplorers And this is exactly my issue. I'm doing what you say in the video and it is not allowing me to import.
Range issue, date type issue, no info in 1st row issue, etc.
Thanks for your reply!
Hi. Thanks for the video. But MySQL gave this error - "Error Code: 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"
How can I fix this?
Hi there,
You need to go to MYSQL Workbench and write "SHOW VARIABLEA LIKE "secure-file-priv"
This will return one row with 2 columns, in your case, you will a path in the second column.
You have to go to ProgramData folder in local disk C then go to MYSQL then MYSQL server and there you will find a file named 'my'. Open it in Notepad and using ctrl+F for "secure-file-priv" delete the path written and leave it with empty string. Then, you need to restart your device and here you can go ahead and import your data successfully.
Good luck and please inform me with the updates!
@@AnalyticsExplorers
Hello. I solved it on Friday, and It was as you said. But nevertheless, I'm so much grateful. Thanks for this video.
Thanks for your feedback and sorry for our late reply
@@AnalyticsExplorers Not at all. We should be thanking you. This video is a gift. You don't know a great help this video is to those who use MySQL. Thanks so much once again.
@@AnalyticsExplorers Thank you foe this Solution I was also getting secure-file-priv error its fixed now but now I am getting new error :
Error Code: 29. File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\printify\orders1.csv' not found (OS errno 2 - No such file or directory)
Even I can see the file exist in this location . Kindly Help
Your Quick response will be appreciated. Thanks
how to load csv file having blank cells
Hi there,
While you are creating your table using the create table statement, if you have a column with empty cells make its datatype as text. After you already imported your table, use the update statement to replace any empty cell by NULlL as
(Update [tablename]
Set [Columename] = NULL
Where [Columnname] = "")
After that you can change your column datatype to the right type you want.
Best of luck!
Getting this Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.234 sec
Hi there,
You need to go to MYSQL Workbench and write "SHOW VARIABLEA LIKE "secure-file-priv"
This will return one row with 2 columns, in your case, you will a path in the second column.
You have to go to ProgramData folder in local disk C then go to MYSQL then MYSQL server and there you will find a file named 'my'. Open it in Notepad and using ctrl+F for "secure-file-priv" delete the path written and leave it with empty string. Then, you need to restart your device and here you can go ahead and import your data successfully.
Good luck and please inform me with the updates!
While writing query show variable like "secure-file-priv" I'm getting 0 rows returned
With 2 columns variable _name and value
After making changes in my .in I'm getting the same error