Error while importing data from CSV to SQL table using BULK Insert

Question

Hello All,

I have an CSV file which have the foloowing data as shown below:

DETAIL|17|2012-03-15|1|519     --First Row
DETAIL|18|2012-03-16|2|520 --IMPORTED TO DB
DETAIL|19|2012-03-17|3|521 --IMPORTED TO DB
DETAIL|20|2012-03-18|4|522     --Last Row

When I import the data from CSV file to SQL table the data imported successfully but it imports only 2 rows instead of 4 i.e first row and the last rows are not imported.

I am using Bulk Insert command in SQL as shown below:

'BULK INSERT Employee FROM ''' + 'C:\Users\Administrator\Desktop\extract2.csv' + ''' WITH 
( FIRSTROW = 1,
MAXERRORS =0,
FIELDTERMINATOR = ''|'' ,
ROWTERMINATOR   =''\n''
)'

I has given me lot of grief to find out the problem but not able to identify it.

Please help me for this issue.

Thanks in advance.

---

Pankaj Kumar Yadav-

Answer 1

can you try to unify spacing?

not spacing between last field and comment is different between rows 1 and 4 and rows 2 and 3

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 2

Actually data is 

DETAIL|17|2012-03-15|1|519   
DETAIL|18|2012-03-16|2|520 
DETAIL|19|2012-03-17|3|521
DETAIL|20|2012-03-18|4|522

The comments are just for reference.........

---

Pankaj Kumar Yadav-

Answer 3

Hi Pankaj Kumar Yadav,
Regarding to the data you provided, using  Transact-SQL bulk insert I did a test success. The code as below:
BULK
 INSERT tempdb.dbo.Table_1
FROM 'C:\\File1.csv'
WITH
( FIELDTERMINATOR ='|',
 ROWTERMINATOR ='\n',
firstrow = 1,
lastrow = 4
 )

If you still have problem, you can try to use a tool which can handle this, like SSIS which is much easier.

---

Regards, Amber zhang