build sql script: tab

Showing posts with label tab. Show all posts

Sunday, March 25, 2012

BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dkJesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.

> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
Jesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.

> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dkJesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.
> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thursday, March 22, 2012

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...

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...

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,

Any links to documentation or examples would be welcome!

Thanks a lot

Detlef

Thursday, March 8, 2012

BULK INSERT FIELD TERMINATOR

I need to be able to insert data from a file that may be tab delimited or delimited with spaces. Right now I am using fieldterminator = '\t' but I also need to account if there are just spaces there. Can this be done?any bites?|||

I don't believe that you will be able to use multiple terminitors.

The way that I have dealth with issues like this is to create a 'data washing' step prior to the bulk load. If you are using a JOB, DTS, or SSIS, create a step that runs a small batch file that pre-processes the data, changing whatever characters to what other characters are needed, and then move on to the bulk load step.

Wednesday, March 7, 2012

BULK INSERT /BCP with UDT

Dear all,

I tried to do BULK INSERT / BCP of a table with a UDT column there.

The data file looks like this: (Tab, \n, second column is the UDT column)

9556987 C,C,0.84 4974588
9845355 C,C,0.84 4974588
9556988 A,C,0.82 4974589
9845356 A,C,0.82 4974589
9556989 A,T,0.90 4974590

This is the SQL:

BULK INSERT dbo.TEST
FROM 'E:\My Documents\Visual Studio 2005\Projects\Genotype\Genotype_UDT\BULK_in1.txt'
WITH
(
KEEPIDENTITY,
KEEPNULLS,
ROWS_PER_BATCH = 5,
TABLOCK
)

This is the error msg:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GENOTYPE).

My UDT works fine if I do this:

INSERT INTO TEST VALUES('A,A,0.1101', 'Whatever')

Or INSERT INTO SELECT ...

So what's the problem?

Does SQL CLR engine call UDT Parse(...) for BULK INSERT? If not, must we provide the UDT value in binary like 0x11222ccffdedd, not a string?It has been too long nobody answer this question, so bring it up again. Also turn the question in a simple way:

How to do BULK load for UDT?

Thanks everybody!|||

Finally got the solution, and I spend some time to blog it here:

http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx

Comments please, and your view on UDT or CLR in general.

dong

|||I have same problem and i am getting nuts about it.

Target table:
CREATE TABLE [dbo].[organiz] (
[cislo_subjektu] [int] NULL ,
[reference_subjektu] [varchar] (30) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_subjektu] [varchar] (100) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_zkraceny] [varchar] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ulice] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[psc] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[misto] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ico] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[dic] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[uverovy_limit] [money] NULL ,
[stav_limitu] [money] NULL
) ON [PRIMARY]
GO

Insert command:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\pok.xml')

Formatfile:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="cislo_subjektu" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="reference_subjektu" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="3" NAME="nazev_subjektu" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="4" NAME="nazev_zkraceny" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="5" NAME="ulice" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="6" NAME="psc" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="7" NAME="misto" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="8" NAME="ico" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="9" NAME="dic" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="10" NAME="uverovy_limit" xsi:type="SQLBINARY"/>
<COLUMN SOURCE="11" NAME="stav_limitu" xsi:type="SQLBINARY"/>
</ROW>
</BCPFORMAT>

All i get is

Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

no matter what i have changed(so far) in format file.
|||

Hi, Pkv

I'm not sure your problem is the same as mine. What I asked and later found the solution is about the UDT bulk load, and the fundamental issue is UDT's Parse(String) method won't be called during bulk op.

By looking at your data, I didn't see any UDT in your table. So I reckon your problem could lie somewhere between the data file, format file and codepage.

If you can show a raw data file segment, and the real error msg, I'll try to replicate on my machine.

|||Here is the sample of the file:

