Query Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-11-26 : 11:30:32

Hi,
I am trying to import the records from csv file. I am getting the error while doing import. Here is query i used to import.

The .csv file size is 350 MB. It does not have the headers.


BULK INSERT Customerinfo_Stage 
    FROM 'C:\Documents and Settings\kkumar\Desktop\Cust\arcscusp.csv' 
    WITH 
    ( 
		FIRSTROW = 1, 
		FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

--Exec (loadCustinfo)

Error:-

quote:

Bulk load data conversion error (truncation) for row 1, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 2, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 3, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 4, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 5, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 6, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 7, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 8, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 9, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 10, column 1 (CSCUS).
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 11, column 1 (CSCUS).
Msg 4865, Level 16, State 1, Line 2
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Please suggest me to solve this issue

Thanks in advance

-- Krishna

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-26 : 11:34:07

Check for size of CSCUS column vs what you have in arcscusp.csv. Make sure you've specified the right FIELDTERMINATOR.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-11-26 : 11:39:19

Thanks for the quick reply sakets_2000. The CSCUS Column max size is 18 characters, in staging table it is varchar(max)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 11:57:47

quote:
Originally posted by CSK

Thanks for the quick reply sakets_2000. The CSCUS Column max size is 18 characters, in staging table it is varchar(max)

and is field terminator ,?

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-11-26 : 11:58:31

yes

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-11-26 : 11:59:29

anyhow i opened the file in Wordpad, i just copied into small file. I ran the quers it worked but it is throwing the follwoing error.

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-26 : 12:08:26

Is the source file from a UNIX type machine? ROWTERMINATOR = '\n' won't work, I think you have to use dynamic sql and nchar(10).

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-11-26 : 12:10:10

I beleive it is from DB2. So how i have to write a query to do import..? Is there any special query do we have..?

Thanks
Krishna

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-26 : 12:13:47

Something similar to

declare @sql nvarchar(4000)
set @sql = 
'BULK INSERT Customerinfo_Stage 
    FROM ''C:\Documents and Settings\kkumar\Desktop\Cust\arcscusp.csv''
    WITH 
    ( 
		FIRSTROW = 1, 
		FIELDTERMINATOR = '','', 
        ROWTERMINATOR = ''' + nchar(10) + ''' 
    )'
select @sql
exec(@sql)

EDIT: char => nchar

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-11-26 : 12:14:44

Bjoerns, Thanks for your reply. Let me try this.

Thanks
Krishna

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-26 : 12:16:52

Welcome. Please notice that I changed

char(10)

nchar(10)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-26 : 12:34:15

Or, You could change filetype from unix to pc using textpad editor. Or, Just change your row delimiter to what you have coming in the file. I think UNIX type files normally have left feed only as the row delimiter.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-11-26 : 12:35:25

Bjoerns, I am getting the same error While Using the following Query

--------------------------------------------------------------------------------
(1 row(s) affected)
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)".
------------------------------------------------------------------------------

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-26 : 13:04:29

You have a problem with row delimter, If possible try loading using DTS and figure out your row delimiter by previewing the file.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-11-26 : 13:07:12

Yupe!! I beleive so. I already started. I dont know when it is going to complete....

Thanks for your replay

-- Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources