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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk Insert

Author

Topic

abhijeetdighe
Starting Member

24 Posts

Posted - 2007-10-09 : 09:21:50

Hi

I am using bulk insert to upload .csv file to sql server.
The first 3 rows of my file contain different format than the
rest of the file.I am using FIRSTROW to skip 3 rows,but it is giving me an error :
[Could not bulk insert because column too long in data file. Make sure FieldTerminator and RowTerminator were specified correctly]
My file looks like :

STOCK EXCHANGE
DAILY TRANSACTION
------------------
itemcode,name,stock
1,pen,10
2,pencil,20
3,eraser,30

Plz help me to skip first 3 rows

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-09 : 09:24:16

should be 4 rows ?

KH
[spoiler]Time is always against us[/spoiler]

abhijeetdighe
Starting Member

24 Posts

Posted - 2007-10-09 : 09:27:00

I want to skip following rows

STOCK EXCHANGE
DAILY TRANSACTION
-----------------------
itemcode,name,stock

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 09:40:57

[code]
1 STOCK EXCHANGE
2 DAILY TRANSACTION
3 -----------------------
4 itemcode,name,stock
[/code]
4 rows, or are you seeing the line breaks differently?

Kristen

abhijeetdighe
Starting Member

24 Posts

Posted - 2007-10-11 : 00:01:53

very very sorry for the late reply.
I want to skip first 4 rows, but the problem I think is that,
these rows dont contain the field seperator comma which causes the error.Plz help.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-11 : 00:51:04

what does your BULK INSERT statement look like?

elsasoft.org

abhijeetdighe
Starting Member

24 Posts

Posted - 2007-10-11 : 05:18:11

My Bulk Insert Procedure looks like :

SET QUOTED_IDENTIFIER OFF
go
Alter PROCEDURE Sp_BulkInsert
(
@path nvarchar(100)
)
AS
DECLARE @SQL varchar(2000)
BEGIN
SET @SQL = "BULK INSERT test.dbo.mytab FROM '"+@path+"' WITH
(FIRSTROW=5,FIELDTERMINATOR =',' ,ROWTERMINATOR ='\n')"
END

EXEC (@SQL)

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-11 : 10:04:56

whoops! how timely!

that proc has a sql injection problem. if someone passes a specially formed value of @path, you'll be in trouble.

I'm guessing the problem you are having though, is because of your ROWTERMINATOR. try this: ROWTERMINATOR ='\r\n' (or you can just remove it entirely, as \r\n is the default.

elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-11 : 10:45:16

do a print @sql in your SP and check or post it here

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 16:42:16

@khtan:

FWIW I couldn't make the syntax provided work with a simple file with 3 columns-per-row, but in the absence of some repeatable example files etc. I gave up at that point.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources