SQL import multiple flat files using powershell

Question

I'm building a script to import multiple flat text files to the SQL server management studio. Currently I'm able to import one file using

AutoImportCommaFlatFiles -location "C:\..." -file "test" -extension ".txt" -server "Database" -database "test"

Is there a way to import all text files from a folder like -file "*" instead of using above code for every new file

My current code is

Function AutoImportCommaFlatFiles($location, $file, $extension, $server, $database)
{
$full = $location + $file + $extension
$all = Get-Content $full
$columns = $all[0]
$columns = $columns.Replace(" ","")
$columns = $columns.Replace("|","] VARCHAR(100), [")
$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=" + $server + ";Database=" + $database + ";integrated security=true"
$buildTable.CommandText = $table
$buildTable.Connection = $connection
## Added to function;
$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()
## Added to function
$x = 1
if ($x = 1)
{
    $connection.Open()
    $insertData.ExecuteNonQuery()
    $connection.Close()
}
}
 AutoImportCommaFlatFiles -location "C:\..." -file "test" -extension ".txt" -server "Database" -database "test"
 AutoImportCommaFlatFiles -location "C:\..." -file "test2" -extension ".txt" -server "Database" -database "test""

Bacon Bits · Accepted Answer · 2017-08-10 12:11:02Z

1

Just pipe the files you want from a Get-ChildItem. All the necessary fields are already available in the FileInfo objects:

Get-ChildItem C:\...\*.txt | ForEach-Object {
    AutoImportCommaFlatFiles -location $_.DirectoryName -file $_.BaseName -extension $_.Extension -server "Database" -database "test"
}

answered Aug 10, 2017 at 12:11

Bacon Bits

32.5k6 gold badges63 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MBurger Over a year ago

Thank you What do I need to fill at -location $_.DirectoryName(this) -file $_.BaseName(this) -extension $_.Extension(this) because I already declared it at Get-ChildItem C:\...*.txt ?

Bacon Bits Over a year ago

@MBurger You don't need to fill anything in. That's the exact code that should do what you want. Get-ChildItem returns objects that have several properties, including DirectoryName, BaseName, and Extension. Try something like Get-ChildItem C:\...\*.txt | Select-Object -Property Name, FullName, DirectoryName, BaseName, Extension to see what I mean. The $_ is the "this" variable that ForEach-Object uses. See Get-Help about_Automatic_Variables -ShowWindow for more about $_ or Get-Help ForEach-Object -ShowWindow for more about how to use the ForEach-Object cmdlet.

Collectives™ on Stack Overflow

SQL import multiple flat files using powershell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related