BULK INSERT and bcp

Is there any way to get BULK INSERT (or bcp) to ignore quotes enclosing character data in an comma-delimited ASCII text file?
My preferred method for this import is to use a bcp format file but I find that the quotes are always imported along with the data.
Thanks
LVL 3
sduckettAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
amitpagarwalConnect With a Mentor Commented:
Thanks sduckett, the format file is a nice idea .. wish i could think that far ..

Amit.
0
 
amitpagarwalCommented:
i would suggest that after exporting the data (with quotes) into a flat file, you could manipulate the text file to remove the quotes or substitute them with some another character.

if u have sed, just say
cat BCPFILE | sed 's/\"//g' > NEWFILE

or open the file in vi and goto escape mode, say 1,$ s/\"//g

Else u can even create a view on that table where the data in the view is without quotes and u could actually bcp out that view.

Thanks.

0
 
sduckettAuthor Commented:
amitpagarwal, thanks for your comments.
I am really looking to find out if bcp can be instructed to ignore the quotes.
The file (flat text) I must import, on a regular basis, is ENORMOUS (>15Gb, containing every address in the UK) so I would like to avoid any pre or post processing.
I was wondering if there is anything clever that can be done with the bcp format file (or some other method) to manage this common file format("character data","more data",0.59,"data").
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
amitpagarwalCommented:
oh the file is really huge...
i can't think of anything apart from post formatting using sed ..

anyway, thanks.
0
 
sduckettAuthor Commented:
No, Thank YOU
0
 
sduckettAuthor Commented:
I found the following example:
It is exactly what I was looking, so I have effectively answered my own question. I am posting the solution here for future reference.

amit, Does a 'B' grade sound fair?

For the data file:

"XX",20,"CC"
"TT",30,"RD"

When the second column is type integer, the remaining columns have a character data type, and the data is in quotation marks for the character data type. Use the following BCP-format file:

4.2
7
1     SYBCHAR       0       0       "\""     0      dummy1
2     SYBCHAR       0       2       "\""     1      col1
3     SYBCHAR       0       0       ","      0      dummy2
4     SYBCHAR       0       2       ","      2      col2
5     SYBCHAR       0       0       "\""     0      dummy3
6     SYBCHAR       0       2       "\""     3      col3
7     SYBCHAR       0       0       "\r\n"   0      dummy4

0
 
viperguynazCommented:
This helped me solve a problem...but here's an easier solution...

8.0
3
1     SQLCHAR       0       2       ","      1      col1 RAW
2     SYBCHAR       0       2       ","      2      col2 RAW
3     SQLCHAR       0       0       "\r\n"   3      col3 SQL_Latin1_General_CP1_CI_AS
0
All Courses

From novice to tech pro — start learning today.