WOW! This video is absolutely awesome! Never had such clear instructions on using the MySQL workbench and you helped me avoid a headache for my homework assignment. Your instructions worked like a charm!!
Thanks! i had to import a csv that my teacher made and i was struggling with the 'date' situation.. his file had the year coming last and i was using date :P when you explained that years in date should come first you gave me the answer i couldn't find after hours of search
Thank you very much for this video. I'll have a look at your others, but I'd be interested to know how to import data that needs to be separated into different tables.
There are a couple of options. You could import it all at once and then separate into tables or just import one on by one and add relationships when you have imported everything.
Thank you Matt, the vedio did help me to learn mysql as a beginner. Could you please advise if you have more vedio of mysql on UA-cam? I would like to study mysql with your further vedioes. Looking forward to more vedioes
Matt, tks for your reply and tried to download the file. However, the data file did not open and link with the database, and maybe I was doing something wrong. If you put a video about downloading and linking the data that would be great. tks.
OK. You have a couple of options here. You can download the files as a .zip and then extract them or you can clone the github repository. Let me see if I can get a tutorial together.
Hi Matt, First of all thanks for brilliant videos. I am loving it. Now to the question, i am trying to import the file to change the date format but while loading the through import wizard, its loading zero data. I am following all your steps mentioned from 11:50, please advise what am i missing.
So you mean the date is in some format other than ISO? I would try to import the date column as text and then convert it in MySQL to see if that works better.
I'm getting an error that says "unhandled exception 'charmap' codec can't decode byte 0x9d in position 2155: character maps to " do u know how I could fix this?
This sounds like there are some characters in your data that MySql can't deal with. Try opening the data file in a simple text editor to see if you can find and remove any odd looking characters. They should be pretty obvious.
@@MattMacarty “This file type is not supported. Valid options are: CSV, JSON” That’s the notification I keep getting - even after I converted the file to CSv format. Trust me, I tried all of the csv format and I keep getting the same disappointment. I don’t know what to do now. May there is a setting in excel..🤦🏾♂️
@@philosophyandreligion Are you on a MAC? Try installing a simple text editor like sublime text or atom. Open the file with the text editor and then copy and paste the data over itself and save. Try the upload then.
Hi, at about 11.30 in the video, if I try to update using the str-to-text function, I get an error message 1411 incorrect datetime value. The csv file I'm using looks just the same as the one in this demo video (the date format). Any idea what's going wrong? (me = complete newbie!) Thanks
Really hard to diagnose remotely. Can you try to alter the table to see if that works? ALTER [table name] MODIFY [data column] date; if this works you can skip the str_to_date( ) since the data is now defined as a date.
@@MattMacarty Thanks so much for replying. In fact what I did was to change the column header in the csv file, previously it was called 'date' so I changed it to something else and then the str-to-date worked. In the table it still has the Datatype as text though - is that right?
What is double and why we have insert the text 'double'? is it because we have to repeat above and why we are not giving char for other columns and only gave for 1st column
Double is the data type specification. This is a general numeric type with decimals. There are other numeric types , but double is a pretty good choice for most numeric data that many have decimals.
Hello! I just want to ask if it is possible to hide the unwanted database or can I just create new file/project to makd it look more organized? Thank you in advance!! 😊
The Workbench is really sort of a development environment for interacting with your databases. I don't think you can hid what you dont want to see unless you just close the schema pane. There are other tools you can try like Tables Plus that may do more of what you are looking for
Thanks. Yes you can import much larger files it just may be a little slow. The limit on output can also be changed to allow more or less results to display in your result set.
This tends to be problematic due to security settings in MySQL. You are looking for LOAD DATA INFILE [filename]. Here's a link to the documentation: dev.mysql.com/doc/refman/8.0/en/load-data.html
i am facing this problem while importing data unhandled exception :list index out of range and data is not importing please help me with this if u can???
It sounds like the data you are trying to import may have to be cleaned. Probably some kind of invalid data. Try opening the file with a text editor. Usually Workbench will tell you what line in the data the problem happens at.
Hi Bhuvan, after view great video, to review LoaderDataPlus tool. This tool import flat files and export to MYSQL, MARIADB sintax and other DMBS, also parsing and detect data type.
When trying to update the date I got the following: 12:37:14 UPDATE amazon_csv SET t_date = str_to_date(t_date, "%m/%d/%Y") Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. I'm having more problems with the date than you can possibly imagine.
Ok, yes the date can be a pain. If this don't absolutely need it to be a date type you can probably just leave as a text. My guess is the dates are in a different format then you have specified. With your str to date function.
What should I do with the large data having more than 20K rows with messy/NULL values..? I may not know if the data type has really maintained to the entire row or not (and not possible to check entire dataset manually).. It's getting very tough to import the data successfully to MySQL table. The Import Data Wizard is really slow in this task. Is there any other way to this more efficiently whithout loosing anything ? please let me know.. I'm unable to complete my portfolio projects 😥
A nice and clear intro to SQL workbench - MANY THANKS A qq, do I need to 'drop' the table every time when I wish to run the code before moving on to the next part? I never used the 'drop' trick, so workbench just returned a red cross with table already exists? TA !
@@MattMacarty thanks for replying. I tried to re-run the whole program after inserting data into the table i created in part 1, but SQL workbench can't run that saying the table is already exist ? Cheers
@@khong6460 OK, just skip the line where you CREATE TABLE. You can run one line at a time in the Workbench editor, or just comment the CREATE line out.
Let me ask, how do we know the latest data that appears from the data that we have imported without importing it again, I mean if we change the data then the new table will also be replaced automatically, how can we do that sir? thank you Sir
It's probably not directly compatible. Even though most of the query language is the same, these two products diverge in database implementation. I would do a search to see if someone has created a MySQL version. I am not familiar with that databse, but it sounds suspiciously similar to Sakila in MySQL
Thank you Matt. Great tutorial and help. Please may I know where is this database stored which we are creating in the localhost. Can we change that location to external drive? 2. How to load .bak datasets, I have downloaded?
In Windows you can find your database files here: C:\ProgramData\MySQL\MySQL Server 8.0\Data The programdata folder is hidden by default so go to file explorer and open This PC, then click the view tab at the top and check the Hidden items check box on the right of the ribbon
Hi Matt, thanks for the video! I have a question: why did my import (9 MB .csv) take so long to finish? 30 minutes passed, and it is still going. Would you happen to have any comments on this? Thanks for the time.
Workbench can be pretty slow importing data. There could be a problem with the data too. I would install TablePlus and try the import there. TablePlus is a client that can handle large imports much more quickly, and if there is a problem with data you will see that very quickly.
I can't able to get the data in the table after following all the procedures and executing the query , would you please tell me the solution for that and why that happens?
This usually means there is a problem with either the data itself, or the way you have defined your table in Workbench. You might visually inspect the data in a text editor to see if there are any special characters. You could try importing into a new table and let Workbench determine the data types as an alternative.
This usually means there's a error in the data type you are trying to import. Try importing the data into a new table and then modify the definitions as needed.
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
WOW! This video is absolutely awesome! Never had such clear instructions on using the MySQL workbench and you helped me avoid a headache for my homework assignment. Your instructions worked like a charm!!
Glad it helped
Thanks Matt, great tutorial easy to follow. All what I needed to learn for my course! Love UA-cam, just keeps giving!
Glad it helped
I feel like I've learned more in this video than all semester. You rock!
Glad it helped
Dude, your tutorial is just so simple to understand. Thank you!!!
Glad it helped!
This is SUPER HELPFUL thank you!! I glanced through so many articles which all made no sense to me...but this video is so on point
Thank you. Glad it helped
This is one of the best tutorials on this subject matter. Thanks so much
Thank you. Glad it helped
This was extremely helpful. Thank you! All the other videos say its for beginners and then fail to explain the most rudimentary steps... Thank you!
Thank you. Glad it helped.
Thaaanks! One of the best tutorials I've seen
Thanks very much. Glad it helped.
Thanks, Matt, really clear and concise..
Glad it helped
Thanks! i had to import a csv that my teacher made and i was struggling with the 'date' situation.. his file had the year coming last and i was using date :P when you explained that years in date should come first you gave me the answer i couldn't find after hours of search
Glad it helped!
This was so useful to my learning. So happy I found this. Great job!
Glad it was helpful!
One of the best tutorial on mysql. Thank you very much!!
Thank you. Glad it helped.
Hi Matt, thanks so much for the clear and beginner-friendly tutorial, this definitely helped me a lot.
Glad it helped
Excellent tutorial to learn database....thankyou very much sir..
Thanks. Glad it helped
The only video that helped me. Because of that, i subscribed🎉
Thanks. Glad it helped
Thanks Matt, this was super explanatory. I can create a database table thanks to you.
Glad it helped!
I am so glad i found this video... amazing. Thank you buddy
Glad it helped
This is extremely helpful, just doing an intro database design class and this video helped me a ton!
Thanks. Glad it helped.
This is exactly what I needed thanks a million
Glad it helped
Amazing explanation and hat's off to your efforts. Thanks
Thank you. Glad it helped
This is amazing. I'm following here with my laptop haha
Nice. Glad it helped
Very easy to follow and understand... 2 thumbs up!!
Glad it helped
Thanks a lot, I have been in trouble with it for so long
Glad it helped
Felt like i was listening to vin Diesel. Thank you dude!!
LOL. Someone else said the same thing. Glad it helped.
Oh God, you have a database called "Frodo"! Cool :-D
Yes it's not my server, but yes a friend for sure.
Samwise was not wise 😂
Best tutorial I've seen .. Thank you !! ✨✨👍😊
Thanks very much. Glad it helped.
This tutorial is amazing! Thank you a bunch! I loved the explanation of issues with data types!
Thank you. Glad it helped.
thank you so much you are just amazing, perfect explanation
Glad it helped
Glad it helped
Thank you for making this easy!
Glad it helped
Incredibly helpful video, thank you.
Glad it helped
thanks alot! i forgot about that lightning thing 😂
Glad it helped
Well done. Very useful and very clearly explained. Newly subbed!
Thank you. Glad it helped
Thanks man! Really good tutorial
Glad it helped
thanks for teaching us what you know I can finally make my website
Glad it helped
You are a life saver Matt. Thanks so much
Glad it helped
very helpful in importing an excel file. thankyou
Glad it helped
thanks so much.... your voice is awesome too... should be voice over artist
Thanks for the compliment. I am glad it helped.
Excellent tutorial
Thanks. Glad it helped.
Thank you for this tutorial!!!
Glad it helped
@@MattMacarty can you do a video on Stored Procedures? I am struggling with it:/
Thank you very much for this video. I'll have a look at your others, but I'd be interested to know how to import data that needs to be separated into different tables.
There are a couple of options. You could import it all at once and then separate into tables or just import one on by one and add relationships when you have imported everything.
Thank you Matt for this comprehensive video. I still have issues on trying to set up my workbench and server😢 How can I go about this?
Which version of MySQL are you trying to install? Windows or MAC?
Nice explaination, and very understandable... Thank you bro..
Glad it helped
you sound like the guy from Khan Academy. Great job on this! Very helpful tutorial!
Thanks. Glad it helped.
That's true , he sounds like Sal khan
Nice this is so clear
Thank you. Glad it helped
Thank you Matt.
Glad it helped
Very helpful, thank you
Glad it helped
Life Saver tutorial!!!
Glad it helped
Greate Job Sir
Glad it helped
Thank you Matt, the vedio did help me to learn mysql as a beginner. Could you please advise if you have more vedio of mysql on UA-cam? I would like to study mysql with your further vedioes. Looking forward to more vedioes
Hi I have a few videos for beginners: ua-cam.com/play/PLiPFKCVZWHKq3RvwDxosNm5Ny7cHyfc4o.html
@@MattMacarty Thank you very much!
thank you so much sir
Glad it helped
Thank you, it helps a lot
Glad it helped
great great great video.........
Thank you. Glad it helped.
Thank you sir
Glad it helped
Great tutorial. Thanks a lot Matt!!
Than kyou. Glad it helped.
So helpful.
Glad it was helpful
great tutorial, thanks!
Glad it helped
Tks Matt. Appreciate it.
Glad it helped
tq sooooo much
Glad it helped
Thank you for this
Glad it helped
Matt, tks for your reply and tried to download the file. However, the data file did not open and link with the database, and maybe I was doing something wrong. If you put a video about downloading and linking the data that would be great. tks.
OK. You have a couple of options here. You can download the files as a .zip and then extract them or you can clone the github repository. Let me see if I can get a tutorial together.
Really so helpful ❤️
Thanks. Glad it helped
Thank you so much for this!
Glad it helped.
Very helpful
Glad it helped
Hi Matt, First of all thanks for brilliant videos. I am loving it. Now to the question, i am trying to import the file to change the date format but while loading the through import wizard, its loading zero data. I am following all your steps mentioned from 11:50, please advise what am i missing.
So you mean the date is in some format other than ISO? I would try to import the date column as text and then convert it in MySQL to see if that works better.
Amazing it helped alott💓
Glad it helped
Thank you!
Glad it helped
OMG .. Perfect ...well explained. You really helped me. Thank you.
Glad it helped!
Wonderful
Glad it helped
I'm getting an error that says "unhandled exception 'charmap' codec can't decode byte 0x9d in position 2155: character maps to " do u know how I could fix this?
This sounds like there are some characters in your data that MySql can't deal with. Try opening the data file in a simple text editor to see if you can find and remove any odd looking characters. They should be pretty obvious.
@@MattMacarty ok thanks
Thanks....
Glad it helped
Thank you so much. You save me
Glad it helped
Great tutorial!
1 question. I’m new to the computer verse.
Was that data sheet created in Excel?
Thanks. The data used started as an Excel file but was converted into .csv from within Excel.
@@MattMacarty “This file type is not supported. Valid options are: CSV, JSON”
That’s the notification I keep getting - even after I converted the file to CSv format. Trust me, I tried all of the csv format and I keep getting the same disappointment.
I don’t know what to do now. May there is a setting in excel..🤦🏾♂️
@@philosophyandreligion Are you on a MAC? Try installing a simple text editor like sublime text or atom. Open the file with the text editor and then copy and paste the data over itself and save. Try the upload then.
Thank you.
Glad it helped
Do u have video on how install n setup MySQL pls share me and I need it on 6.3ce workbench
I have a document if you email me I will send it to you. Tell me if you are using MAC or Windows
Great tutorial. Thanks!
Glad it helped.
Hi, at about 11.30 in the video, if I try to update using the str-to-text function, I get an error message 1411 incorrect datetime value. The csv file I'm using looks just the same as the one in this demo video (the date format). Any idea what's going wrong? (me = complete newbie!) Thanks
Really hard to diagnose remotely. Can you try to alter the table to see if that works?
ALTER [table name] MODIFY [data column] date; if this works you can skip the str_to_date( ) since the data is now defined as a date.
@@MattMacarty Thanks so much for replying. In fact what I did was to change the column header in the csv file, previously it was called 'date' so I changed it to something else and then the str-to-date worked. In the table it still has the Datatype as text though - is that right?
@@nutsaboutnuts927 Yes. To change the data type you run the ALTER table command from my previous response.
What is double and why we have insert the text 'double'? is it because we have to repeat above and why we are not giving char for other columns and only gave for 1st column
Double is the data type specification. This is a general numeric type with decimals. There are other numeric types , but double is a pretty good choice for most numeric data that many have decimals.
thanks matt..
Glad it helped
Hello! I just want to ask if it is possible to hide the unwanted database or can I just create new file/project to makd it look more organized? Thank you in advance!! 😊
The Workbench is really sort of a development environment for interacting with your databases. I don't think you can hid what you dont want to see unless you just close the schema pane. There are other tools you can try like Tables Plus that may do more of what you are looking for
Hi Matt, tks for the great video. However, how to download the data file and upload
Here is a link to all the MySQL files: github.com/mjmacarty/mysql
Great video! Is it possible to import more than 1000 rows with the wizard? Does creating the DB manually allow for more?
Thanks. Yes you can import much larger files it just may be a little slow. The limit on output can also be changed to allow more or less results to display in your result set.
thank you for your video
Glad it helped
Hi Matt, how could we upload csv files using an sql script to upload instead of doing need manually?
This tends to be problematic due to security settings in MySQL. You are looking for LOAD DATA INFILE [filename]. Here's a link to the documentation: dev.mysql.com/doc/refman/8.0/en/load-data.html
@@MattMacarty Thank you for the feedback
thank you
Glad it helped
@@MattMacarty 😇😇
i am facing this problem while importing data
unhandled exception :list index out of range
and data is not importing
please help me with this if u can???
It sounds like the data you are trying to import may have to be cleaned. Probably some kind of invalid data. Try opening the file with a text editor. Usually Workbench will tell you what line in the data the problem happens at.
Hi Bhuvan, after view great video, to review LoaderDataPlus tool. This tool import flat files and export to MYSQL, MARIADB sintax and other DMBS, also parsing and detect data type.
Thanks both of you there was some problem with the data ....I solved it... thanks again
When trying to update the date I got the following: 12:37:14 UPDATE amazon_csv SET t_date = str_to_date(t_date, "%m/%d/%Y") Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. I'm having more problems with the date than you can possibly imagine.
Ok, yes the date can be a pain. If this don't absolutely need it to be a date type you can probably just leave as a text. My guess is the dates are in a different format then you have specified. With your str to date function.
What should I do with the large data having more than 20K rows with messy/NULL values..?
I may not know if the data type has really maintained to the entire row or not (and not possible to check entire dataset manually).. It's getting very tough to import the data successfully to MySQL table.
The Import Data Wizard is really slow in this task. Is there any other way to this more efficiently whithout loosing anything ?
please let me know..
I'm unable to complete my portfolio projects 😥
You can try the import as a generic data type like text. Workbench is very slow with larger datasets. You may wan to try something like TablePlus
sir what if we use d/m/y date format instead of m/d/y ..will it work ?
The goal should be to get dates into ISO format for sorting. Yes you can use %d/%m/%Y to import European format
Thank you so much!
Glad it helped
Thanks man
Glad it helped
Very helpfull! Great tutorial.
Glad it helped
A nice and clear intro to SQL workbench - MANY THANKS
A qq, do I need to 'drop' the table every time when I wish to run the code before moving on to the next part? I never used the 'drop' trick, so workbench just returned a red cross with table already exists? TA !
Hi no you don't need to drop table unless you want to start over.
@@MattMacarty thanks for replying. I tried to re-run the whole program after inserting data into the table i created in part 1, but SQL workbench can't run that saying the table is already exist ? Cheers
@@khong6460 OK, just skip the line where you CREATE TABLE. You can run one line at a time in the Workbench editor, or just comment the CREATE line out.
Let me ask, how do we know the latest data that appears from the data that we have imported without importing it again, I mean if we change the data then the new table will also be replaced automatically, how can we do that sir? thank you Sir
You can either manually import or write a script in something like Python to automate this.
Hello,
How to install DVD rental database in MySQL as it's in postgreSQL
It's probably not directly compatible. Even though most of the query language is the same, these two products diverge in database implementation. I would do a search to see if someone has created a MySQL version. I am not familiar with that databse, but it sounds suspiciously similar to Sakila in MySQL
Thank you Matt. Great tutorial and help. Please may I know where is this database stored which we are creating in the localhost. Can we change that location to external drive? 2. How to load .bak datasets, I have downloaded?
In Windows you can find your database files here:
C:\ProgramData\MySQL\MySQL Server 8.0\Data
The programdata folder is hidden by default so go to file explorer and open This PC, then click the view tab at the top and check the Hidden items check box on the right of the ribbon
Hi Matt, thanks for the video! I have a question: why did my import (9 MB .csv) take so long to finish? 30 minutes passed, and it is still going. Would you happen to have any comments on this? Thanks for the time.
Workbench can be pretty slow importing data. There could be a problem with the data too. I would install TablePlus and try the import there. TablePlus is a client that can handle large imports much more quickly, and if there is a problem with data you will see that very quickly.
thank you for your time @@MattMacarty
could you make tutorial about how to make ER Diagram from excel files to mysql worbench ?
Sure. I will try to get to this soon.
@@MattMacarty thank you sir
I can't able to get the data in the table after following all the procedures and executing the query , would you please tell me the solution for that and why that happens?
This usually means there is a problem with either the data itself, or the way you have defined your table in Workbench. You might visually inspect the data in a text editor to see if there are any special characters. You could try importing into a new table and let Workbench determine the data types as an alternative.
Thank you a lot, it's the best tutorial! However, I've faced the problem: 0 records imported. Please, can u say me how to fix it?
This usually means there's a error in the data type you are trying to import. Try importing the data into a new table and then modify the definitions as needed.
Awesome!! Dude
Glad it helped
Glad it helped