Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove first two column in excel using Script Task Expand / Collapse
Author
Message
Posted Wednesday, December 1, 2010 4:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
Hi,
I am converting an Excel File to Flat File [txt file] format. In my excel source file i am getting some unwanted values in first and second rows. Which means that from third row itself i am getting real datas in my excel.

I need to remove those unwanted rows from excel before converting it into an txt file [Flat File format]. How can i do this in Script Task?

---
Post #1028517
Posted Wednesday, December 1, 2010 1:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 9, 2014 10:16 PM
Points: 265, Visits: 587
If the "good" data doesn't start until the 3rd column, then you actually don't need a script task to correct this. You can just use an excel source, your transformations in between, and a flat file destination. In your flat file destination you would just ignore the first two columns in the mapping.

Strick



Post #1028903
Posted Thursday, December 2, 2010 5:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
stricknyn (12/1/2010)
If the "good" data doesn't start until the 3rd column, then you actually don't need a script task to correct this. You can just use an excel source, your transformations in between, and a flat file destination. In your flat file destination you would just ignore the first two columns in the mapping.

Strick


But i am getting error while clicking mapping column tab after imporing this into Excel Source part.
Post #1029165
Posted Thursday, December 2, 2010 5:17 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 4,983, Visits: 11,675
The title of this thread refers to columns, yet your posts refer to rows. That's an important difference - which do you mean?

For future reference, when you get an error and you want to ask the community for help resolving it, please post the text of the error message.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1029167
Posted Thursday, December 2, 2010 6:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
Phil Parkin (12/2/2010)
The title of this thread refers to columns, yet your posts refer to rows. That's an important difference - which do you mean?

For future reference, when you get an error and you want to ask the community for help resolving it, please post the text of the error message.


Ops, Sorry Phil. I d'nt noticed that. I need rows not columns. But i got the solution for this.

In my data Flow task, i need to set the "OpenRowset" Custom Property in my Excel Source (right-click your Excel connection > Properties; in the Properties window, look for OpenRowset under Custom Properties). To ignore the first 2 rows in Sheet1, and import columns from A-M, i would enter the following value for OpenRowset: Sheet1$A3:M (notice, I did not specify a row number for column M. You can enter a row number if you like. Using my OLEDB Destination i got all the records to my table.

Thank You guyz for your valuable info.
Post #1029216
Posted Thursday, December 2, 2010 7:02 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 4,983, Visits: 11,675
Nice solution - I'm sure others will use it.

Phil



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1029230
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse