Solved

BCP data file with quotes.

Posted on 2000-04-11
15
2,365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Want an individual results display div 8 43
Upgrading to SQL Server 2015 Express 2 32
SQL Syntax 6 41
SQL Server Compression Decision 5 44
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

749 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