Question

Workflow Queries to Network Excel Spreadsheet

Version 9 Workflow

Updated July 1, 2014

asked on March 17, 2014 • Show version history

Hello,

Need to query an Excel spreadsheet stored over the network (aka DSN-less data source).

note: please assume network access a hard requirement, so is access to native file (no SQL transit ;-(

As ODBC sources are local to the Workflow server, is there any mechanism to Query Data followed by For Each Row to a DSN-less Excel spreadsheet?

1 0

Answer

SELECTED ANSWER

replied on March 17, 2014

You can make a 'Direct' DSN-less connection to excel from a query data activity and then use 'For Each Row' to process the results.

excel.png (100.27 KB)

| Download

3 0

replied on March 18, 2014

Thank u Ed... works great!

FYI, filename (and possibly file path) provided in direct connections cannot contain spaces (unlike the ODBC counterpart ;-(

0 0

replied on July 1, 2014 • Show version history

Hi Ed,

I was facing similar issue but I managed to get it working by providing a hard coded path, same as you have mentioned here (as it doesn't let you browse to the xls file on network share).

But now the problem I have is, in my workflow at the end I was moving the xls file into a sub-folder called 'processed'. (using SDK script)

This was working when my xls was on local drive but after changing the path to shared drive, I am getting warning/error as WF is looking at the C drive instead of shared drive.

Please see the screenshot attached. In the screenshot the shared folder path is \\en-prd-cache01\........ but workflow is changing it to C:\en-prd-cache01\.......

Any thoughts?

Thanks

WF Path.png (3.67 KB)

| Download

0 0

Replies

replied on March 17, 2014

Is it not possible to use a script? You can access any excel file from a script that would be accessible from the machine and it's network connections.

Might be able to use a multi-value token to get each row. like combining every value with a separator and then using the "Split" function on the token to get what you need for the current value.

0 0

replied on March 17, 2014

Ken,

Can you be more specific about the script methods?

0 0

replied on March 17, 2014

there are two Script activities inside workflow. You can use them to do things that you otherwise would not be able to with the current set of Workflow Activities, provided you know how to script or form a script.

If you look on Google for ways to use C# or .NET to access an Excel file, you will find lots of ways.

0 0

You are not allowed to follow up in this post.

Question

Question

Workflow Queries to Network Excel Spreadsheet

Answer

Replies

Sign in to reply to this post.