SQL Server Video Automating Flat File Imports using PowerShell


By:   |   Updated: 2014-06-18   |   Comments (6)   |   Related: More > PowerShell




 






Enter your business email address to
get free SQL Server tips.

Problem / Solution

SQL Server DBAs and Developers often get flat files that they need to import into SQL Server for queries and data manipulation.  You could easily use SSIS to do imports or use the import wizard within SSMS, but this takes time to setup and if you have multiple files it can become a time consuming task.  In this video tip, Tim Smith shows how you can use PowerShell to dynamically read the flat file, dynamically create a SQL Server table and then import the data into SQL Server with an easy to use PowerShell function.  With this function you can pass parameters to the function and use this to automate the creation and import of flat files.  Take a look at this video tip and also read the companion tip to get a step by step explanation and access to the code.

Key Learning Items
  • Using PowerShell variables
  • Using SQLConnection from PowerShell
  • Using SQLCommand from PowerShell
  • Running SQL queries and stored procedures from PowerShell
  • Dynamically building a table and inserting data into SQL Server from PowerShell
  • Creating a PowerShell function
Read Companion Tip


Last Updated: 2014-06-18


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Comments For This Article




Saturday, April 25, 2020 - 2:55:34 AM - mike Back To Top (85470)

Hi terrific script thanks.

I was hoping that this would allow me to import my CSV. 

My data includes quote qualified text fields where those fields include carriage returns and commas.  

Incredibly there does not appear to be a true CSV import format that can be used with BULK.  That being the case BULK determines a new row or new field midway through my  " qualified text field.


Monday, May 07, 2018 - 6:32:54 PM - Ron Abbott Back To Top (75887)

Great topic

I got the code working fine the other day, but then tried to add code to drop the table first so that I can recreate it each time. Now the code doesnt work anymore!. I have deleted the extra code I added, going back to what I thought had worked.

Here is my code  with the errors (running from Powershell_ise

PS C:\Windows\system32> Function CreateRestOwners($location, $file, $extension)

{

$full = $location + $file + $extension

$all = Get-Content $full

$columns = Get-Content $full | Select -First 1

$columns = $columns.Replace(" ","")

$columns = $columns.Replace("`t","] VARCHAR(200), [")

$table = "CREATE TABLE " + $file + "([" + $columns + "] VARCHAR(100))

$connection = New-Object System.Data.SqlClient.SqlConnection

$buildTable = New-Object System.Data.SqlClient.SqlCommand

$insertData = New-Object System.Data.SqlClient.SqlCommand

$connection.ConnectionString = "Data Source=LPCONVVM-ADMIN\SQLDEVELOPMENT;Database=Rest01;integrated security=true"

$buildTable.CommandText = $table

$buildTable.Connection = $connection

$x = 0

$insertData.CommandText = "EXECUTE stp_CommaBulkInsert @1,@2"

$insertData.Parameters.Add("@1", $full)

$insertData.Parameters.Add("@2", $file)

$insertData.Connection = $connection

$connection.Open()

$buildTable.ExecuteNonQuery()

$connection.Close()

$buildTable.CommandText = $table

$x = 1

if ($x = 1)

{

        $connection.Open()

        $insertData.ExecuteNonQuery()

        $connection.Close()

}

}

 

Unexpected token 'data' in expression or statement.

At line:12 char:33

 

Unexpected token 'Source=LPCONVVM-ADMIN\SQLDEVELOPMENT' in expression or statement.

At line:12 char:38

 

Unrecognized token in source text.

At line:16 char:59

 

Missing expression after ',' in pipeline element.

At line:16 char:58

 

Unexpected token '2' in expression or statement.

At line:16 char:59

 

Unexpected token '

$insertData.Parameters.Add(' in expression or statement.

At line:16 char:61

 

Unexpected token '1' in expression or statement.

At line:17 char:29

 

Unexpected token ', $full)

$insertData.Parameters.Add(' in expression or statement.

At line:17 char:31

 

Unexpected token '2' in expression or statement.

At line:18 char:29

 

The string starting:

At line:18 char:31

+ $insertData.Parameters.Add("@2 <<<< ", $file)

is missing the terminator: ".

At line:32 char:1

 

Missing closing '}' in statement block.

At line:32 char:1

 

Missing function body in function declaration.

At line:32 char:1

 

 


Monday, February 16, 2015 - 2:19:13 PM - Greg Robidoux Back To Top (36255)

H Jack, take a look at this tip for the scripts: http://www.mssqltips.com/sqlservertip/3208/automating-flat-file-sql-server-imports-with-powershell/

 


Monday, February 16, 2015 - 1:59:51 PM - Jack Owens Back To Top (36254)

Great demo

Could you please let us know where can I download your demo ".ps1" file from?

Is your file shared by any chance?


Wednesday, February 11, 2015 - 5:25:08 PM - JeremyH Back To Top (36210)

Nice little video Tim. About 4.5 minutes in, where you mention how SQL Server doesn't recognize the new tables as existing yet (because the session was opened before the tables were created), you can refresh that session by pressing ctrl + shift + 'r'. Just a little SSMS tip.


Wednesday, January 07, 2015 - 5:32:20 PM - Ted Back To Top (35863)

Great vid and info Tim!  Thanks for sharing.

 

Ted Higgins



download





Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools