writing back data from Table to Excel

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 2012 Forums

SSIS and Import/Export (2012)

writing back data from Table to Excel

Author

Topic

divanu
Starting Member

6 Posts

Posted - 2013-04-10 : 11:03:48

HI,

I am trying to load the data from table to Excel files.
I have 5 folders, each folder has an exel file called "Excel Upload.xlsx".

I need to write data into each of the excel file(into one of the sheet) based on one condition.

If the prefix of one of the ID in the table matches prefix of folder name then write that data back to that particular folder.
Ex: ID: "GARNIER SHAMPOO" then write back to \\servername\e$\Data\MDP_76_02\Inbound\Excel Upload\Garnier\excel upload.xlsx

I am facing issues while doing this, Can any one please provide me an approach.

divanu
Starting Member

6 Posts

Posted - 2013-04-10 : 14:30:38

I don't want to hard code the prefixes. I would like to get the folder prefixes dynamically and match to prefix of the ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-11 : 02:41:36

in that case you need to have a mapping table which holds the folder names against the prefixes. then in your data flow task you can check for the prefix and based on that you can set value of variable you creating for holding the corresponding excel path. Then in excel destination map this variable to connection string property for pointing to correct excel.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

divanu
Starting Member

6 Posts

Posted - 2013-04-11 : 13:20:52

Thanks for the reply. But I don't want to create a mapping table.

Can you please tell me how can i get folder name dynamically.

I have a foreach loop container. The loop has "dirpath" in expression and excel file in variable mapping
Inside foreach loop container I have a Dataflow Task
The Dataflow task has a OLEDBSource(for getting recordset) and Excel Destination.I have a conditionalsplit inside the DataFlow Task, The conditional split has substring(Id,1,3) == SUBSTRING(@[User::strFolderName],1,3)
The problem is I am not getting the folder name dynamically.

divanu
Starting Member

6 Posts

Posted - 2013-04-11 : 13:22:33

This is kind of urgent. I really appreciate your input.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 04:44:30

quote:
Originally posted by divanu

Thanks for the reply. But I don't want to create a mapping table.

Can you please tell me how can i get folder name dynamically.

I have a foreach loop container. The loop has "dirpath" in expression and excel file in variable mapping
Inside foreach loop container I have a Dataflow Task
The Dataflow task has a OLEDBSource(for getting recordset) and Excel Destination.I have a conditionalsplit inside the DataFlow Task, The conditional split has substring(Id,1,3) == SUBSTRING(@[User::strFolderName],1,3)
The problem is I am not getting the folder name dynamically.

In Excel conection manager set connection string property to variable where you set path. Make this variable dynamic (evaluate as expression property to true) and give an expression to append the folder name variable value to the rest of the path

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

divanu
Starting Member

6 Posts

Posted - 2013-04-12 : 12:02:02

Thank you for the quick reply. I have done that, Still Its not getting the foldername dynamically. Its not getting the entire path and its not looping through subfolders.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 14:21:11

hmm...how have you set the path expression? inside foreachloop what option you chose? fully qualified?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

divanu
Starting Member

6 Posts

Posted - 2013-04-12 : 16:42:55

I am trying to attach the package here. But I don't see options here.
Inside the Foreach loop, I have selected 'Fully Qualified' option and 'traverse subfolders' option checked, under files i mentioned *.*
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-13 : 01:47:38

then it should give you full path.
then you've to apply some logic based on substring to add the foldername in between

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jason663
Starting Member

3 Posts

Posted - 2013-07-25 : 08:08:57

Here is a library can write to Excel without Excel being installed.

https://www.kellermansoftware.com/p-52-excel-reports.aspx[url][/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources