Databases
StackOverflow2013
Postsdbo
POLoad text into SQL Server via Powershell
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLoad text into SQL Server via Powershell
    text
    copied!<p>I am loading character data read from a file row by row into a SQL table. The table is:</p> <pre><code>CREATE TABLE [dbo].[PSFileOrder]( [PSFOrder_Id] [int] IDENTITY(0,1) NOT NULL, [PSFile] [varchar](255) NOT NULL, CONSTRAINT [PK_PSFileOrder] PRIMARY KEY CLUSTERED ( [PSFOrder_Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] </code></pre> <p>The powershell code I am using is </p> <pre><code>#LoadPSFile $PSFile = "d:\ps\xx.ps1" cls $xy = Get-content $PSFile $xy foreach($xy in $xy) {invoke-sqlcmd -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values ('$xy')"} </code></pre> <p>If the file I am loading is:</p> <pre><code>#Filename #The first line will always be the file name cls $filter = "*.*" $folder = "\\localhost\d$\Master\men and their toys" $filecount = (Get-ChildItem -literalpath $folder -filter $filter).Countem -literalpath $folder -filter $filter).Countem -literalpath $folder -filter $filter).Countem -literalpath $folder -filter $filter).Count If ($filecount -lt 1) {$filecount = 0} "There are {0} files in the {1} directory" -f $filecount, $folder </code></pre> <p>the file loads without error. If there is a single quote in the text </p> <pre><code>"There are {0} files in the {1} d'irectory" -f $filecount, $folder </code></pre> <p>then I will recieve this error </p> <pre><code>Invoke-Sqlcmd : Incorrect syntax near 'irectory'. Unclosed quotation mark after the character string ' -f $filecount, $folder') ;'. At C:\Users\RC\AppData\Local\Temp\2ed13f21-5b46-4df4-a5ee-2488c3dd5ee4.ps1:6 char:35 + foreach($xy in $xy) {invoke-sqlcmd &lt;&lt;&lt;&lt; -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values ('$xy')"} + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand </code></pre> <p>I do believe the error is caused by the way I have written the "-query" statement using single quotes arount the $xy variable.<br> My questions are:</p> <ol> <li>Have I crafted the Invoke-sqlcmd correctly?</li> <li>Is there a substitue for the single quote around $xy?</li> <li>Is there a better way to load the text?</li> </ol> <p>I want to maintain the one to one relationship of the text file and row in the db.</p> <p>Thanks RC</p> <p>Two answers put me on the correct path of doubling the single quote prior to loading to SQL. The revised code is</p> <pre><code>#LoadPSFile cls Get-content "d:\ps\xx.ps1" | Foreach-Object {$_ -replace "'", "''"} | ForEach-Object{invoke-sqlcmd -serverInstance localhost\sqlexpress -query "Insert AA.dbo.PSFileOrder(PSFile) Values ('$_')"} </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload