• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2499
  • Last Modified:

BCP data file with quotes.

I'm trying to BCP a data file into a SQL database table.  The file goes in okay but all of the character fields or stings have quotes around them.  Is there a way to take away the quotes?

The data file I am BCP in is a comma delimited file with quotes around all string variables but NOT around other types of variables.

Example:  "CustomerName", 12.20 "Main St"

I thought I could get around this in the format file but haven't been able to figure it out.

Format file is as below
1  SQLCHAR  0  255  ","  1  CustId
2-89 same as above.
90  SQLCHAR  0  8  "\r\n"  90  UnearnedYTD

Any help would be greatly appreciated.
0
yunginv
Asked:
yunginv
  • 5
  • 5
  • 2
  • +2
1 Solution
 
JHausmannCommented:
I'd give some thought to pre-processing the file. For example, VB 6 has the replace command that you could use to remove all instances of any value:

replace(string,chr$34),"",1,-1) will remove any ocurrence of double-quotes in the source string.


0
 
JHausmannCommented:
grr, typo

replace(string,chr$(34),"",1,-1) 'will remove any ocurrence of double-quotes in the source string.
0
 
crsankarCommented:
Well, I think the easiest thing to do would be to remove the "" from the data after importing the data into sql server

you can use the replace function in sql server for this purpose
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
yunginvAuthor Commented:
Adjusted points from 200 to 250
0
 
yunginvAuthor Commented:
I know I can remove the quotes once inside SQL but I was hoping that there was a way to do this as it goes into SQL.  

I believe the way the data is comming in is a standard data format.  I know programs such as Excel will remove the quotes so again I was hoping SQL would do the same.

VB isn't really an option either.  I would remove the quotes using SQL before VB.

Thanks everyone for your help and comments.  I will raise the points and see if anyone else has any ideas.  

If it can't be done then it just can't be done.

0
 
simonsabinCommented:
The best way would be to load the file into EXCEL or Access (or write a VB thing for it) which allow for quoted strings.
Then export using a unique delimeter i.e #~# this is unlikely to appear in your file.

Then BCP in the new file.

Using VB and ado you could use the flat file provider which copes with quoted strings and then export the data to file. (i.e do what excel and access would do for you.)
0
 
simonsabinCommented:
The other way is to use DTS, which will cope with the qutoes.
0
 
yunginvAuthor Commented:
DTS is what I'm playing with now and it appears to be working.  Is there any difference in speed between DTS & BCP.  I'm currently only bringing over a few test records so I don't see much of a difference.
0
 
simonsabinCommented:
Yes Bulk copy of DTS is the fastest, then BCP and then Data Pumps.

I can get you figures if you want.

For < 100 rows I wouldn't bother, the data pump does what is required and does take too long so use it.
0
 
Brendt HessSenior DBACommented:
Actually, MS says that bcp is fastest, followed by DTS.  However, I don't know that it's that large a difference.

Definitely use DTS if you have SQL 7.  The Text Delimiter option on the import is exactly what you need.
0
 
yunginvAuthor Commented:
I'm using DTS and it seems to be working fine.  The only thing I really don't like about it is that there is not much useful information reported in the error file.

Thanks for all the help - I'm not sure how to give the points away for posting a comment but if "simonsabin" wants them post an answer and they are yours

AGAIN THANKS FOR YOUR HELP (EVERYONE)
0
 
simonsabinCommented:
click on Accept comment as answer on right hand side of one of my comments
0
 
simonsabinCommented:
Bhess1

At the SQL pass conference, in London last month Euan Garden the Group Program Manager for SQL Server Tools, formally the Program Manager for DTS stated the order above. And who am I to disagree.
0
 
yunginvAuthor Commented:
Thanks for the imput
0
 
Brendt HessSenior DBACommented:
simonsabin

Interesting - I hadn't hear this.  I was just going off the BOL info comparing BCP and DTS, but if they've changed their opinion....

Thanks for the info.
0

Featured Post

The 14th Annual Expert Award Winners

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

  • 5
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now