Trying to Bulk Insert "|" Delimited txt File

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

SQL Server 2008 Forums

Transact-SQL (2008)

Trying to Bulk Insert "|" Delimited txt File

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2010-05-06 : 10:16:27

I am using SQL Server 2008 Express Edition

I have a txt file I am trying to Bulk Insert into an existing Table and I can't seem to get it to work. Below is the code I am using and a few lines from the txt File.


BULK INSERT Dictionaries.dbo.Rejections_1
   FROM 'C:\Dictionaries\Rejections.txt'
   WITH 
      (
        
        FIELDTERMINATOR = '|', 
        ROWTERMINATOR = '\n' 

         )

"1"|"DEDUCTIBLE AMOUNT"|"PT RESPONSIBILITY/OTHER INS"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"1"|"10/11/2006"|"05:42PM"|"ITJLT01"|"10/11/2006"|"05:42PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"2"|"COINSURANCE AMOUNT"|"PT RESPONSIBILITY/OTHER INS"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"2"|"10/11/2006"|"05:42PM"|"ITJLT01"|"10/11/2006"|"05:42PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"3"|"CO-PAYMENT AMOUNT"|"PT RESPONSIBILITY/OTHER INS"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"3"|"10/11/2006"|"05:42PM"|"ITJLT01"|"10/11/2006"|"05:42PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"4"|"THE PROCEDURE CODE IS INCONSIST"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"4"|"10/11/2006"|"05:19PM"|"ITJLT01"|"10/11/2006"|"05:19PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"5"|"THE PROCEDURE CODE/BILL TYPE IS"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"5"|"10/11/2006"|"05:19PM"|"ITJLT01"|"10/11/2006"|"05:19PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"6"|"THE PROCEDURE/REVENUE CODE IS I"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"6"|"10/11/2006"|"05:19PM"|"ITJLT01"|"10/11/2006"|"05:19PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"7"|"THE PROCEDURE/REVENUE CODE IS I"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"7"|"10/11/2006"|"05:19PM"|"ITJLT01"|"10/11/2006"|"05:19PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"8"|"THE PROCEDURE CODE IS INCONSIST"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"8"|"10/11/2006"|"05:19PM"|"ITJLT01"|"10/11/2006"|"05:19PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"9"|"THE DIAGNOSIS IS INCONSISTENT W"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"9"|"10/11/2006"|"05:19PM"|"ITJLT01"|"10/11/2006"|"05:19PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"10"|"THE DIAGNOSIS IS INCONSISTENT W"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"10"|"10/11/2006"|"05:19PM"|"ITJLT01"|"10/11/2006"|"05:19PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "
"11"|"THE DIAGNOSIS IS INCONSISTENT W"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"11"|"10/11/2006"|"05:19PM"|"ITJLT01"|"03/19/2007"|"11:39AM"|"DALJAM01"|" "|" "|" "|" "|" "|" "
"12"|"THE DIAGNOSIS IS INCONSISTENT W"|"CLAIM APPEAL"|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|" "|"1"|"12"|"10/11/2006"|"05:19PM"|"ITJLT01"|"10/11/2006"|"05:19PM"|"ITJLT01"|" "|" "|" "|" "|" "|" "

Here is the error I am getting:

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 8 (DeactivationDate).

Brian

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-06 : 10:20:48

my guess is that it doesn't like the quote identifiers...

lose the quotes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-06 : 10:21:45

plus it looks like the last qute is malformed anyway

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-06 : 10:40:31

Can you post the CREATE TABLE script for Dictionaries.dbo.Rejections_1

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

bconner
Starting Member

48 Posts

Posted - 2010-05-06 : 10:43:45

Here is the Make Table for Rejections_1

CREATE TABLE [dbo].[Rejections_1](
[CODE] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[Type] [nvarchar](255) NULL,
[REPORTING_CATEGORY_1] [nvarchar](255) NULL,
[REPORTING_CATEGORY_2] [nvarchar](255) NULL,
[REPORTING_CATEGORY_3] [nvarchar](255) NULL,
[DeactivationFlag] [nvarchar](255) NULL,
[DeactivationDate] [datetime] NULL,
[SYSTEM_EFFECTIVE_DATE] [datetime] NULL,
[SystemExpirationDate] [datetime] NULL,
[ETM_TASK_NAME] [nvarchar](255) NULL,
[ETM_ASSIGN_TO_USER] [nvarchar](255) NULL,
[ETM_ASSIGN_TO_ROLE] [nvarchar](255) NULL,
[ETM_TASK_PRIORITY] [nvarchar](255) NULL,
[GRP_VARIANT_BREAKOUT] [nvarchar](255) NULL,
[FSC_VARIANT_BREAKOUT] [nvarchar](255) NULL,
[X12CodeSource] [nvarchar](255) NULL,
[X12Code] [nvarchar](255) NULL,
[CreDt] [datetime] NULL,
[CREATION_TIME] [nvarchar](255) NULL,
[CREATION_USERNAME] [nvarchar](255) NULL,
[UPDATE_DATE] [datetime] NULL,
[UPDATE_TIME] [datetime] NULL,
[UPDATE_USERNAME] [nvarchar](255) NULL,
[DELETED_FLAG] [nvarchar](255) NULL,
[BI_DENIALS] [nvarchar](255) NULL,
[INCLUDE_ON_STATEMENT] [nvarchar](255) NULL,
[STATEMENT_MESSAGE] [nvarchar](255) NULL,
[PAYER_CONTRACT_REJECTION] [nvarchar](255) NULL,
[COMMENTS] [nvarchar](255) NULL
) ON [PRIMARY]

Brian

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-06 : 10:58:38

DeactivationDate is a DATETIME datatype, and you're trying to insert a space character into it. You can't convert a space character into a DATETIME datatype.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

bconner
Starting Member

48 Posts

Posted - 2010-05-06 : 11:28:08

I removed the Quotes and it worked! Thanks everyone for taking the time to respond to me I appreciate it.... This site has been a life saver for me

Brian

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-06 : 13:05:49

you're welcome....funny how all other M$ Productscan accept quote identifiers, but BULK INSERT and bcp can't

What created your data in the first place??? EXCEL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bconner
Starting Member

48 Posts

Posted - 2010-05-07 : 08:13:14

No, data came from Mumps Database (Flowcast 3.0)

Brian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 09:09:57

quote:
Originally posted by DBA in the making

DeactivationDate is a DATETIME datatype, and you're trying to insert a space character into it. You can't convert a space character into a DATETIME datatype.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

A sapce will be defaulted to 1900-01-01 00:00:00.000

select cast(' ' as datetime)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources