PowerShell script to bcp export data to CSV file from SQL Server

Question

I am using a PowerShell script to export data from an SQL server to a CSV file. I need the CSV file delimited with , and have multiline data to be in single line format.


$tableList =@(
"Test1",
"Sample",
"Access"

)


foreach ($table in $tableList)
{
    Write-Host $table

    $selectQuery = "select * from Test.$table"
    $queryOutPath = "C:\Test\Test_$table\Test_$table`_20230612.csv"
    $mkdirPath = "C:\Test\Test_$table"

    Write-Host $selectQuery
    Write-Host $queryOutPath

    # Create the directory if it doesn't exist
    if (-not (Test-Path -Path $mkdirPath -PathType Container))
    {
        New-Item -Path $mkdirPath -ItemType Directory | Out-Null
    }

    # SQL Server credentials
    $server = "Some Server"
    $database = "TestDB"
    $username = "TestUser"
    $password = "TestPassword"

    # Export data using BCP with credentials
    & 'bcp' $selectQuery queryout $queryOutPath -S $server -d $database -U $username -P $password  -c -t "," -k -r "`"\n"

}

The source table has data as below:

12,other,iutguop,oihdi\,1,15,2014-08-11 17:09:50,itgiug
9,test
UPS
P.O. Box 8769870
VA 986987
1-800-769-XXX

I need the above data in below format:

12,other,iutguop,oihdi\,1,15,2014-08-11 17:09:50,itgiug
9,test UPS P.O. Box 8769870 VA 986987 1-800-769-XXX

Need help to modify the posted PowerShell query to create a CSV file that has multiline data in source to be added in a single line format.

Does this answer your question? [How to write data on multiple lines BUT within the same cell of csv?](https://stackoverflow.com/questions/6516222/how-to-write-data-on-multiple-lines-but-within-the-same-cell-of-csv) — stackprotector, Jun 13 '23 at 06:02

score 0 · Answer 1 · answered Jun 13 '23 at 07:23

$Data = ConvertFrom-Csv @'
id,h1,h2,h3,h4,h5,h6,h7
12,other,iutguop,oihdi\,1,15,2014-08-11 17:09:50,itgiug
9,"test
UPS
P.O. Box 8769870
VA 986987
1-800-769-XXX"
'@

Assuming that you would like to replace all the newline characters from all the fields in each row with a space:

# $Data |ConvertTo-Csv |Foreach-Object { $_ -Replace '[\r\n]', ' ' } |Out-File $queryOutPath
$Data |ConvertTo-Csv |Foreach-Object { $_ -Replace '[\r\n]', ' ' } |Out-String

"id","h1","h2","h3","h4","h5","h6","h7"
"12","other","iutguop","oihdi\","1","15","2014-08-11 17:09:50","itgiug"
"9","test UPS P.O. Box 8769870 VA 986987 1-800-769-XXX",,,,,,

Thank you.will check this out – gayathri Jun 13 '23 at 13:12 — gayathri, Jun 13 '23 at 13:12

PowerShell script to bcp export data to CSV file from SQL Server

1 Answers1