add-content is not adding a new line

PowerShell Help
1

I need to send a file that has a header row and the data starts on the next line. It is not working. The data is either right next to the header or I can add an extra blank line (which I don’t want).
The problem code is in the last 10 lines.
 



This is a re-usable function that makes a connection to the database and creates a variable you can fetch the result set into


Function Execute-SqlSelect($command,$serverName,$dbName)

{

$resultDataSet = New-Object System.Data.DataSet


$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server=$serverName;Database=$dbName;Integrated Security=SSPI"

$sqlConn.Open()

$sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand

if($sqlConn.State -eq 1)
{
    $sqlCommand.CommandText = $command
    $SqlCommand.Connection = $sqlConn
$sqlCommand.CommandTimeout = 0
    $sqlDataAdapter.SelectCommand = $sqlCommand
    $sqlDataAdapter.Fill($resultDataSet,"result") | Out-Null
    
    $sqlConn.Close()
}

return $resultDataSet



}


#this is the sql run to get the data

$file = “P:\ReportScheduler\SCRIPTS\RelayBillPlacement.sql”


$query = get-content $file


echo $query


$results = Execute-SqlSelect $query “my server” “my database”


$pathname = "c:\downloads"


$filename =  [System.DateTime]::Today.AddDays(-1).ToString(“MMddyyyy”) + “_LPH_fauquier_patinfo.dat”


#this is the header row

$z = “16960|Fauquier Hospital|Fauquier Hospital|”+(Get-Date).ToString(‘MM/dd/yyyy’)+“|06:00:00”


#create the file with ONLY the header line

new-item -path $pathname -name $filename -type “file” -value $z


#this is the file name with the full path

$x = $pathname + $filename


#drop the data into a 2nd file

$results.Tables[0] | export-csv -path “c:\downloads\aNewFile.csv”  -notypeinformation -Delimiter ‘|’ -encoding ASCII


$a = “c:\downloads\aNewFile.csv”


#remove the double quotes

(Get-Content $a) | Foreach-Object {$_ -replace ‘"’, ‘’}|Out-File $a


#!!! this ends up adding a blank line instead of a carriage return

Add-Content -Path $x -Value “`r”


#add the data to the file which only contains the header row

Add-Content -Path $x -Value (Get-Content -Path “c:\downloads\aNewFile.csv”)


DONE ###
2

Export-Csv has the parameter -Append. You could use this. If you really need to remove the double quotes you could do this after you added data to the CSV file with Export-Csv -Append. :wink:
All standard compliant tools should be able to read valid CSV files including double quotes.

3

OK… there’s a few points where this might be going astray. Let me give that script a quick pass for you so I can try to make sure there’s not something weird happening here.

It’s hard to be sure, but I suspect what’s happening is that creating a file with New-Item and supplying the value directly isn’t adding the expected newline to the end of the file. A simple way to avoid this issue would be to just use Set-Content as it will create the file if it doesn’t exist anyway. Set-Content should also ensure that the initial file has the final newline, which avoids the issue of adding the next line improperly.

Here’s my take on your script with all of that cleaned up. I also removed the secondary file, as you can just use ConvertTo-Csv to convert to an in-memory CSV string rather than creating an extra file.

4

Won’t that add the header twice? Why not just use the csv file? By the way, a windows text file has both a carriage return and a line feed. But I don’t think you need to worry about it.

5

Depends what we mean by “header”. Given that a header is added manually and seems to contain metadata, I think the “header” OP speaks of is not the usual CSV header. Sounds like they’re dealing with a homebrew CSV parser that has a header row for file metadata, then a header row for actual CSV entries.