How to write a loop to read a text file and insert it into a database

I am writing a powershell script to load text files from a specific folder into a SQL Server database, I succeeded, but only one file at a time. I want the program to do this automatically as soon as the user enters the path and file extension.

For example:

In C: \ Test path I have:

• Test1.txt
• test2.txt
• Test3.txt
• Abcd.txt

I want the program to iterate over the file and automatically insert it into the database. The number of files in a folder can vary from 10 files to 100 files, and the number of columns for each file is not fixed.

What I have done so far:

• Created a stored procedure in SQL Server to create a new table based on the filename and call it through my powershell script.

• Wrote a powershell script that requires users to enter path, filename and extension (txt, csv, etc.), and once the data is captured, it will create a table on the SQL server.

Right now, I am stuck in a looping process as I cannot figure out how to get the program to read Test1.txt, insert it into the SQL server, and then proceed to read Test2.txt, insert it into the SQL server, until the end of the file in this 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"