title: Import multiple pipe delimited text flat files in the same folder to SQL Server with different columns
body: <p>This is a question similar to a previous question I have asked here :
  <br/> <a href="https://stackoverflow.com/questions/70695001/import-multiple-flat-files-in-the-same-folder-to-sql-server-using-flat-file-name">Import multiple flat files in the same folder to SQL Server using flat file name as table name</a> <br/>but much more tailored and detailed <a href="https://meta.stackoverflow.com/questions/332820/what-to-do-when-someone-answers-dont-be-a-chameleon-dont-be-a-vandal">(don't be a chameleon)</a></p>
  <h1>The Question</h1>
  <p>The aim of this question is to find efficient way to load in <b>over 200 pipe ( | ) delimited text files </b> at the same time (on a regular basis and over night).<br/><br/>
  Each text file has a different number of columns and column names.<br/><br/>
  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: <b>Extract_MachineTypes_1</b>.<br></p>
  <p>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.<br/><br/>
  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.</p>
  <p>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.</p>
  <br/>
  <h2>Sample Data</h2>
  <p>MachineType_1</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>MachineType</th>
  <th>No.</th>
  <th>LongName</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>1</td>
  <td>Alongname</td>
  </tr>
  <tr>
  <td>b</td>
  <td>2</td>
  <td>blongname</td>
  </tr>
  <tr>
  <td>c</td>
  <td>3</td>
  <td>clongname</td>
  </tr>
  <tr>
  <td>d</td>
  <td>4</td>
  <td>dlongname</td>
  </tr>
  <tr>
  <td>e</td>
  <td>5</td>
  <td>elongname</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>MachineType_2</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>MachineType</th>
  <th>No.</th>
  <th>LongName</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>f</td>
  <td>6</td>
  <td>flongname</td>
  </tr>
  <tr>
  <td>g</td>
  <td>7</td>
  <td>glongname</td>
  </tr>
  <tr>
  <td>h</td>
  <td>8</td>
  <td>hlongname</td>
  </tr>
  <tr>
  <td>i</td>
  <td>9</td>
  <td>ilongname</td>
  </tr>
  <tr>
  <td>j</td>
  <td>10</td>
  <td>jlongname</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Animal Type</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Animal ype</th>
  <th>Friendly with</th>
  <th>LongName</th>
  <th>extra</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Dog</td>
  <td>1</td>
  <td>Donkey</td>
  <td>words</td>
  </tr>
  <tr>
  <td>Bat</td>
  <td>2</td>
  <td>N/A</td>
  <td>more words</td>
  </tr>
  <tr>
  <td>Cat</td>
  <td>3</td>
  <td>Dog</td>
  <td>less words</td>
  </tr>
  <tr>
  <td>Donkey</td>
  <td>4</td>
  <td>No-one</td>
  <td>test?</td>
  </tr>
  <tr>
  <td>Elephant</td>
  <td>5</td>
  <td>Mice</td>
  <td>sof</td>
  </tr>
  </tbody>
  </table>
  </div><h2>Expected Output</h2>
  <p>The expected output of this would be two tables loaded into the server with type nvarchar(255) with the table names <b> MachineType (10 entries) </b> and <b> AnimalType (5 entries) </b></p>
  <h2>My attempt</h2>
  <pre><code> $ImportFolder = &quot;location\&quot;
  $Instance = &quot;servername&quot; t
  $Database = &quot;dbname&quot; #The database you are inserting the data into
  $Files = Get-ChildItem -Path $ImportFolder -Filter &quot;*.txt&quot; 
  #Loop the files
  foreach($File in $Files){
      #Create the creation statement
      Write-Verbose &quot;Defining table creation script for $($File.Name).&quot;
      $CreateSQL = &quot;CREATE TABLE #Rows (DelimitedData nvarchar(255));`n&quot; +
                   &quot;BULK INSERT #Rows FROM '$($File.FullName.Replace(&quot;'&quot;,&quot;''&quot;))' WITH(FIELDTERMINATOR = '|', ROWTERMINATOR = '0x0a', FIRSTROW = 1, LASTROW = 1);`n&quot; + 
                   &quot;DECLARE @SQL nvarchar(MAX);`n&quot; +
                   &quot;SELECT @SQL = N'CREATE TABLE dbo.[$($File.BaseName.Replace(&quot;]&quot;,&quot;]]&quot;))] (' + 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&quot; + 
                   &quot;EXEC sys.sp_executesql @SQL;&quot;
      #Execute the statement
      Write-Verbose &quot;Table creation script for $($File.Name) generated:&quot;
      Write-Verbose $CreateSQL
      Invoke-SqlCmd -ServerInstance $Instance -Database $Database -Query $CreateSQL
      #Create the Insert statement
      Write-Verbose &quot;Defining BULK INSERT script for $($File.Name).&quot;
      $InsertSQL = &quot;BULK INSERT dbo.[$($File.BaseName.Replace(&quot;]&quot;,&quot;]]&quot;))] FROM '$($File.FullName.Replace(&quot;'&quot;,&quot;''&quot;))' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0x0a', FIRSTROW = 2);&quot;
      Write-Verbose &quot;BULK INSERT script for $($File.Name) generated:&quot;
      Write-Verbose $InsertSQL
      #Execute the statement
      Invoke-SqlCmd -ServerInstance $Instance -Database $Database -Query $InsertSQL
  }'
  </code></pre>
  <p>Current errors:</p>
  <pre><code>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
   
  </code></pre>
  <p>Any help on this would be great!</p>
  <p>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.</p>