Connect MySQL to squirrel Client and importing csv and excel files in MySQL

SQuirreL is great because is universal and platform independent. You can use it to connect to almost every database that you can imagine.
The only drawback is that it doesn't ship with the database drivers due to licensing issues.

So here is a step-by-step procedure for connecting to MySQL from SQuirreL.

First of all verify that you have network access to the mysql host and its port. Usually mysql servers run at port 3306, so test the connection from your host:

telnet xxx.xxx.xxx.xxx 3306

If you get something like:

Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused

then you won't be able to connect.

The correct response you should get is something like:

Trying xxx.xxx.xxx.xxx...
Connected to xxx.xxx.xxx.xxx.

Then you must check that the database user you will be using has access from the host that the squirrel client will run.

You can check the privileges using phpmyadmin, or by command line:
GRANT USAGE ON *.* TO 'user'@'hostname' 

    Download the latest Squirrel client from the url below:

   http://www.squirrelsql.org/#installation

Squirrel SQL Client is a graphical Java program that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc. The minimum version of Java supported is 1.6.x as of Squirrel version 3.0.  To download Java -        

        http://www.java.com/en/download/index.jsp

2. installing the squirrel-sql-3.0.3-install.jar 

The SQuirreL SQL Client is installed with the IzPack Java installer. Download the file squirrel-sql-<version>-install.jar and execute it using the following command: 

                java -jar squirrel-sql-<version>-install.jar

Make sure that you have write privileges to the directory that you want to install into.

Note that some browsers may attempt to open this file rather than download it. In this case, right-click on the download link and click your browser's equivalent of the "Save as..." menu item.

On Windows, execute the file squirrel-sql.bat to run the application. On Unix, the file is squirrel-sql.sh

Adding Mysql Driver to squirrel sql

 

Many of you might have faced the issue of loading the driver for the SQuirreL for specific Database Drivers. For myself I faced issue when I do not have MySql installed in my machine, the SQuirreL is unable to configure the driver.

The below is simple step how to configure the drivers manually by downloading the driver files from the provider's site.

Step 1: Download the JDBC driver library (*.jar) from the provider's site.
For example, the mysql JDBC drivers are available for download from mysql site (brlow url).

                    http://dev.mysql.com/downloads/connector/j/

           Copy the “Text_JDBC30.jar” file to the “lib” directly of the installation directory

         C:\Program Files\SQuirreL SQL Client\lib (if you didn’t change the location.

Launch the Squirrel client – Create a desktop short-cut if desired

         C:\Program Files\SQuirreL SQL Client\squirrel-sql.bat

  

Step 2: Launch SQuirreL Application

Step 3: Click on Drivers tab from left hand side (or) from Menu Drivers

Step 4: Under the Drivers List find the selected database. In our case "MySQL Driver". Right click on the database and click on <Modify Driver...> option



Step 5: The Database driver window will pop up.  Now click on "Extra Class Path" Tab.



Step 6: Click on <Add> action button. And select the downloaded driver file (jar) from the path and click <Open> action button.
Note: Repeat the Step 6 if you have more than on .jar file for the selected Database Driver 



Step 7: Now you can see all the added jar files are listed under the "Extra Class Path". Click <Ok> to apply the changes to the Database Drivers.

Creating Aliases for Mysql In squirrel client

step 1:Click on “Aliases” tab and then click on the “+” icon

Step 2 :After clicking + icon it will display the Alias window

Step3 : under name =name of the alias

           under driver select the driver from the list which ever required

           under url specify the hostname:portno of DB or required input

          under username=give the username of the db

          under the password=specify the appropriate password for the given user

          select auto logon or connect at startup which ever we required

Step 4: click on Test the connection to verify whether the given parameters are right and click ok'it will 

            connect to the DB.

How to import data from csv and excel file

Step1: Click on plugins -->summary it will show all the plugins and check whether data import 

             plugin is enabled

Step 2:Add a jar for text_jdbc in the lib folder and and modify the driver and select the  class path of the jar .

Step 3 :select the  database-->table in which the data have to be imported

Step 3:create the schema attribute for the required imput of the file in the desired table

Step 4;select the table-->right click -->import data

Step 5:if we import the data it will pop up up this window and it ask to delete the existing record

              

               Step 6: select the csv file /excel file from the saved location

        Step 7 :Select the delimiter type

           Step 8: This will Display all the input which was imported from the file

                               and we can map the attributes to the table by selecting one to one                                         mapping

              Step 9: The data will be imported in the table.

Video for connecting MySQL to squirrel client: