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
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Skip Last Row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gunasilanm
Starting Member

Malaysia
8 Posts

Posted - 08/29/2011 :  00:29:09  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/29/2011 :  01:42:18  Show Profile  Reply with Quote
did you try setting LASTROW = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gunasilanm
Starting Member

Malaysia
8 Posts

Posted - 08/29/2011 :  02:41:33  Show Profile  Reply with Quote
Hi Visakh. Yes i have tried LASTROW = 1. It only taking 1 row.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/29/2011 :  03:05:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Malaysia
8 Posts

Posted - 08/29/2011 :  03:11:35  Show Profile  Reply with Quote
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

India
22755 Posts

Posted - 08/29/2011 :  03:26:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Malaysia
8 Posts

Posted - 09/04/2011 :  23:15:58  Show Profile  Reply with Quote
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
  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.08 seconds. Powered By: Snitz Forums 2000