CODEWITHSUNDEEP
powershellbcputility
gayathri
gayathri

Reputation: 43

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

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.

Upvotes: 0

Views: 889

Answers (1)

iRon
iRon

Reputation: 23830

$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",,,,,,

Upvotes: 0

Related Questions