sql server: how to bulk load csv file with " quota every field?

I have a pipe delimited csv file, but every field was quoted as following

"111"|"test"|"abc"

Now I used powershell to remove all " and then save it before the bu开发者_开发问答lk insert. Is it possible just load it without removing "?

Yes, you need a format file to deal with different column separators " then "|" then "|" ... ".

By default, bcp and BULK INSERT deal only with consistent separators

I ran into this same issue a couple years ago and never found a way for the bulk insert to ignore it. After about an hour of researching I decided to just ask our client to re-export the file without the quotes. Fifteen minutes later I was up and running. I'd love to know if there's actually a way to accomplish this, but the bulk-insert feature always seemed rather inflexible so I decided not to burn too much time on it.