How to remove the double quotes and skip last 3 lines in bcp loading into SQL Server?-CodePudding

I am loading data into SQL Server using bcp command line utility. The problem is data is coming is like below. Every field in double quotes and I have to skip last three rows because it has some trailers. How can i solve this? Thanks in Advance. I appreciate if you could help in this.

bcp database.schema.tablename in Filename.text -T -c -t"|" -r"0x0a" -F 3 -m 2

UHDR 20211110
"DATE","CUSIP","ISIN","SEDOL","TICKER","DESCRIPTION","QUANTITY","RATE","COMMENT","MARKET","FEE"
11/10/2021|""|"CA45826T3010"|"BMVXZT5"|"ITR"|"INTEGRA RESOURCES CORP REGISTERED SHS"|"28712"|"0.0000"|"HTB"|"CA"|"11.5000"

CodePudding user response：

If you want to:

unconditionally remove all " chars.
unconditionally skip the last 3 lines:

(Get-Content -ReadCount 0 Filename.text) -replace '"' |
  Select-Object -SkipLast 3 |
    Set-Content Filename_CleanedUp.text

^{Note: -ReadCount 0 is a performance optimization that makes Get-Content read all lines into a single array instead of streaming the lines one by one.}

Then pass Filename_CleanedUp.text to your bcp command.