Solved

BCP data file with quotes.

Posted on 2000-04-11
15
2,317 Views
Last Modified: 2008-02-01
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
Comment
Question by:yunginv
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 2

Expert Comment

by:JHausmann
ID: 2705921
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
 
LVL 2

Expert Comment

by:JHausmann
ID: 2705922
grr, typo

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

Expert Comment

by:crsankar
ID: 2706948
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
 

Author Comment

by:yunginv
ID: 2707983
Adjusted points from 200 to 250
0
 

Author Comment

by:yunginv
ID: 2707984
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
 
LVL 7

Expert Comment

by:simonsabin
ID: 2708107
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
 
LVL 7

Expert Comment

by:simonsabin
ID: 2708502
The other way is to use DTS, which will cope with the qutoes.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:yunginv
ID: 2709115
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
 
LVL 7

Expert Comment

by:simonsabin
ID: 2711058
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
 
LVL 32

Expert Comment

by:bhess1
ID: 2713456
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
 

Author Comment

by:yunginv
ID: 2713841
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
 
LVL 7

Accepted Solution

by:
simonsabin earned 250 total points
ID: 2715082
click on Accept comment as answer on right hand side of one of my comments
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2715107
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
 

Author Comment

by:yunginv
ID: 2716366
Thanks for the imput
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2716484
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 43
SQL JOIN 6 39
Update in Sql 7 12
Authentication error 1 0
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now