Bulk Insert for data with quotes

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 02:35:10 -0700

You have a couple of options

1. Format file (Examples on Google)
2. Clear the " after the event using REPLACE()

>-----Original Message-----
>Hi
>I am new to DTS, I use bulk insert to load a csv file
which has all its
>character (as opposed to numeric) data in double quotes.
But the bulk insert
>takes the data into the table along with the quotation
marks. I know you can
>do this easily in Transform Data Task, but for lot of
records I want to use
>bulk insert. How can I get bulk insert to recognise
double quotes?
>.
>

Relevant Pages

  • Cannot fetch a row from OLE DB provider "BULK" for linked server
    ... consistently receive during a stored proc Bulk Insert from a text file. ... Format file created using BCP ... BULK INSERT DataGL.dbo. ... I've checked the data file for anomalies. ...
    (microsoft.public.sqlserver.programming)
  • Re: Bulk Insert for data with quotes
    ... DTS has nothing to do with it. ... the BI Task is a wrapper around the BULK ... It does work if you use a format file. ... Allan Mitchell MCSE,MCDBA, ...
    (microsoft.public.sqlserver.dts)
  • Re: Bulk Insert
    ... and the error message says: ... Error: Bulk Insert fails. ... file with comma as field delimiter, and quotes are obviously not part ... A format file for the above would look ...
    (microsoft.public.sqlserver.tools)
  • Re: Bulk insert via SQL DTS
    ... from our own SQL server and have no line endings at all. ... BULK INSERT command from within a stored proc. ... Here's the format file I'm using: ... >> I'm trying to get a bulk insert job to run from within a DTS task. ...
    (microsoft.public.sqlserver.dts)
  • Re: Bulk Insert Question
    ... >>I need to move some data from an ascii file to a database. ... The problem I have is there is no field seperation in the ... Is there a way to use BULK INSERT without having to put ... > editor to check the contents of the format file and tweak it as needed. ...
    (microsoft.public.sqlserver.programming)