Bulk Insert remove extra quotes

I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

Example 1: """"This is a just a test."""

Results should be "This is just a test"

Example 2: """This is only a test"" and nothing more"

Results should be:"This is only a test" and nothing more.

Now.

If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?

Below is my syntax

SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'

Thanks
Conrad...

There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.|||

Thanks for the information.

When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.

Keep in mind, that the quotes may not appeared in all of the fields.

Thanks

Conrad...

|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||

Umachandar Jayachandran - MS wrote:

As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

Hello,

I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

Bulk Insert remove extra quotes

I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.

Example 1: """"This is a just a test."""

Results should be "This is just a test"

Example 2: """This is only a test"" and nothing more"

Results should be:"This is only a test" and nothing more.

Now.

If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.

Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?

Below is my syntax

SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'

Thanks
Conrad...

There is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.|||

Thanks for the information.

When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.

On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.

Keep in mind, that the quotes may not appeared in all of the fields.

Thanks

Conrad...

|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||

Umachandar Jayachandran - MS wrote:

As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.

Hello,

I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

BULK INSERT - Urgent

I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}
When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records
How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = "\t",
ROWTERMINATOR = "\n"
)
Pls help..........This is a multi-part message in MIME format.
--020900050805040007080207
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
One thing I noticed is that you use "\r\n" as your row terminator in
your DTS package but only "\n" in your BULK INSERT statement. Perhaps
every row in your input file doesn't have a "\n" to terminate but only a
"\r" (from memory, although it's been a while so I could be wrong,
typically text files generated with Unix/Linux tools only use a "\r"
(ASCII 13) to terminate a line). I don't know why \r\n would work in
the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT statement
(like "\n", "\r\n", "\r").
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Sanjay wrote:
>I have a tab delimeted file which i BCP into a temp table using BULK INSERT
>When i use DTS Transform data task i get all 7000 records in the table
>In DTS data transform i choose Column Delimeter is a tab ann row delimeter
>is {CR}{LF}
>When i do Bulk insert in using command below a lot of these records are
>combined with previous records dud to improper row terminators on some records
>How do i make this work in BULK INSERT same way as it works in DTS
>BULK INSERT #stage_obligor_exposure
> FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
> WITH
> (
> FIRSTROW = 2,
> FIELDTERMINATOR = "\t",
> ROWTERMINATOR = "\n"
> )
>Pls help..........
>
>
--020900050805040007080207
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>One thing I noticed is that you use "\r\n" as your row terminator
in your DTS package but only "\n" in your BULK INSERT statement.Â
Perhaps every row in your input file doesn't have a "\n" to terminate
but only a "\r" (from memory, although it's been a while so I could be
wrong, typically text files generated with Unix/Linux tools only use a
"\r" (ASCII 13) to terminate a line). I don't know why \r\n would work
in the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT
statement (like "\n", "\r\n", "\r").</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Sanjay wrote:
<blockquote cite="midBC983130-5E18-4E77-92BB-0FFC20421FAA@.microsoft.com"
type="cite">
<pre wrap="">I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}
When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records
How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = "\t",
ROWTERMINATOR = "\n"
)
Pls help..........
</pre>
</blockquote>
</body>
</html>
--020900050805040007080207--|||I have gotten this bulk insert statement to work and all you would have to do
is change the fieldterminator to '\t'. Notice the file I was working with
was CSV and it had "," for field termination. You can try using a FORMAT
file parameter but so far I have been unsuccessful in getting that to work.
You can make a format file from the table using the following code:
EXEC master.dbo.xp_cmdshell 'BCP sqlsrv.dbo.SystemTemp FORMAT -Usa
-Ppassword -N -fc:\TBL_Format.fmt'
Which will make you a format file of the table you are trying to bulk import
into.
---
BULK INSERT [dbo].[SystemTemp]
FROM 'c:\SystemTempIn.csv'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '","',
FIRSTROW = 2,
ROWTERMINATOR = '"\n'
)
"Sanjay" wrote:
> I have a tab delimeted file which i BCP into a temp table using BULK INSERT
> When i use DTS Transform data task i get all 7000 records in the table
> In DTS data transform i choose Column Delimeter is a tab ann row delimeter
> is {CR}{LF}
> When i do Bulk insert in using command below a lot of these records are
> combined with previous records dud to improper row terminators on some records
> How do i make this work in BULK INSERT same way as it works in DTS
> BULK INSERT #stage_obligor_exposure
> FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
> WITH
> (
> FIRSTROW = 2,
> FIELDTERMINATOR = "\t",
> ROWTERMINATOR = "\n"
> )
> Pls help..........
>