BULK INSERT / DTS package - 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

General SQL Server Forums

Data Corruption Issues

BULK INSERT / DTS package

Author

Topic

nabeela
Starting Member

3 Posts

Posted - 2007-06-29 : 06:46:47

Hi,

I have a text file and having the header that have fewer columns as compare to second rows column in the txt file (also the actual table columns). I am using the bulk insert command to insert the text file data into the sql server 2000 table by using the below query:

BULK INSERT Mytable FROM 'e:\Mytext.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)

Mytext.txt contains, lets assume like this ---

Name;FName;year;Sex
Robin Singh;Robin;2007;M;Graduate;Working;India
John Mathew;John;1999;M;Post Graduate;Working;USA
Rita Jhonson;Rita;2000;F;Graduate;Housewife;UK

When we are running above query it is inserting from the 3 row i.e. John Mathew skipping the 2 row. What I come to know from the above result that if the header has less columns in the txt file then it considers 2nd row as 1st row that's why it's skipping the 2nd row.

Amazing when I use the bulk insert by using DTS package its inserting the rows properly. It’s real fun. So any idea guys, if yes can anybody tell me the way to get the DTS package script for the bulk insert or any other solution to insert all the row except header.

Thanks in advance.
Regards,
Nabeel Ahmad

Celia
Starting Member

8 Posts

Posted - 2007-06-29 : 08:57:13

Did you check if length of table's fields are big enough for the data you want to insert into it? I mean, let's suppose that you defined FName as varchar (4). In this case, 'Robin Singh 'can't be inserted into the table because FName = 'Robin' that has 5 characters. 'John' and 'Rita' have 4 characters each of them.

Regards,

Celia

nabeela
Starting Member

3 Posts

Posted - 2007-06-29 : 09:10:23

Dear Celia,

That is just example data type and length is perfect.
Just look at the first row of the text file that is culprit i want to skip that row while inserting.
So any idea

quote:
Originally posted by Celia

Did you check if length of table's fields are big enough for the data you want to insert into it? I mean, let's suppose that you defined FName as varchar (4). In this case, 'Robin Singh 'can't be inserted into the table because FName = 'Robin' that has 5 characters. 'John' and 'Rita' have 4 characters each of them.

Regards,

Celia

anxcomp
Starting Member

41 Posts

Posted - 2007-09-07 : 05:17:11

Hello,

I have the same amount of header and column, but bulk insert doesn't insert first data row, it starts insert from the 3rd row :(

I described this problem here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88866

If anybody could, help please with this strange problem, how insert this first row.
Thanks

--
Regards,
anxcomp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources