Today I am gonna show you one really cool feature of SQLYOG, a must have software for mySQL administrator or users.
We will be covering how we can import a CSV into a database and create a table automatically. You can rename the table after the import if you like.
I now can’t think of my life without SQLYOG.
All right lets get started.
First Fire up SQLYog, have your CSV file ready
I believe you are connected to your mysql database where you would like to import your CSV (A connection is not required as such but I always want to see what available before I make a call to import )
Below is what I am going to import
I created a folder called importfolder and pasted CSV to be importent in there to keep it simple. There is no limit however you can use any URL. This is me who does few things like that to make my life easy so that I know what I am doing.
Ok so Goto Powertools > Import External Data
You will get this screen
Now Keep “Start New Job” selected and click Next.
After you click Next you will see the screen below.
Keep the CSV option checked and browse to your CSV file. Here is the real deal. you can actual import more than 1 CSV in one go and convert them to a mySQL table.
Ok so Click Next and you will see the screen below
Now make sure that you’ve chosen the right connection and you select the right database. It does not really matter because you are creating something not dropping anything, but I always recommend that you should know where the changes are being made.
So with the database selected Click Next
Below is the screen that will show up
This is important, Now because we are importing our CSV to create a table we will keep the first option checked. If you select the second option you will be asked to input a query that will be used to import or select specific data to be imported.
With the first option checked click Next.
You will see the screen below
This is really important step in the import wizard. As you can see the CSV that we wish to import appears up there. If you have many CSV you will have option to select any of them.
Destination: Is the name of the resultant table.
Map: Select columns you want.
Advanced: You can select Trigger option, Change the resultant Table engine type, and there are other useful options that are really English.
Where: This is interesting and really powerful. You can add your conditions to say what data you want to be in you table.
Once everything is set Click on Next
You will see screen below
Now you can choose to abort on error and you can send yourself email notifications on warnings, errors etc. Really handy if the CSV to be imported is really large and will take some time.
Now you have the option to run this job immediately or Save it or you can save and schedule it for later run.
A log file will be create for this import job. You can change the location of the log file if you want.
Your job will now Run if you haven’t scheduled it. And produced something similar to this
If there are any warnings or error those will be show where. But for this job everything went fine. You will see a new table create under the database you initially selected for import. below is what I got
Ta! Da! I got my table from a CSV without much effort. total 10 clicks and that’s it.
I hope this article helped you.
If you have any questions don’t forget to ask