March 23, 2015 at 3:21 pm
I want to bulk insert data into a table named scd_event_tab inside a database named rdb.
when I do select * from rdb.dbo.scd_event_tab, i get :
JOB_ID RUN_ONPRIORITYPAYLOADTIMEOUT_INTERVALSTATUSPICKUP_TIMESCD_TYPESCHEDULE_IDDB_ADMIN_LOGIN_REQUIRED_YN
I saved the result into a csv file and then truncated the table. Now, I am trying to bulk insert the data into the table. So I used:
bulk insert
rdb.dbo.scd_event_tab from 'C:\users\sluintel.ctr\desktop\eventtab.csv'
with
(
codepage = 'RAW',
datafiletype = 'native',
fieldterminator = '\t',
keepidentity,
keepnulls
);
go
However, I get this error:
Msg 4867, Level 16, State 1, Line 1
Bulk load data conversion error (overflow) for row 1, column 1 (JOB_ID).
Msg 4866, Level 16, State 5, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 3. Verify that the field terminator and row terminator are specified correctly.
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)".
Can someone help me with this Please?
March 23, 2015 at 3:32 pm
Srl832 (3/23/2015)
I want to bulk insert data into a table named scd_event_tab inside a database named rdb.when I do select * from rdb.dbo.scd_event_tab, i get :
JOB_ID RUN_ONPRIORITYPAYLOADTIMEOUT_INTERVALSTATUSPICKUP_TIMESCD_TYPESCHEDULE_IDDB_ADMIN_LOGIN_REQUIRED_YN
I saved the result into a csv file and then truncated the table. Now, I am trying to bulk insert the data into the table. So I used:
bulk insert
rdb.dbo.scd_event_tab from 'C:\users\sluintel.ctr\desktop\eventtab.csv'
with
(
codepage = 'RAW',
datafiletype = 'native',
fieldterminator = '\t',
keepidentity,
keepnulls
);
go
However, I get this error:
Msg 4867, Level 16, State 1, Line 1
Bulk load data conversion error (overflow) for row 1, column 1 (JOB_ID).
Msg 4866, Level 16, State 5, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 3. Verify that the field terminator and row terminator are specified correctly.
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)".
Can someone help me with this Please?
If you saved it as a CSV, then why are you trying to do a "Native" import, which is a whole 'nuther ball-o-wax?
--Jeff Moden
March 23, 2015 at 3:36 pm
I'm a bit of a SQL newbie myself, fair warning.
What's the data type for column 3? What's the actual data that's in row 1 column 3 in your CSV file?
Is it definitely tab delimited?
It doesn't appear that you defined a row terminator - perhaps there's a default that I'm just unaware of.
Does the CSV you're importing from include column header names? If so you'll want to include FIRSTROW accordingly (or if this is just a one off you could delete the header row if it exists)
Edit: Perhaps give this a shot:
BULK INSERT rdb.dbo.scd_event_tab
from 'C:\users\sluintel.ctr\desktop\eventtab.csv'
with
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR ='0x0a' --Note this may not be the correct row terminator for your file.
KEEPIDENTITY,
KEEPNULLS
);
March 23, 2015 at 4:17 pm
thanks for the help guys. I think I do understand a little bit about what is going on but I am still getting errors. This is what I am using now:
bulk insert
rdb.dbo.scd_event_tab from 'C:\users\sluintel.ctr\desktop\eventtab.csv'
with
(
CODEPAGE = 'RAW',
datafiletype = 'native',
fieldterminator = ',',
ROWTERMINATOR = '',
keepnulls,
tablock
);
go
I am getting this particular error:
Msg 4866, Level 16, State 5, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 3. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".
by the way there is no header. The table which I am going to load the data into already has a header. Just to give you an idea, this is one of the rows:
146380769,476141510,5, <PAYLOAD PLATFORM="DotNet">
<PACKAGE>xxxxxxx</PACKAGE>
<SOURCE LOCATION="GAC">xxxxxxx,Version=1.4.0.0,Culture=neutral,PublicKeyToken=xxxxxx</SOURCE>
<CLASS>Agent</CLASS>
<METHOD>xxxxxx</METHOD>
<PARAMETERS>
<PARAM TYPE="String" VALUE="UAP-IAC" />
<PARAM TYPE="Long" VALUE="10" />
</PARAMETERS>
</PAYLOAD>
,300,NULL,NULL,S,140157870,N
I think I need to use a different fieldterminator, but I am not sure how to go about it .
March 23, 2015 at 4:21 pm
I have been asked by the management to provide a script for importing customer related data contained in a CSV file to a production db table next week and since this is the first time I am doing something like this, I am having some issues. That's why I am doing this.
March 24, 2015 at 9:06 am
The sample you've provided looks to be more like an XML format to me, not really a CSV. Unless the intent is to just load the XML stuff into columns in the table.
Would it be possible for you to provide a sample CSV file that you're attempting to insert from, as well as the create table code for the table you're attempting to insert it into?
March 24, 2015 at 9:26 am
Srl832 (3/23/2015)
I have been asked by the management to provide a script for importing customer related data contained in a CSV file to a production db table next week and since this is the first time I am doing something like this, I am having some issues. That's why I am doing this.
If you have a small file example that doesn't contain any personally identifiable or proprietary data and you attached it, I could take a crack at it. The commas in the XML are going to be the bitch of it all but I believe I have a pretty easy way around that.
And, yes, a different field terminator is in order but not like you currently think.
The reason why I want an actual file is so that I can check for the hex values of any row terminators and make sure the code I'd write for this actually works for you.
--Jeff Moden
March 24, 2015 at 1:51 pm
Hi Jeff, it's actually a different table that I have to load the database into. I have attached an excel sheet, which should give you an idea of how the data looks like in the table.
Here's the schema view:
USE [nggateway]
GO
/****** Object: Table [dbo].[Organization] Script Date: 03/24/2015 12:49:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Organization](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Guid] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[Description] [nvarchar](2048) NULL,
[Type] [int] NOT NULL,
[Info] [nvarchar](max) NULL,
[VpsSystemId] [int] NOT NULL,
[AddressLine1] [nvarchar](256) NULL,
[AddressLine2] [nvarchar](256) NULL,
[City] [nvarchar](128) NULL,
[State] [nvarchar](128) NULL,
[Zipcode] [nvarchar](10) NULL,
[Country] [nvarchar](128) NULL,
[ContactFirstName] [nvarchar](128) NULL,
[ContactLastName] [nvarchar](128) NULL,
[ContactPhoneNumber] [nvarchar](128) NULL,
[WebsiteUrl] [nvarchar](512) NULL,
[Location] [geography] NULL,
[logo] [varbinary](max) NULL,
[logoVersion] [nvarchar](200) NULL,
[Status] [int] NOT NULL,
[CreatedOn] [datetime] NULL,
[UpdatedOn] [datetime] NULL,
[CreatedBy] [int] NULL,
[UpdatedBy] [int] NULL,
CONSTRAINT [PK_Organization_Id] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_Organization_Guid] UNIQUE NONCLUSTERED
(
[Guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Check [Organization_Status_CK] Script Date: 03/24/2015 12:49:20 ******/
ALTER TABLE [dbo].[Organization] WITH CHECK ADD CONSTRAINT [Organization_Status_CK] CHECK (([status]=(3) OR [status]=(2) OR [status]=(1)))
GO
ALTER TABLE [dbo].[Organization] CHECK CONSTRAINT [Organization_Status_CK]
GO
/****** Object: ForeignKey [FK_Organization_VpsSystemId] Script Date: 03/24/2015 12:49:20 ******/
ALTER TABLE [dbo].[Organization] WITH CHECK ADD CONSTRAINT [FK_Organization_VpsSystemId] FOREIGN KEY([VpsSystemId])
REFERENCES [dbo].[VpsSystem] ([Id])
GO
ALTER TABLE [dbo].[Organization] CHECK CONSTRAINT [FK_Organization_VpsSystemId]
GO
/****** Object: ForeignKey [FK_Organziation_OrganizationTypeId] Script Date: 03/24/2015 12:49:20 ******/
ALTER TABLE [dbo].[Organization] WITH CHECK ADD CONSTRAINT [FK_Organziation_OrganizationTypeId] FOREIGN KEY([Type])
REFERENCES [dbo].[OrganizationType] ([Id])
GO
ALTER TABLE [dbo].[Organization] CHECK CONSTRAINT [FK_Organziation_OrganizationTypeId]
GO
March 24, 2015 at 8:04 pm
That's good. Thanks for that. But, we first have to get the data into a table. The problem at hand is to import the data, correct? That's why I'm asking for the file that you have to import. And, to be honest, that table doesn't look anything like the tags provided in the original one line of test data you provided. Help me help you here. I need the gazintas so the I can make them gazinta. 😛
--Jeff Moden
March 26, 2015 at 1:24 am
the excel file that I attached is actually the data that exported from the table that I need to load into. Sorry, the table that I was working with was just me trying something similar. However, if you could help me with this table, that would be great.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply