CODEWITHSUNDEEP
sql-serverloopspowershell
Piros
Piros

Reputation: 33

How do I write a loop to read text file and insert it to the database

I'm writing a powershell script to load text files from a specific folder into SQL server database, I have manage to do so but only one file by one file. I want the program to do this automatically once the user inputs the path and the file extension.

For example:

Under C:\Test path, I have:

  1. Test1.txt
  2. Test2.txt
  3. Test3.txt
  4. Abcd.txt

I want to have the program to loop through the file and insert it to the database automatically. The number of files in the folder can vary from 10 files to 100s of files and the numbers of columns for each file are not fixed

What I’ve done so far:

  1. Created a stored procedure in the SQL Server to create a new table based on the file name and calling it through my powershell script.

  2. Wrote the powershell script that needs users to input the path, the filename, and the extension (txt,csv,etc) and once the details are captured it will create a table in the SQL server.

Right now, I’m stuck at the looping process as I can’t figure it out how to make the program read Test1.txt insert it to SQL server then proceeds to read Test2.txt insert it to SQL server, until end of file in that particular folder.

Here's the script:

Function AutoImportFlatFiles
(
$location = $(read-host "Folder Location ('C:\Test\' okay)"), 
$file = $(read-host "File Name Without Extension ('Test1' okay)"),
$extension = $(read-host "File Extension ('.txt' okay)"),
$server, 
$database
)

{
$full = $location + $file + $extension
$columns = Get-Content $full | Select -First 1
$columns = $columns.Replace(" ","")
$columns = $columns.Replace("|","] VARCHAR(2000), [")
$table = "CREATE TABLE " + $file + "([" + $columns + "] VARCHAR(2000))"
$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_BulkInsert @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()
}
}
AutoImportFlatFiles -server "WIN123" -database "DB_DISCOVERY"

Upvotes: 3

Views: 4079

Answers (1)

Jan Chrbolka
Jan Chrbolka

Reputation: 4454

To add a simple loop, you can use your existing AutoImportFlatFiles function like this:

$Folder= $(read-host "Folder Location ('C:\Test\' okay)")
foreach ($file in (get-childitem $Folder)) {
    $location = split-path $file.FullName -Parent
    $filename = (split-path $file.FullName -Leaf).split(".")[0]
    $extension = (split-path $file.FullName -Leaf).split(".")[1]

    AutoImportFlatFiles -location $location -file $filename -extension $extension -server "WIN123" -database "DB_DISCOVERY"
}

Upvotes: 1

Related Questions