SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 BULK INSERT / DTS package
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nabeela
Starting Member

3 Posts

Posted - 06/29/2007 :  06:46:47  Show Profile  Reply with Quote
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 - 06/29/2007 :  08:57:13  Show Profile  Reply with Quote
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
Go to Top of Page

nabeela
Starting Member

3 Posts

Posted - 06/29/2007 :  09:10:23  Show Profile  Reply with Quote
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

Go to Top of Page

anxcomp
Starting Member

41 Posts

Posted - 09/07/2007 :  05:17:11  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000