<?xml version="1.0" encoding="windows-1250"?>
<root>
<organizace>
<cislo_subjektu>65673</cislo_subjektu>
<reference_subjektu>0000002135</reference_subjektu>
<nazev_subjektu>Martin Prudky</nazev_subjektu>
<nazev_zkraceny>PRUDKY LOUKA U LITV.</nazev_zkraceny>
<ulice>U nádra?í</ulice>
<psc>435 33</psc>
<misto>Louka u Litvínova</misto>
<ico>15137970</ico>
<dic>CZ6011151003</dic>
<uverovy_limit>200000</uverovy_limit>
<stav_limitu>172081.0000</stav_limitu>
</organizace>
<organizace>
<cislo_subjektu>67125</cislo_subjektu>
<reference_subjektu>0000003301</reference_subjektu>
<nazev_subjektu>Lesní dru?stvo obcí Ledec nad Sázavou</nazev_subjektu>
<nazev_zkraceny>LESNI DRUZSTVO LEDEC</nazev_zkraceny>
<ulice>Hnevkovice 56</ulice>
<psc>582 94</psc>
<misto>Ledec nad Sázavou</misto>
<ico>25251937</ico>
<dic>CZ25251937</dic>
<uverovy_limit>50000</uverovy_limit>
<stav_limitu>0.0000</stav_limitu>
</organizace>
<organizace>
<cislo_subjektu>67159</cislo_subjektu>
<reference_subjektu>0000004170</reference_subjektu>
<nazev_subjektu>D.D.L.Servis, s.r.o.</nazev_subjektu>
<nazev_zkraceny>D.D.L. SERVIS LOMNICE</nazev_zkraceny>
<ulice>?elechy 37</ulice>
<psc>512 51</psc>
<misto>Lomnice nad Popelkou</misto>
<ico>25291521</ico>
<dic>CZ25291521</dic>
<uverovy_limit>50000</uverovy_limit>
<stav_limitu>0.0000</stav_limitu>
</organizace>
.
.
.

It goes for next 30 000 table rows.

Here is list of errors:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 1 (cislo_subjektu).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 1 (cislo_subjektu).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

|||

Hi, pkv,

Sorry for the late reply. After looking at your data, I'm now sure your problem isn't same as mine.

Your task is to BULK INSERT XML data file into a relational table. Also called 'shredding'.

There are 3 ways to do this: (from the MCTS trainning kit book)

1, Use OPENXML and the XML stored procedures

2, Use the XML data type's node() method

3, Use the SQLXML API.

Here are my code for method number 2:

DECLARE @.X XML

SELECT @.X = X.C

FROM OPENROWSET(BULK

'E:\My Documents\SQL Server Management Studio\Projects\pkv_problem\organizace.xml',

SINGLE_BLOB) AS X(C)

INSERT INTO dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

As you can see, this solution has nothing to do with a format file here.

