Explore more on MYSQL CSV import: 1) using python and pandas (ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5IU7PZZ0LM8.htmlsi=ayhprBcnipS8-RJM) 2) using table data import wizard (ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pmLrjtnUEBc.htmlsi=a4C2Mxb0UaAEajBk) Find Error 1290 "secure-file-priv error" solution here (ru-vid.comn4xjOUnfOMY?si=2ACk_9oOw0WfStyr)
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!
thanks of lot ...i have no words for you but such as you are a great man...this error have taken my a lot of time but after seen your video my all problem was solved as clear cut to cut ...once again thanks of lot...🙌🙌❤❤
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.
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.
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.
Hello Suvarna, You can find the solution in our short video here: ru-vid.comn4xjOUnfOMY?si=rj1MxuCMHAI9lpQ5 Good luck and if you find it useful, don't forget to spread it with your colleagues!😀
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?
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
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!
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
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.
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
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
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!
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
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!
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.
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
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!
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.
"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
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!
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!
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.
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 .
@@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 .
@@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
@@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.
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
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 I forgot what I did; it's been a while, but it's fixed. Thanks for creating this video as my company has transitioned from ms access to an actualy rdbms
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
@@AbhishekNavle-e6r 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!
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"
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 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!
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
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!
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 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!
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 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
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!
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!
