Data Import using PowerShell into MySQL database.

 

When it was asked to work on PowerShell to import data into the MySQL database. It is seen that very less things are written for using MySQL database using PowerShell. So, it was a very big hurdle while starting this assignment.

While searching on the internet there are two modules that are majorly used while working on MySQL database using PowerShell.

1.       MySQLCmdlets

2.       SimpleSQL

These two modules make up most of our work for MySQL with PowerShell. For this task now we were having two ways to import CSV files to MySQL tables.

1.       Write a program to investigate each row and inset data into the table.

2.       Load is one command which is there in MySQL database, which imports CSV files to table.

The load command is a very fast way to import CSV file data into a MySQL table. So, we thought to go ahead with this load command.

 So, we imported SimpleSQL the module into PowerShell, we made a database connection using the following steps.

Import-Module simplysql

Get-Module simplysql              - Check the weather module got imported

 

Open-MySqlConnection -Server 'Localhost' -Database 'test' -Credential (Get-Credential)

Invoke-SqlQuery 'show databases' - Check weather mysql commands are working

 

Database         

--------         

information_schema

mysql            

performance_schema

sys              

test    

 

 

To this time, it is working fine. Now it was the load command which will get the data imported into a particular table.

 

Invoke-SqlQuery 'load data local infile "D:\powershell\target.csv" into table target'

This  is mysql data import using load command to mysql database. But it was giving error.

"Loading local data is disabled; this must be enabled on both the client and server

sides"

"Loading local data is disabled; this must be enabled on both the client and server  sides"

So we checked the parameter  for this server, it this enabled or not. The setting needed for this is local_infile, following is command used for checking same.

SHOW GLOBAL VARIABLES LIKE "local_infile"

 

It shows that it is enabled for this PowerShell environment.

PS C:\Windows\system32> Invoke-SqlQuery 'SHOW GLOBAL VARIABLES LIKE "local_infile"'

 

 

 

Variable_name Value

------------- -----

local_infile  ON  

 

 

Since his is localhost, server and client is having the same configuration. Somewhere I read we can enable this parameter exclusively for this connection using the following statement.

mysql --local-infile=1 -u root -p1

 

While connecting itself, you can mention making local-infile to be enabled. But this module which we have imported was not having the facility to add these options while making this connectivity. It was giving an error for this –local-infile option usage.

Open-MySqlConnection : A positional parameter cannot be found that accepts argument '--local-infile=1'.

Now, it was the only option left to use the MySQL command line through PowerShell, which we have done by importing the MySQL bin path into the path variable. So that MySQL commands can be accessed using PowerShell.

Now we have written as follows

$csvfile = "'C:/Users/csv/transport.csv'" –Get the path of file. You must have observed    over here, the slash used is forward slash over here, not the regular backward

$query = "LOAD DATA LOCAL INFILE "+ $csvfile +" INTO TABLE transport FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES ("+$columnNames+");"                       

 - This will prepare the load query into a variable called query

mysql.exe --local-infile=1 -C -B "-h$server" "-P$port" "-u$user" "-p$password" "-D$db" "-e$query"

 

- Using this, you can use any of MySQL commands through PowerShell

 

And Project successful!!!