(The SELECT clause in the INSERT INTO statement can be replaced by this one:

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('* Devil ', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('* Music ', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

should give you same result.)

I guess you may have a misconception here, for bcp.exe or BULK INSERT, a format file can be in two formats: a text one or a XML one. But both only work for a flat data file. It seems you are binding together a XML data file with a XML format file, hoping to shred data from XML into relational, no, that won't work.

Regards,

dong

|||Hi Dong.

Thank you for helping me.

Pkv

BULK INSERT /BCP with UDT

Dear all,

I tried to do BULK INSERT / BCP of a table with a UDT column there.

The data file looks like this: (Tab, \n, second column is the UDT column)

9556987 C,C,0.84 4974588
9845355 C,C,0.84 4974588
9556988 A,C,0.82 4974589
9845356 A,C,0.82 4974589
9556989 A,T,0.90 4974590

This is the SQL:

BULK INSERT dbo.TEST
FROM 'E:\My Documents\Visual Studio 2005\Projects\Genotype\Genotype_UDT\BULK_in1.txt'
WITH
(
KEEPIDENTITY,
KEEPNULLS,
ROWS_PER_BATCH = 5,
TABLOCK
)

This is the error msg:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GENOTYPE).

My UDT works fine if I do this:

INSERT INTO TEST VALUES('A,A,0.1101', 'Whatever')

Or INSERT INTO SELECT ...

So what's the problem?

How to do BULK load for UDT?

Thanks everybody!|||

Finally got the solution, and I spend some time to blog it here:

http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx

Comments please, and your view on UDT or CLR in general.

dong

|||I have same problem and i am getting nuts about it.

Insert command:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\pok.xml')

Formatfile:

All i get is

Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

no matter what i have changed(so far) in format file.|||

Hi, Pkv

By looking at your data, I didn't see any UDT in your table. So I reckon your problem could lie somewhere between the data file, format file and codepage.

If you can show a raw data file segment, and the real error msg, I'll try to replicate on my machine.

|||Here is the sample of the file:

It goes for next 30 000 table rows.

Hi, pkv,

Sorry for the late reply. After looking at your data, I'm now sure your problem isn't same as mine.

Your task is to BULK INSERT XML data file into a relational table. Also called 'shredding'.

There are 3 ways to do this: (from the MCTS trainning kit book)

1, Use OPENXML and the XML stored procedures

2, Use the XML data type's node() method

3, Use the SQLXML API.

Here are my code for method number 2:

DECLARE @.X XML

SELECT @.X = X.C

FROM OPENROWSET(BULK

'E:\My Documents\SQL Server Management Studio\Projects\pkv_problem\organizace.xml',

SINGLE_BLOB) AS X(C)

INSERT INTO dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

As you can see, this solution has nothing to do with a format file here.

(The SELECT clause in the INSERT INTO statement can be replaced by this one:

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('* Devil ', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('* Music ', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

should give you same result.)

Regards,

dong

|||Hi Dong.

Thank you for helping me.

Pkv

BULK INSERT /BCP with UDT

Dear all,

I tried to do BULK INSERT / BCP of a table with a UDT column there.

The data file looks like this: (Tab, \n, second column is the UDT column)

9556987 C,C,0.84 4974588
9845355 C,C,0.84 4974588
9556988 A,C,0.82 4974589
9845356 A,C,0.82 4974589
9556989 A,T,0.90 4974590

This is the SQL:

BULK INSERT dbo.TEST
FROM 'E:\My Documents\Visual Studio 2005\Projects\Genotype\Genotype_UDT\BULK_in1.txt'
WITH
(
KEEPIDENTITY,
KEEPNULLS,
ROWS_PER_BATCH = 5,
TABLOCK
)

This is the error msg:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GENOTYPE).

My UDT works fine if I do this:

INSERT INTO TEST VALUES('A,A,0.1101', 'Whatever')

Or INSERT INTO SELECT ...

So what's the problem?

How to do BULK load for UDT?

Thanks everybody!|||

Finally got the solution, and I spend some time to blog it here:

http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx

Comments please, and your view on UDT or CLR in general.

dong

|||I have same problem and i am getting nuts about it.

Insert command:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\pok.xml')

Formatfile:

All i get is

Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

no matter what i have changed(so far) in format file.
|||

Hi, Pkv

By looking at your data, I didn't see any UDT in your table. So I reckon your problem could lie somewhere between the data file, format file and codepage.

If you can show a raw data file segment, and the real error msg, I'll try to replicate on my machine.

|||Here is the sample of the file:

It goes for next 30 000 table rows.

|||

Hi, pkv,

Sorry for the late reply. After looking at your data, I'm now sure your problem isn't same as mine.

Your task is to BULK INSERT XML data file into a relational table. Also called 'shredding'.

There are 3 ways to do this: (from the MCTS trainning kit book)

1, Use OPENXML and the XML stored procedures

2, Use the XML data type's node() method

3, Use the SQLXML API.

Here are my code for method number 2:

DECLARE @.X XML

SELECT @.X = X.C

FROMOPENROWSET(BULK

'E:\My Documents\SQL Server Management Studio\Projects\pkv_problem\organizace.xml',

SINGLE_BLOB)AS X(C)

INSERTINTO dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]','int')AS'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]','varchar(30)')AS'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]','varchar(100)')AS'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]','varchar(40)')AS'nazev_zkraceny'

,C.value('(./ulice/text())[1]','char(40)')AS'ulice'

,C.value('(./psc/text())[1]','char(15)')AS'psc'

,C.value('(./misto/text())[1]','char(40)')AS'misto'

,C.value('(./ico/text())[1]','char(15)')AS'ico'

,C.value('(./dic/text())[1]','char(15)')AS'dic'

,C.value('(./uverovy_limit/text())[1]','money')AS'uverovy_limit'

,C.value('(./stav_limitu/text())[1]','money')AS'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

As you can see, this solution has nothing to do with a format file here.

(The SELECT clause in the INSERT INTO statement can be replaced by this one:

SELECT

C.value('*[1]','int')AS'cislo_subjektu'

,C.value('*[2]','varchar(30)')AS'reference_subjektu'

,C.value('*[3]','varchar(100)')AS'nazev_subjektu'

,C.value('*[4]','varchar(40)')AS'nazev_zkraceny'

,C.value('*[5]','char(40)')AS'ulice'

,C.value('* Devil ','char(15)')AS'psc'

,C.value('*[7]','char(40)')AS'misto'

,C.value('* Music ','char(15)')AS'ico'

,C.value('*[9]','char(15)')AS'dic'

,C.value('*[10]','money')AS'uverovy_limit'

,C.value('*[11]','money')AS'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

should give you same result.)

