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

Retrieve Part of the Filename stored in a variable Expand / Collapse
Author
Message
Posted Friday, January 16, 2009 4:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:51 PM
Points: 215, Visits: 556
Hi

I am using a Foreach loop container and in the derived column im trying to retrieve only the Filename but when I use the variable which actualy stores the Filename I also get the path of the File For example If a use a variable in the derived column expression I get the following Output C:\Data\Loop\Filesame.xls but I only need the file name i.e., name... How do i do that .. I have tried using Name only option in the For each Enumerator but it gives me error asking me to check if the Worksheet Name [Excel Source [122]] Error: Opening a rowset for "New$" failed. Check that the object exists in the database.
Post #638589
Posted Tuesday, January 20, 2009 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 44, Visits: 1,675
Hey Danny,

one way to get the file name only would be to create another variable. set it's property, EvaluateAsExpression to True. Then enter the following expression:

REVERSE(SUBSTRING( REVERSE(@[User::filename]),1, FINDSTRING( REVERSE(@[User::filename]) , "\\", 1 ) -1))

replace the @[User::filename] variable in the sample code with your variable that is being populated by the ForEachFile loop.

ArkWare
Post #639937
Posted Tuesday, January 20, 2009 12:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:51 PM
Points: 215, Visits: 556
Thanks a lot dude!!!!
Post #640173
Posted Tuesday, January 20, 2009 1:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 44, Visits: 1,675
No Problem, Dude

Expressions are your friend..

ArkWare
Post #640189
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse