Import-CSV that has single quotes |
|
I needed to import a CSV into SQL but was having issue with the import-export wizard.
I also need to remove any single quotes from a specific field.
$data = Import-Csv -Path C:\somedata.csv
foreach($row in $data ){
try{
## this is the column with single quotes. remove the single quote and replace with two single quotes to escape
$dvalue = $row.default_value -replace"'","''"
invoke-sqlcmd -ServerInstance someserver -Database yo_momma -query "insert into sometable (col1,col2,col3,col4,col5,col6,col7,col8,col9)
values('$($row.name)', '$($row.element)', '$($row.internal_type)', '$($row.reference)','$($dvalue)', '$($row.display)','$($row.text_index)', '$($row.audit)','$([datetime]$row.sys_updated_on)' )"
}
catch [exception]
{
#General Failure
Write-Host table name.. $($row.name) -ForegroundColor Red
write-host type.. -ForegroundColor Yellow
$($row.internal_type)
write-host column name.. -ForegroundColor Blue
$($row.internal_type)
write-host value.. -ForegroundColor Green
$($row.default_value)
##When I want to know the full exception type:
$errormessage = $Error[0].Exception.Message -replace "'",""
Write-Host $errormessage -BackgroundColor blue -ForegroundColor Yellow
}
}
I also need to remove any single quotes from a specific field.
$data = Import-Csv -Path C:\somedata.csv
foreach($row in $data ){
try{
## this is the column with single quotes. remove the single quote and replace with two single quotes to escape
$dvalue = $row.default_value -replace"'","''"
invoke-sqlcmd -ServerInstance someserver -Database yo_momma -query "insert into sometable (col1,col2,col3,col4,col5,col6,col7,col8,col9)
values('$($row.name)', '$($row.element)', '$($row.internal_type)', '$($row.reference)','$($dvalue)', '$($row.display)','$($row.text_index)', '$($row.audit)','$([datetime]$row.sys_updated_on)' )"
}
catch [exception]
{
#General Failure
Write-Host table name.. $($row.name) -ForegroundColor Red
write-host type.. -ForegroundColor Yellow
$($row.internal_type)
write-host column name.. -ForegroundColor Blue
$($row.internal_type)
write-host value.. -ForegroundColor Green
$($row.default_value)
##When I want to know the full exception type:
$errormessage = $Error[0].Exception.Message -replace "'",""
Write-Host $errormessage -BackgroundColor blue -ForegroundColor Yellow
}
}