Regards,

dong

|||Hi Dong.

Thank you for helping me.

Pkv

BULK INSERT /BCP with UDT

Dear all,

I tried to do BULK INSERT / BCP of a table with a UDT column there.

The data file looks like this: (Tab, \n, second column is the UDT column)

9556987 C,C,0.84 4974588
9845355 C,C,0.84 4974588
9556988 A,C,0.82 4974589
9845356 A,C,0.82 4974589
9556989 A,T,0.90 4974590

This is the SQL:

BULK INSERT dbo.TEST
FROM 'E:\My Documents\Visual Studio 2005\Projects\Genotype\Genotype_UDT\BULK_in1.txt'
WITH
(
KEEPIDENTITY,
KEEPNULLS,
ROWS_PER_BATCH = 5,
TABLOCK
)

This is the error msg:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GENOTYPE).

My UDT works fine if I do this:

INSERT INTO TEST VALUES('A,A,0.1101', 'Whatever')

Or INSERT INTO SELECT ...

So what's the problem?

How to do BULK load for UDT?

Thanks everybody!|||

Finally got the solution, and I spend some time to blog it here:

http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx

Comments please, and your view on UDT or CLR in general.

dong

|||I have same problem and i am getting nuts about it.

Insert command:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\pok.xml')

Formatfile:

All i get is

Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

no matter what i have changed(so far) in format file.|||

Hi, Pkv

By looking at your data, I didn't see any UDT in your table. So I reckon your problem could lie somewhere between the data file, format file and codepage.

If you can show a raw data file segment, and the real error msg, I'll try to replicate on my machine.

|||Here is the sample of the file:

It goes for next 30 000 table rows.

Hi, pkv,

Sorry for the late reply. After looking at your data, I'm now sure your problem isn't same as mine.

Your task is to BULK INSERT XML data file into a relational table. Also called 'shredding'.

There are 3 ways to do this: (from the MCTS trainning kit book)

1, Use OPENXML and the XML stored procedures

2, Use the XML data type's node() method

3, Use the SQLXML API.

Here are my code for method number 2:

DECLARE @.X XML

SELECT @.X = X.C

FROM OPENROWSET(BULK

'E:\My Documents\SQL Server Management Studio\Projects\pkv_problem\organizace.xml',

SINGLE_BLOB) AS X(C)

INSERT INTO dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

As you can see, this solution has nothing to do with a format file here.

