Append to Beginning of File-CodePudding

Background

I have to run SqlCmd and Bcp to get the output of an Sql table. There are better ways, but this is what I have at my disposal.

First, I run SqlCmd to get the table headers and save to C:\headers.csv.

SqlCmd -S Server -d Database -Q "set nocount on; select top 0 * from TableName;" -o "C:\headers.csv" -s "," -W

Next, I run Bcp to get the data from the table and save to C:\data.csv.

Bcp TableName out "C:\data.csv" -S Server -d Database -T -t , -c

I end up with two files headers.csv and data.csv. I need to append the content of headers.csv to the beginning of data.csv.

My Attempt

Annoyingly, SqlCmd adds a row of hyphens after the headers. I strip out that line with:

Get-Content -Path "C:\headers.csv" -TotalCount 1 | Out-File -FilePath "C:\final-output.csv" -Force

This is what I came up with do the final "join" of the two files:

[System.IO.File]::AppendAllLines("C:\final-output.csv",[System.IO.File]::ReadLines("C:\data.csv"))

I'm running on Server 2016. Using PowerShell is there a faster way to read or write the final-output.csv?

CodePudding user response：

This isn't a built-in supported operation, appending is generally done to the end of the file. Your only option I'm aware of is to read in the current contents, insert the new content at position 0, and re-write the file. For this we'll use a List to help with this process and prevent string/array concatenation:

Note: This sample assumes the target file encoding is UTF8. There is no way for .NET to know what encoding the current file is written in, so you will need to know this ahead of time and change the encoding accordingly. Review the derived types in the prior link to understand the supported encodings PowerShell/.NET supports out-of-the-box.

# $newContent should be what you want to append to the start of the existing file
# If $newContent is already an array of text lines then you do not need to worry about
# splitting on `[Environment]::NewLine` and you can pass $newContent in directly.
$newContent = [System.Generic.Collections.List[string]]::new(( $newContent -split [System.Environment]::NewLine ))

# $filePath should be the file you want to append to the beginning of
$newContent.Add(( Get-Content $filePath ))

# Write back out to file, overwriting the existing content ($newContent should have it all)
$newContent | Out-File -Encoding UTF8 -Force $filePath

Unfortunately, this is not going to be particularly performant, though the use of List[string] prevents unnecessary duplication of the existing content which should help, especially if it is a larger file. The downside is that you must read in all of the existing content at the end (or alternatively read the file contents first and insert the new content at index 0, though this sample does not do this), and so the entire file must be re-written. As I mentioned at the start, writing directly to the start of a file and not the end is not a supported operation.

CodePudding user response：

I haven't read much on how both commands work so please correct me if I'm wrong.
Assuming SqlCmd returns an array to stdout if we remove -o "C:\headers.csv", we could store the headers like so:

$headers = SqlCmd -S Server -d Database -Q "set nocount on; select top 0 * from TableName;" -s "," -W
$headers = $headers[0]

If it doesn't return an array an actually returns a multi-line string, then this should work:

$headers = SqlCmd -S Server -d Database -Q "set nocount on; select top 0 * from TableName;" -s "," -W
$headers = $headers -split '\r?\n' | Select-Object -First 1

Then, assuming if we remove out "C:\data.csv" from Bcp it returns the contents of the Table to stdout, we can store the results to the $data variable:

$data = Bcp TableName -S Server -d Database -T -t , -c

Now you should be able to do something like this to merge both variables:

$headers, $data | Out-File path\to\csv.csv

A simple example:

# This would be a multi-line string
$headers = @'
col1,col2,col3
--------------
'@

$headers = $headers -split '\r?\n' | Select-Object -First 1

# Then something random for $data
$data = 0..5 | ForEach-Object { $x = Get-Random; "$x,$x,$x" }

# Lastly:
PS \> $headers, $data
col1,col2,col3
1975011385,1975011385,1975011385
858903029,858903029,858903029
1830294256,1830294256,1830294256
1252960811,1252960811,1252960811
1400092566,1400092566,1400092566
1412627916,1412627916,1412627916

# If we pass this to Out-File it should look exactly as we're seeing it on the console