Pick up the file

Question

Post reply

Pick up the file

Data9

SSC-Addicted

Points: 453
More actions
June 30, 2009 at 12:25 pm

#206532

Hi,
I have to import Excel file data to a table. My excel file name is like this: filename_2006_06_30_084520.xls. Minutes and seconds are changing daily. I want to pick the file, I know how to pick up to year, month and date. I want to know how to exclude time part and pick the file.
I appreciate your quick responce
Thanks
🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

Do you mean that you know that there will be a file called

filename_2006_06_30_hhmmss.xls

but that you do not know what the hhmmss bit will be? So a test for

filename_2006_06_30_*.xls

would return the file you want?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Data9 SSC-Addicted Points: 453 More actions · Answer 2

Thanks for your reponce first.

Please let me know where can I give *.*. Can you please brief me in detail

Phil Parkin SSC Guru Points: 246963 More actions · Answer 3

I asked you two questions and you answered neither. So I do not know whether I am on the right track.

A Foreach container might give you what you want ... maybe.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Data9 SSC-Addicted Points: 453 More actions · Answer 4

Hi,

Sorry for not answering. Here are the answers for two questions.

Yes I know the filename but hhmmss will be varying.

ex: filename_2006_06_29_084520.xls

filename_2006_06_30_084510.xls

Can you please elaborate where can I use this filename_2006_06_30_*.xls?

Also I tried using foreachloop but in the dataflowtask how can I pass output variable value to a excel connection manager.

I really appreciate this.

Thanks

Phil Parkin SSC Guru Points: 246963 More actions · Answer 5

OK, this is how I think it should work - not that I've done it before - so please excuse slight inaccuracies ...

1) Create two string package variables, 'FileSpec' and 'FileName' (or whatever)

2) Populate the FileSpec variable with your wildcard search string - filename_2006_06_30_*.xls in your example.

3) Add a Foreach loop - Foreach file enumerator. Set the folder as required and in the 'Expressions' section, set the Filespec to be the 'Filespec' variable mentioned above.

4) On the variable mappings page, map the FileName variable to index 0.

5) Drop a data flow task into the Foreach loop.

6) Set up the dataflow task as usual. For your Excel connection manager, create an expression for the ExcelFilePath property, setting it to the contents of your 'FileName' variable.

See this thread for more details:

http://www.sqlservercentral.com/Forums/Topic559905-148-1.aspx

Best of luck

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin