Question

Laserfiche workflow script Microsoft Office Excel cannot access file within local drive

Workflow Laserfiche

Updated May 21, 2020

asked on December 4, 2019

I wrote a little c# script that will read excel file (csv) and query (insert) each row to the SQL table.

When i run the script on the editor the script will run perfectly and i have verified that the SQL Table is updated.

However, when i run the workflow in designer, i receive a warning/error that breaks my script.

Message:

Microsoft Excel cannot access the file 'C:\****\*****\Desktop\****\LF STUFF\APexcel.xlsx'. There are several possible reasons:

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

My guess is that maybe the workflow designer has a user but is not an authenticated windows user so it does not have read/write to access such file.

if that's the case how do i go about creating access for my designer user or maybe my assembly issue? i am positive that my reference assembly is good because the GAC setting is Yes.

tldr: script runs on editor but not on designer :

if anyone wants to throw suggestions --- Let the comments flow

0 0

Replies

replied on December 4, 2019 • Show version history

If you have the Excel file open while you're running the workflow, that's likely the cause of the error. Excel does not allow multiple concurrent connections.

The WFServer runs as the user specified in its service login. The Designer runs as the currently logged in user.

If your script is using Microsoft's Office interops to access Excel, that's not going to work at runtime because they're going to try to open Excel (and the Workflow server will not have a user session in which to launch a window).

0 0

replied on December 4, 2019

You are correct workflow server does not have the proper rights and Microsoft excel does not allow connection on the server side which is what happens when you run the workflow normally.

https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office

^ shout out Alex Huang

0 0

replied on December 4, 2019

Out of curiosity, is there a reason you wanted to do this with C# scripts instead of built-in activities?

If you configure your Excel file in ODBC as a data source on the Workflow server, you could query it with the Query Data activity. You'd have to use a For Each Row loop on the results with the Insert Data activity to move it into SQL, but it would eliminate the need for custom scripting.

0 0

replied on December 4, 2019

Okay i will try this and tell the people of Laserfiche. Thank you

0 0

replied on December 4, 2019

As a follow up, you may need to install the Excel ODBC drivers on the Workflow server if they're not already installed.

1 0

replied on December 5, 2019 • Show version history

Here is my Answer: < shout out Jason Smith for this

Workflow Script is the wrong choice in trying to update SQL DB with excel.

So i created a workflow that uses excel as datasource.

Step one. Download Access Engine from this link so that you can have odbc driver for excel

ill come back and post the link when i find it ... too lazy but methodology will work.

Step two : Turn file browsing on in Workflow Admin Console

Step 3: Add excel file as Datasource. make sure to set the excel driver for odbc.

step 4: create workflow

Activity in chronological order

- Query for Data from Excel (Dont put any parameters just get the whole table)

- For each row - output of the query data

within for each row: Insert Data Activity to the Sql table you want.

Make sure the Data you add using query data token is the current Entry from the for each row not the starting entry.

This works but you gotta realize its probably gonna take you an hour to update like 8k rows HAHAH... sucks but it works.

My Next objective: Hint: win form app c# + openfiledialog + excel reader to sql db cmd library.

0 0

View 3 previous replies

replied on December 6, 2019

Are you doing this in Workflow because it's a more familiar tool or to minimize the places you have to maintain integrations?

SQL has more efficient ways for bulk inserts that can also be scheduled. At least look into switching to a Custom Query activity and distributed query if you don't need the extra tracking that comes with For Each Row activities in Workflow.

0 0

replied on December 6, 2019

I concur with Miruna, having 8,000+ rows does changes things a bit. In one of my processes I use a custom query and Bulk Insert to do a mass import.

0 0

replied on December 10, 2019

They need it to be automated and i believe you cannot schedule tasks to run the import on a daily(scheduled) using just SQL express but this particular client does not have Enterprise edition.

Tell me if im wrong. ill go searching for a way to schedule this import task using sql express

Bulk insert?? .. i see is that "insert from with" query ?

0 0

replied on December 10, 2019 • Show version history

What I mean is that within the workflow, you could use a Custom Query activity to write your own query that imports the data from the Excel file directly into SQL.

With the Custom Query, you can enter your own SQL syntax without being tied down by the limitations of the other Insert, Query, or Update activities.

BULK INSERT and OPENROWSET would probably be the best options in your case because you can write the SQL for it and drop it into a Custom Query within workflow.

Bulk insert requires CSV format, but OPENROWSET can be used with an xlsx file.

Microsoft has a page all about importing data from Excel, so all you would need is to build a valid query for your Custom Query activity in the workflow.

For example,

USE MyDatabase;
GO
INSERT * INTO My_Table
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]);
GO

You should be able to update your db\table names, columns, file path, and sheet name, then drop the query right into a workflow Custom Query.

The difference with this approach is that the file will need to be somewhere that SQL can find it, whether that be the SQL server itself or a shared/network folder.

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver15

1 0

replied on December 10, 2019

Beautiful ... let me try this

0 0

replied on May 1, 2020

Were you able to get this to work?

0 0

You are not allowed to follow up in this post.

Question

Question

Laserfiche workflow script Microsoft Office Excel cannot access file within local drive

Replies

Sign in to reply to this post.