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
 SSIS and Import/Export (2005)
 Skip Last Row

Author  Topic 

gunasilanm
Starting Member

8 Posts
Posted - 2011-08-29 : 00:29:09
I'm currently working on BULK INSERT to load data from my csv to staging table. Everything is working fine unless one last thing where my flat file last row contains number of data and its dynamic. How do i skip the last row in BULK INSERT. my code.

"INSERT Cust_Info_Test_Loader "
                  + "([Reseller_ID],"
                  + "[Reseller_Name],"
                  + "[Account_No],"
                  + "[Login_ID],"
                  + "[Customer_Name],"
                  //+ "[Outlet_ID],"
                  + "[Service_Type],"
                  + "[Outlet_FullName],"
                  + "[Service_No],"
                  + "[Status_Date],"
                  + "[Package_Name],"
                  + "[Registration_Date],"
                  + "[Status_Code],"
                  + "[Outstanding_Balance],"
                  + "[Agent_ID],"
                  + "[Old_Package_Name],"
                  + "[Product_Code],"
                  + "[Cust_Seg_Code],"
                  + "[PTT_Code],"
                  + "[PTT_Desc],"
                  + "[Cost_Centre_Code],"
                  + "[Sector_Code],"
                  + "[Old_Outlet_ID]"
                  //+ "[Package_Group]"
                  + ")"                  
                  + "select [Reseller_ID],"
                  + "[Reseller_Name],"
                  + "[Account_No],"
                  + "[Login_ID],"
                  + "[Customer_Name],"
                  //+ "[Outlet_ID],"
                  + "[Service_Type] = 'Y',"
                  + "[Outlet_FullName],"
                  + "[Service_No],"
                  + "[Status_Date],"
                  + "[Package_Name],"
                  + "[Registration_Date],"
                  + "[Status_Code],"
                  + "[Outstanding_Balance],"
                  + "[Agent_ID],"
                  + "[Old_Package_Name],"
                  + "[Product_Code],"
                  + "[Cust_Seg_Code],"
                  + "[PTT_Code],"
                  + "[PTT_Desc],"
                  + "[Cost_Centre_Code],"
                  + "[Sector_Code],"
                  + "[Old_Outlet_ID]"
                  //+ "[Package_Group]"
                  + "FROM  OPENROWSET (BULK 'C:/Java/testload.csv',FORMATFILE='C:/Java/test.Xml',LASTROW = -1) as BulkLoadFile";

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-08-29 : 01:42:18
did you try setting LASTROW = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

gunasilanm
Starting Member

8 Posts
Posted - 2011-08-29 : 02:41:33
Hi Visakh. Yes i have tried LASTROW = 1. It only taking 1 row.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2011-08-29 : 03:05:00
Can you post some sample data of actual data with last row data?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

gunasilanm
Starting Member

8 Posts
Posted - 2011-08-29 : 03:11:35
Hi Madhivanan,

Please refer below for the samle data.
Thanks.

tmcsme01|tmc|2011602|kc62|FORTUNE SDN BHD.|TRTC02-Telemarketing Center|0890632|2011-08-16|Plan 1 |2011-07-26|10103|0|X1010348V||DUB01|S10|SB|Saah|YNDSSS|Organization (Business)||
tmcsme01|tmc|2011102|j477|FOOD ENTERPRISE|TRTC02-Telemarketing Center|0387661|2011-08-16|Plan 1 |2011-07-26|10103|0|X1012413H||DUB01|S10|SB|Saah|YNDSSS|Organization (Business)||
tmcsme01|tmc|2011309|l45|BROTHERS CONTRACTION|TRTC02-Telemarketing Center|0885618|2011-08-16|Plan 4 |2011-07-27|10103|0|X0808418H||DUB01|S10|SB|Saah|YNDSSS|Organization (Business)||
tmcsme01|tmc|2011002|s11|CARGO HANDLING|TRTC02-Telemarketing Center|0886757|2011-08-16|Plan 3 |2011-07-29|10103|0|X1010348H||DUB01|S10|SB|Saah|YNDSSS|Organization (Business)||
tmcsme01|tmc|2011506|c11|CAHA SDN BHD|TRTC02-Telemarketing Center|0386918|2011-08-16|Plan 3 |2011-08-08|10103|0|X1101692V||DUB01|S10|SB|Saah|YNDSSS|Organization (Business)||
tmcsme01|tmc|20119908|ng88|TENG SDN BHD|TRTC02-Telemarketing Center|0090929|2011-08-16|Plan 2 |2011-08-10|10103|0|X1107108H||DUB01|S10|KN|Kelan|YNDSDD|Organization (Business)||
000000006
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2011-08-29 : 03:26:10
Try to use this logic

+ "FROM OPENROWSET (BULK 'C:/Java/testload.csv',FORMATFILE='C:/Java/test.Xml') as BulkLoadFile
WHERE col not like '[0-9]%'";


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

gunasilanm
Starting Member

8 Posts
Posted - 2011-09-04 : 23:15:58
Hi Madhivanan,
The reason why i wanted to skip my last row is because, if you see my sample data you can notice that the last record is showing the number of row. In my formatfile i have set the rowterminator as pipeline '|'. Since the last row '000000006' no pipeline, an error occurred whenever i run my program to upload the data. Error details.'java.sql.SQLException: Bulk load: An unexpected end of file was encountered in the data file.'
Appreciate if anyone can help me on this. Stuck with this for almost 1 week.
Sample format file:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1"  xsi:type="CharTerm" TERMINATOR="|"    MAX_LENGTH="100"/>
  <FIELD ID="2"  xsi:type="CharTerm" TERMINATOR="|"    MAX_LENGTH="100"/>
  <FIELD ID="3"  xsi:type="CharTerm" TERMINATOR="|"    MAX_LENGTH="100"/>
  <FIELD ID="4"  xsi:type="CharTerm" TERMINATOR="|" 	 MAX_LENGTH="100"/>
  <FIELD ID="5"  xsi:type="CharTerm" TERMINATOR="|" 	 MAX_LENGTH="100"/>
  <FIELD ID="6"  xsi:type="CharTerm" TERMINATOR="|"  	 MAX_LENGTH="100"/>
  <FIELD ID="7"  xsi:type="CharTerm" TERMINATOR="|" 	 MAX_LENGTH="100"/>
  <FIELD ID="8"  xsi:type="CharTerm" TERMINATOR="|"    MAX_LENGTH="100"/>
  <FIELD ID="9"  xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="200"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="18" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="19" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="20" xsi:type="CharTerm" TERMINATOR="|"	 MAX_LENGTH="100"/>
  <FIELD ID="21" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="100"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1"  NAME="Reseller_ID"      	 xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2"  NAME="Reseller_Name"   	 xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3"  NAME="Account_No"      	 xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4"  NAME="Login_ID"         	 xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5"  NAME="Customer_Name"       xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6"  NAME="Outlet_FullName"     xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7"  NAME="Service_No"       	 xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8"  NAME="Status_Date"       	 xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="9" NAME="Package_Name"         xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="10" NAME="Registration_Date"   xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="11" NAME="Status_Code"         xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="Outstanding_Balance" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="13" NAME="Agent_ID"            xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="14" NAME="Old_Package_Name"    xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="15" NAME="Product_Code"        xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="16" NAME="Cust_Seg_Code"       xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="17" NAME="PTT_Code"            xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="18" NAME="PTT_Desc"            xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="19" NAME="Cost_Centre_Code"    xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="20" NAME="Sector_Code"         xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="21" NAME="Old_Outlet_ID"       xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site