(The SELECT clause in the INSERT INTO statement can be replaced by this one:

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('* Devil ', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('* Music ', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

should give you same result.)

Regards,

dong

|||Hi Dong.

Thank you for helping me.

Pkv

BULK INSERT /BCP with UDT

Dear all,

I tried to do BULK INSERT / BCP of a table with a UDT column there.

The data file looks like this: (Tab, \n, second column is the UDT column)

9556987 C,C,0.84 4974588
9845355 C,C,0.84 4974588
9556988 A,C,0.82 4974589
9845356 A,C,0.82 4974589
9556989 A,T,0.90 4974590

This is the SQL:

BULK INSERT dbo.TEST
FROM 'E:\My Documents\Visual Studio 2005\Projects\Genotype\Genotype_UDT\BULK_in1.txt'
WITH
(
KEEPIDENTITY,
KEEPNULLS,
ROWS_PER_BATCH = 5,
TABLOCK
)

This is the error msg:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GENOTYPE).

My UDT works fine if I do this:

INSERT INTO TEST VALUES('A,A,0.1101', 'Whatever')

Or INSERT INTO SELECT ...

So what's the problem?

How to do BULK load for UDT?

Thanks everybody!|||

Finally got the solution, and I spend some time to blog it here:

http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx

Comments please, and your view on UDT or CLR in general.

dong

|||I have same problem and i am getting nuts about it.

Insert command:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\pok.xml')

Formatfile:

All i get is

Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

no matter what i have changed(so far) in format file.
|||

Hi, Pkv

By looking at your data, I didn't see any UDT in your table. So I reckon your problem could lie somewhere between the data file, format file and codepage.

If you can show a raw data file segment, and the real error msg, I'll try to replicate on my machine.

|||Here is the sample of the file:

It goes for next 30 000 table rows.

|||

Hi, pkv,

Sorry for the late reply. After looking at your data, I'm now sure your problem isn't same as mine.

Your task is to BULK INSERT XML data file into a relational table. Also called 'shredding'.

There are 3 ways to do this: (from the MCTS trainning kit book)

1, Use OPENXML and the XML stored procedures

2, Use the XML data type's node() method

3, Use the SQLXML API.

Here are my code for method number 2:

DECLARE @.X XML

SELECT @.X = X.C

FROM OPENROWSET(BULK

'E:\My Documents\SQL Server Management Studio\Projects\pkv_problem\organizace.xml',

SINGLE_BLOB) AS X(C)

INSERT INTO dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

As you can see, this solution has nothing to do with a format file here.

(The SELECT clause in the INSERT INTO statement can be replaced by this one:

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('* Devil ', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('* Music ', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

should give you same result.)

Regards,

dong

|||Hi Dong.

Thank you for helping me.

Pkv

Saturday, February 25, 2012

BULK INSERT /BCP with UDT

Dear all,

I tried to do BULK INSERT / BCP of a table with a UDT column there.

The data file looks like this: (Tab, \n, second column is the UDT column)

9556987 C,C,0.84 4974588
9845355 C,C,0.84 4974588
9556988 A,C,0.82 4974589
9845356 A,C,0.82 4974589
9556989 A,T,0.90 4974590

This is the SQL:

BULK INSERT dbo.TEST
FROM 'E:\My Documents\Visual Studio 2005\Projects\Genotype\Genotype_UDT\BULK_in1.txt'
WITH
(
KEEPIDENTITY,
KEEPNULLS,
ROWS_PER_BATCH = 5,
TABLOCK
)

This is the error msg:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GENOTYPE).

My UDT works fine if I do this:

INSERT INTO TEST VALUES('A,A,0.1101', 'Whatever')

Or INSERT INTO SELECT ...

So what's the problem?

How to do BULK load for UDT?

Thanks everybody!|||

Finally got the solution, and I spend some time to blog it here:

http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx

Comments please, and your view on UDT or CLR in general.

dong

|||I have same problem and i am getting nuts about it.

Insert command:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\pok.xml')

Formatfile:

All i get is

Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

no matter what i have changed(so far) in format file.|||

Hi, Pkv

By looking at your data, I didn't see any UDT in your table. So I reckon your problem could lie somewhere between the data file, format file and codepage.

If you can show a raw data file segment, and the real error msg, I'll try to replicate on my machine.

|||Here is the sample of the file:

It goes for next 30 000 table rows.

Hi, pkv,

Sorry for the late reply. After looking at your data, I'm now sure your problem isn't same as mine.

Your task is to BULK INSERT XML data file into a relational table. Also called 'shredding'.

There are 3 ways to do this: (from the MCTS trainning kit book)

1, Use OPENXML and the XML stored procedures

2, Use the XML data type's node() method

3, Use the SQLXML API.

Here are my code for method number 2:

DECLARE @.X XML

SELECT @.X = X.C

FROM OPENROWSET(BULK

'E:\My Documents\SQL Server Management Studio\Projects\pkv_problem\organizace.xml',

SINGLE_BLOB) AS X(C)

INSERT INTO dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

As you can see, this solution has nothing to do with a format file here.

(The SELECT clause in the INSERT INTO statement can be replaced by this one:

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('* Devil ', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('* Music ', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

should give you same result.)

Regards,

dong

|||Hi Dong.

Thank you for helping me.

Pkv

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> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
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..........
>

Sunday, March 25, 2012

BULK INSERT with leading/trailing quotes

BULK INSERT with leading/trailing quotes

BULK INSERT with leading/trailing quotes

BULK INSERT with leading/trailing quotes

Thursday, March 22, 2012

Bulk Insert remove extra quotes

Bulk Insert remove extra quotes

Thursday, March 8, 2012

BULK INSERT FIELD TERMINATOR

Wednesday, March 7, 2012

BULK INSERT /BCP with UDT

BULK INSERT /BCP with UDT

BULK INSERT /BCP with UDT

BULK INSERT /BCP with UDT

BULK INSERT /BCP with UDT

Saturday, February 25, 2012

BULK INSERT /BCP with UDT

BULK INSERT - Urgent

build sql script

Blog Archive

About Me