Bulk Insert Problem, Fails With Column Too Long Error
May 6, 2008
Hello All,
I'm having a problem doing a bulk insert on a tab delimited text file into mssql 2005 using either bulk insert or bcp.
When using the following bulk insert command I get the "The column is too long in the data file for row 1, column 2" error.
I have tried
The data file only has data for the first 10 columns of a table with over 100 columns.
First 10 table columns have the format of
CREATE TABLE [dbo].[CustomerDefinition](
[Rowid] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CustomerId] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Addr1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Addr2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Addr3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zipcode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_CustomerDefinition] PRIMARY KEY CLUSTERED
The data-file looks like this (it is tab delimited):
1 1 BEN ONE BENVENUTO 1 BENVENUTO ST. CLAIR & AVENUE ROAD TORONTO ON
2 1 BIGGIN DDP PARTNERSHIP 1 BIGGIN LTD. 1 BIGGIN COURT NORTH YORK ON
3 1 EVA MELIA CORPORATION 1 EVA ROAD SUITE #412 ETOBICOKE ON
4 1 FINANC CONCERT PROPERTIES 200 BAY STREET- SOUTH TOWER SUITE 2100- PO BOX 56 TORONTO ON
5 1 LONGBRID BERKLEY PROPERTY MANAGEMENT INC 1 LONGBRIDGE ROAD 2ND FL THORNHILL ON
6 10 DORA VILLA LASFLORES C/O FOCUS PROPERTIE 10 DORA AVENUE TORONTO- ON
7 10 HOLMES HALTON COMMUNITY HOUSING 10 HOLMESWAY PLACE ACTON ON
8 100 CANYON DEL PROPERTY MANAGEMENT 100 CANYON AVENUE BATHURST & SHEPPARD NORTH YORK ON
9 100 CEDAR LAWRENCE CONSTRUCTION 100 CEDAR AVENUE YONGE & MAJOR MACKENZIE WEST RICHMOND HILL ON
10 100 GOWAN KANCO - 100 GOWAN LTD. 100 GOWAN AVENUE PAPE & DANFORTH EAST YORK ON
I have two versions of "rsk.txt" one with 1.9mill rows and one with the first 2000 rows only. The files have one column only with 115 characters that I'll split in to several columns later using SUBSTRING. The one with 2000 rows fires in to the database with no problems whatsoever using this exact code, the other one throws the following error:
Server: Msg 4866, Level 17, State 66, Line 1 Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
How can I resolve this problem?
EDIT: I tried several different row- and fieldterminators but this exact one works for the small data-file so I assume it should also work for the large one...the large one is however copyed directly using binary ftp from a unix-filesystem and the small one is manually copied into a new txt-file using UltraEdit.
I am new to MS DTS and i am using MS SQL 2000 as my database. I am trying to do a Bulk insert using MS DTS package. The package is trying to load data from Text file to a SQL 2000 table. When runninh the package i am getting an error saying that 1 task failed during execution and the task is shown in red colour indicating that the task has failed. Now when i get the details of the error it shows the follows:
Could not bulk insert because the file D:DtsFile.txt could not be opened. Operation system error code: 21 (The device is not ready).
Please help me in solving this problem, if any one has got this error and resolved or have any idea of the error please help. :)
I am using SQL Server Data Tools for Visual Studio 2012. I have a very simple SSIS package with a Data Flow task that exports from an OLE DB Source to a tab-delimited unicode Flat File Destination and a Bulk Insert task that loads from the file. Both the Flat File Destination and Bulk Import are using the same code page. The Bulk Insert task is using the wide char format to read from the file. The process works fine with nvarchar and int columns, but when I add a unique identifier column it fails with "type mismatch or invalid character for the specified code page".
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Here is the situation, please let me know if you have any tips:..TXT files in a share at \fooSPROCS run daily parses of many things, including data on that share. Theother day, we encountered rows in the TXT files which looked like:column1Row1data,column2Row1datacolumn1Row2data,column2Row2data...etc..However, column2 was about 6000 bytes of unicode. We are bulk insertinginto a table specifying nvarchar(4000). When it encounters high unicoderows, it throws a truncation error (16).We really need information contained in the first 200 bytes of the string incolumn2. However, the errors are causing the calling SPROC to abort.Please let me know if you have any suggestions on workarounds for thissituation. Ideally, we would only Bulk Insert a sub-section of column2 ifpossible.Thanks!/Ty
The destination table is truncated and indexes are dropped before loading and after data being inserted we re-create the indexes.
Before this, a view extracts data from more than 22 tables from a staging database and tries to insert this data in the destination table.
it used to take 12-15 mins, but since yesterday loading one particular table never completes. While loading, the database is set to Simple recovery. There are no blocking. It's part of a daily batch thats loads 6 GB of data everyday. But while loading on particular table it's just keep running for hours. I tried rebuilding the indexes and re-starting the SQL Server but of no use.
Any help is much appreciated as this production batch job.
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages: BULK INSERT TableName FROM 'C:DataDbTableName.bcp' WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following: DataFileType: DTSBulkInsert_DataFileType_WideNative RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help. Paul
I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example]( col1 [varchar](10) NOT NULL, col2 [datetime] NOT NULL, col3 [date] NOT NULL, col4 [varchar](6) NOT NULL, col5 [varchar](3) NOT NULL,
The first column should store double (in col2 and col3) in my table
My file: Col1,Col2,Col3,Col4,Col5,Col6,Col7 2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS 2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command: bulk insert data.example from 'R:epoolexample.csv' WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error: Msg 4823, Level 16, State 1, Line 2 Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as: used ";" and "," as column delimiter changed file type from UNIX to DOS and adjusted the format file with " " for row delimiter
Removed this line from format file 1 SQLCHAR 0 10 "@|@" 2 Col2 "" Nothing works ....
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode). 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)".
BULK INSERTtbl_ASX_Data_temp FROM 'M:DataASXImportTest.txt' WITH (FORMATFILE='M:DataASXSQLFormatImport.Fmt')
Hi I have a page that bulkinsert data to my sql server, I build up the bulk insert part like this.... 1 sb.Append("Exec p_BulkInsertPDI '<ROOT><PROT>") 2 3 sb.Append("<PDI NID=""" & HiddenField1.Value & """ AID=""" & HiddenField1a.Value & """ MID="" GID="" UID=""" & UserID & """/>") 4 5 sb.Append("</PROT></ROOT>'")The problem I have here is that sometimes the AID value doesn't have any value beacuse on the previous page haven't sent any value to that hiddenfield. So when I try to run this, I get a error message like this... "Conversion failed when converting the nvarchar value 'AID=' to data type int".It would be the best if I could insert Null values if no value have been provided. Is this possible to do? Regards
I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:
Msg 4861, Level 16, State 1, Line 1 Cannot bulk load because the file "\FILESERVERNAMEsharedfolderfilename.txt" could not be opened. Operating system error code 5(Access is denied.).
Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):
BULK INSERT #FIRSTROW FROM '\FILESERVERNAMEsharedfolderfilename.txt' WITH ( DATAFILETYPE = 'char', ROWTERMINATOR = '', LASTROW = 1 )
If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.
If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.
My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.
I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1 , but still no luck and same error.
I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.
Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).
Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).
I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.
It seems to me that files created on Unix machines with line terminator , or chr(10), cannot be imported using the Bulk Insert statement. Is this a bug, or an oversight by Microsoft? Does this mean that unless one replaces all with , there is no way to use Bulk Insert to import Unix files? This is a very strange behavior by MSSQL. Even lessor programs such as Excel and Word automatically recognize chr(10) as a line termination character. Am I missing something, or is this just the way MSSQL is?
Hi, I have a question on inserting data for only a specific column in a table. I have a table as follows Table <MyTable> {
Name varchar, DateUpdate DateTime }
I wanted to insert the the from a file to table. The File contains the list of name as follows (line by line)
name1 name2 name3 name4 ......
The file name actually contains the DateTime I would like to insert the names in the file as wellas the DateTime (i.e. part of the file name ) into <MyTable>
I guess "Bulk insert " doens't allow to insert values for only one column
But my original file will contains only Names and the file name contains the date that commom for all the names in the file. And also the file may contains millions of names
Is there any way this can be accomplised using " Bulk Insert" ? Or is there any alternative that i can do it fastly
I am trying to bulk insert a text file. The file has fixed-length fieldswith no field terminators. BOL says that field terminators are onlyneeded when the data does *not* contain fixed-length fields, whichimplies they are optional -- so I made a format file without any (twoconsecutive tabs with nothing between them). The following messageresulted:Server: Msg 4827, Level 16, State 1, Line 1Could not bulk insert. Invalid column terminator for column number1 in format fileThat sounds like I am required to have some sort of terminator in theformat file, even though there aren't any in the data file. Unfortunately,the documentation on bcp/bulk copy and format files does not directlyaddress this point, and I would appreciate some help.BTW, putting '""' (empty string) for the terminator also leads to errors,with the first field overflowing -- bulk insert can't figure out whereit ends.Thanks,Jim GeissmanCountrywide Home Loans
I have a text file with a single column that i need to bulk insert into a table with 2 colums - an ID (with identity turned on) and col2
my text file looks like:
row1 row2 row3 ... row10
so my bulk insert i have like this: BULK INSERT test FROM 'd: estBig.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = ' ' )
but i get the error:
Server: Msg 4866, Level 17, State 66, Line 1 Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
However, as you can see from the text file, there is only one column, so i dont have any field terminators.
I have a file I'm trying to do some non-set-based processing with. Inorder to make sure I keep the order of the results, I want to BULKINSERT into a temp table with an identity column. The spec says thatyou should be able to use either KEEPIDENTITY or KEEPNULLS, but I can'tget it to work. For once, I have full code - just add any file of yourchoice that doesn't have commas/tabs. :)Any suggestions, folks?--create table ##Holding_Tank ( full_record varchar(500)) -- thisworkscreate table ##Holding_Tank (id int identity(1,1) primary key,full_record varchar(500)) --that doesn't workBULK INSERT ##Holding_TankFROM "d: elnet_scriptspsaxresult.txt"WITH(TABLOCK,KEEPIDENTITY,KEEPNULLS,MAXERRORS = 0)select * from ##Holding_tank
Need to know a mode whereby somehow I can every time insert an additional column in a table while bulk inserting data to an existing table from a new flat file thus identifying the file from which, or the time when, the data was inserted in an existing table.
I just wanted to insert only SubjectIds into my table 'Subjects' which has the follwing schama ignoring the classes The row delimeter is " " and the column delimeter is ' | '
Table Subjects {
ID (Autoincrement) SubjectId varchar(20) }
Can any one provide the format file for doing this or suggest anyway to do this? Please do note that the file may contain millions of records
Hi, I have a problem with BULK INSERT. I created the following table:
Code Snippetcreate table Test (id char(4), name nvarchar(16), last char(1))
I am trying to bulk insert data from ASCII (not unicode) file with only two rows: 0011First name 0018Second name
Since it is a fixed length file, I am using the following format file:
Code Snippet 8.0 3 1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS 2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS 3 SQLCHAR 0 0 " " 3 Last HEBREW_CI_AS
With bcp utility everything works just fine!
Code Snippet bcp Demo.dbo.test in c: est -T -f c: est.fmt
But when I use BULK INSERT in the following form:
Code Snippet BULK INSERT Test FROM 'c:Test' WITH ( FORMATFILE='c:Test.fmt', CODEPAGE='OEM' );
I am getting error Server: Msg 4863, Level 16, State 1, Line 1 Bulk insert data conversion error (truncation) for row 1, column 2 (name).
Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well. Can anybody explain what is going on here?
however, it fails when i try to insert the value 0000-00-00 00:00:00. ie., the following insert statement fails
INSERT INTO TEST VALUES('0000-00-00 00:00:00')
The error thrown is,
Server Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.
I am working with parent child tables and want to populate the primary key on insert so that the user does not have to enter this for each record. Here is my codeInsertCommand="INSERT INTO [Awards] ([UFID], [DateAwarded], [Amount], [AwardingAgency]) Select UFID, @DateAwarded, @Amount, @AwardingAgency from master where GatorlinkName = @LoginName" <InsertParameters><asp:Parameter Name="LoginName" Type="String" /> <asp:Parameter Name="strusername" Type="String" /> <asp:Parameter Name="UFID" Type="String" /> <asp:Parameter Name="DateAwarded" Type="DateTime" /> <asp:Parameter Name="Amount" Type="Decimal" /> <asp:Parameter Name="AwardingAgency" Type="String" /> </InsertParameters> The UFID field is the only field that should be populated from SQL data the others are coming from a form view insert form. When I run an insert I get no error but the insert does not happen. I know that the @LoginName works since I am using this same logic in my select statement. Thanks in advance for your help,Ken
I'm doing a bulk insert from a text file to sql server 7 I'm getting an error:
Server: Msg 4867, Level 16, State 1, Line 1 Bulk insert data conversion error (overflow) for row 1, column 169 (LOT_WIDTH). Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. The statement has been terminated.
Now my lot-width field coming in is defined as a numeric 9(5). My table is defined as an INT.
I am attempting to bulk insert a comma delimited text file with double quotes as the text qualifier but I keep getting an error message(EOF) on the bulk insert.
I think the problem lies in my format file (see below)
Please take a look and let me know what I am missing?
Thanks, Matt
Error message: Msg 4832, Level 16, State 1, Line 1 Bulk load: An unexpected end of file was encountered in the data file. 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)".
I found error in bulk insert: - "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.".
I do read a few article saying that after apply SP2 and hotfixes, this error should be fix, but unfortunately, it is not in my case, what should i do to fix it?
This is my script: - BULK INSERT wng01_work..nw_business_person FROM 'g:SQLFTPCDIS_Extractew_worker.dat' WITH ( MAXERRORS = 1, FORMATFILE ='g:sqlftpcdis_extractew_work.fmt' )
When I try to execute the code, I get the following error, on this line: select @cmd = @cmd + ' with (Fieldterminator = ',')'
Msg 141, Level 15, State 1, Procedure Imp_Header_PO_sp, Line 46
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
I've tried to find a fix for this error, but it seams to only relate to a select statement and not a Bulk Insert. Can someone please help me figure out how to fix this error?
Simple test project. Created Flat File connection, database connection (both local), and Bulk Insert Task. When running the package I get the following error:
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Bulk load: An unexpected end of file was encountered in the data file.".
I've tried different settings for the Flat File config, and the database connection, but still get the error. Any suggestions would be helpful.
