This is a question similar to a previous question I have asked here :
Import multiple flat files in the same folder to SQL Server using flat file name as table name
but much more tailored and detailed (don't be a chameleon)
The Question
The aim of this question is to find efficient way to load in over 200 pipe ( | ) delimited text files at the same time (on a regular basis and over night).
Each text file has a different number of columns and column names.
All of the text files are stored in the same folder with their name associated with their assigned table name and table extract ID ie: Extract_MachineTypes_1.
Using a Power Shell script looks to be the best option. I have made multiple attempts connecting to my server and using BULK INSERT but to no avail. I will attach my latest attempt at the bottom of this post.
Each table can be uploaded to the server with the column type nvarchar(255) as they will be processed after the fact at a later time.
Running the final script via an SQL Agent would be the best resolve for scheduled running, hence calling the Powershell script require setting up also.
Sample Data
MachineType_1
| MachineType | No. | LongName |
|---|---|---|
| A | 1 | Alongname |
| b | 2 | blongname |
| c | 3 | clongname |
| d | 4 | dlongname |
| e | 5 | elongname |
MachineType_2
| MachineType | No. | LongName |
|---|---|---|
| f | 6 | flongname |
| g | 7 | glongname |
| h | 8 | hlongname |
| i | 9 | ilongname |
| j | 10 | jlongname |
Animal Type
| Animal ype | Friendly with | LongName | extra |
|---|---|---|---|
| Dog | 1 | Donkey | words |
| Bat | 2 | N/A | more words |
| Cat | 3 | Dog | less words |
| Donkey | 4 | No-one | test? |
| Elephant | 5 | Mice | sof |
Expected Output
The expected output of this would be two tables loaded into the server with type nvarchar(255) with the table names MachineType (10 entries) and AnimalType (5 entries)
My attempt
$ImportFolder = "location\"
$Instance = "servername" t
$Database = "dbname" #The database you are inserting the data into
$Files = Get-ChildItem -Path $ImportFolder -Filter "*.txt"
#Loop the files
foreach($File in $Files){
#Create the creation statement
Write-Verbose "Defining table creation script for $($File.Name)."
$CreateSQL = "CREATE TABLE #Rows (DelimitedData nvarchar(255));`n" +
"BULK INSERT #Rows FROM '$($File.FullName.Replace("'","''"))' WITH(FIELDTERMINATOR = '|', ROWTERMINATOR = '0x0a', FIRSTROW = 1, LASTROW = 1);`n" +
"DECLARE @SQL nvarchar(MAX);`n" +
"SELECT @SQL = N'CREATE TABLE dbo.[$($File.BaseName.Replace("]","]]"))] (' + N' nvarchar(255)',N',') WITHIN GROUP (ORDER BY DSL.ItemNumber) + N');' FROM #Rows R CROSS APPLY Utility.fn.DelimitedSplitN4K_LEAD(R.DelimitedData,N',') DSL;`n" +
"EXEC sys.sp_executesql @SQL;"
#Execute the statement
Write-Verbose "Table creation script for $($File.Name) generated:"
Write-Verbose $CreateSQL
Invoke-SqlCmd -ServerInstance $Instance -Database $Database -Query $CreateSQL
#Create the Insert statement
Write-Verbose "Defining BULK INSERT script for $($File.Name)."
$InsertSQL = "BULK INSERT dbo.[$($File.BaseName.Replace("]","]]"))] FROM '$($File.FullName.Replace("'","''"))' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0x0a', FIRSTROW = 2);"
Write-Verbose "BULK INSERT script for $($File.Name) generated:"
Write-Verbose $InsertSQL
#Execute the statement
Invoke-SqlCmd -ServerInstance $Instance -Database $Database -Query $InsertSQL
}'
Current errors:
Invoke-SqlCmd : A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Incorrect syntax near ')'.
At PowerSheel Auto Upload Script.ps1:20 char:5
+ Invoke-SqlCmd -ServerInstance $Instance -Database $Database -Quer ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Invoke-SqlCmd : Invalid object name 'dbo.file'.
At PowerSheel Auto Upload Script.ps1:30 char:5
+ Invoke-SqlCmd -ServerInstance $Instance -Database $Database -Quer ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Any help on this would be great!
Ideally an SQL Server Job that can be run on a schedule that runs a PS script within a select folder to upload multiple files is the best fit for this problem.
