BULK INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

BULK INSERT

Author

Topic

koptastic69
Starting Member

7 Posts

Posted - 2010-04-01 : 07:13:50

I'm trying to bulk insert the following CSV comma & " delimited file;

","""",""99999999"","""",""10-00-00"",""GBP"",""30/03/10"",""Close"",-3.24,"""","""","""","""","

into the following table def;

CREATE TABLE [dbo].[ClarityStatementHold](
[Group] [char](30) NULL,
[AccID] [char](30) NULL,
[AccountNo] [char](20) NULL,
[Type] [char](30) NULL,
[BankCode] [char](15) NULL,
[Curr] [char](20) NULL,
[Date] [date] NULL,
[AsAt] [char](10) NULL,
[Amount] [money] NULL,
[TLACode] [char](10) NULL,
[ChequeNo] [char](20) NULL,
[Status] [char](20) NULL,
[Description] [char](100) NULL

using the following format file;

<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="15" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR='",' MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR=',"' MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR='",\r\n' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Group" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="AccID" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="AccountNo" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="Type" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="BankCode" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="Curr" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="Date" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="8" NAME="AsAt" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="9" NAME="Amount" xsi:type="SQLMONEY"/>
<COLUMN SOURCE="10" NAME="TLACode" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="11" NAME="ChequeNo" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="12" NAME="Status" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="13" NAME="Description" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

When I run the import I am getting the following;

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 7 (Date).

Any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 07:30:25

In your table the column is of type DATE which exists since SQL Server 2008.
Maybe this is a DATETIME? <COLUMN SOURCE="7" NAME="Date" xsi:type="SQLDATETIME"/>

So change your column in destination table to DATETIME instead of DATE.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 07:31:48

seems like you've some spurious formatted data coming for date field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

koptastic69
Starting Member

7 Posts

Posted - 2010-04-01 : 08:28:27

quote:
Originally posted by webfred

In your table the column is of type DATE which exists since SQL Server 2008.
Maybe this is a DATETIME? <COLUMN SOURCE="7" NAME="Date" xsi:type="SQLDATETIME"/>

So change your column in destination table to DATETIME instead of DATE.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks for that. No, it doesn't seem to make any difference.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 09:02:31

Have a look in your datafile.
Is there a problem with the data for date?
How does it look like?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 09:05:11

Do you have a chance to change the process that generates the input?
I think the date as "30/03/10" isn't fine.
Better: "20100330"

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 09:18:25

set dateformat dmy
--Try your bulk insert

Madhivanan

Failing to plan is Planning to fail

koptastic69
Starting Member

7 Posts

Posted - 2010-04-01 : 09:50:08

Changed the date format to yyyymmdd. Still no joy :-(

koptastic69
Starting Member

7 Posts

Posted - 2010-04-01 : 10:40:44

Changed the format to ddmmyyyy. Still no joy.......

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 10:53:47

In your first post the sample record I see 14 fields?

No, you're never too old to Yak'n'Roll if you're too young to die.

koptastic69
Starting Member

7 Posts

Posted - 2010-04-01 : 11:11:26

Fair comment, but that shouldn't raise the error I'm getting should it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 11:52:41

is there any gaps in data in file?like any rows containing blank(NULL) value for any fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

koptastic69
Starting Member

7 Posts

Posted - 2010-04-01 : 11:59:47

No. No gaps in the data file. The example I've listed is typical of the data. The table allows NULL (if present)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources