Solved

BCP data file with quotes.

Posted on 2000-04-11
15
2,326 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

895 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