Question

Can Workflow Parse an Excel Spreadsheet?

Workflow Version 9

Updated December 4, 2019

asked on January 23, 2014

Can Workflow take an Excel spreadsheet and extract data from it's rows? For example, we would like to have a standard Excel spreadsheet that multiple people would be filling out. We would then like to have them each processed through Workflow to extract the row data and insert that data into a database. Is that possible?

1 0

Answer

SELECTED ANSWER

replied on February 5, 2014

The solution we have come up with is to have the Excel spreadsheet dropped into a specific folder on the network. We are then having SQL Import run on a schedule to import that data into an SQL database. We can then connect LF Forms and LF Workflow to the SQL database to use as we wish. Thank you for all of the suggestions.

1 0

Replies

replied on January 24, 2014

Blake,

Yes, this is possible using Workflow, as long as text has been extracted from the Excel file first. I have successfully used pattern matching to generate tokens using workflow to extract data in cells - for an expense report - for example.

FYI - we had tried using Import Agent to watch a folder for the excel file, and import into Laserfiche. That works fine, but Import Agent does not extract the text from electronic files - only TIF images.

1 0

replied on January 24, 2014

Hi,

I second Franz's reply. Also, if the appropriate iFilter has been installed you can extract the text from within the workflow.

-Ben

0 0

replied on April 22, 2014

Franz, we are looking at another case of taking data from an Excel spreadsheet using Workflow and extracting the data to insert into SQL. When you "Retrieve Document Text" from an Excel spreadsheet, how does it work when trying to take an entire row to insert into a row in SQL?

0 0

replied on January 23, 2014

It can. But I wouldn't do it in WF with the built-in activities since inserting the data from the file one row at a time would be slow (since you'd basically be opening a connection for each row, inserting the data, closing the connection and moving to the next row).

SQL Server's Import Data wizard can do it as a bulk insert and it can be scheduled. Oracle has similar tools.

0 0

replied on January 23, 2014

I should have been a little clearer. We would like the employees to submit the excel spreadsheets using forms and then have them go through a workflow. There would be a couple hundred of them, so we are looking at something a little more automated then having to setup a connection to each one. I'm going to guess from your answer that this is probably not possible?

0 0

replied on January 24, 2014

Right, if you're using the built-in activities, you'd have to set up a data source for each file. So it's not possible to go that route for attachments from Forms.

You could, however, use Retrieve Forms Content to download the file from Forms, and then a script to read from Excel, connect to SQL and insert the data. See this example.

2 0

replied on January 24, 2014 • Show version history

Adding another option on to Miruna's example,

If all you want is users to post data from excel to sql I would incorporate a script directly into the excel document the end user has open. Tie the script to a button "POST" or "SAVE" and the button can post the data, then save and close the file. Sample of posting in excel to a db connection.

This avoids having to use forms, send a file across the network, and work flow to re-open and post to sql but does rely on the end user to click the button and have macros enabled locally.

0 0

replied on January 27, 2014 • Show version history

Using a C# or VB toolkit script (or custom activity), you could save the Excel file to a local temp location, establish an odbc connection to it, read it, process it, add the values as tokens, and finally delete the temp file.

If this is an activity that is going to happen often, I might think about running WorkFlow on a dedicated machine instead of on a server with other services so it does not compete for resources or degrade other systems.

One other thought that came to mind, if the built in lookup function can use a token value in its odbc connection, you could save the excel file locally and store the path into a token...

0 0

replied on December 4, 2019

Heads up if anyone trying to write a script.. windows services doesn't allow automation on server side. basically it wont let you run microsoft office interop excel namespace to read excel.

Found out after creating it and testing it on editor. script editor will let you run the script but workflow server runs your workflows which runs on services that wont let you do it.

https://answers.laserfiche.com/questions/167448/Laserfiche-workflow-script-Microsoft-Office-Excel-cannot-access-file-within-local-drive#167468

Read more about it on this post ^^^ tells you more on why that wont work but there is a second solution to this. AKA excel odbc + custom query .. still testing that right now

0 0

You are not allowed to follow up in this post.

Question

Question

Can Workflow Parse an Excel Spreadsheet?

Answer

Replies

Sign in to reply to this post.