You are viewing limited content. For full access, please sign in.

Question

Question

We need to import orders from a Excel sheet

Laserfiche Cloud Workflow Business Processes
Updated August 18, 2022
asked on August 4, 2022

We have a process where we receive orders via a standard Excel (always has same columns) and need to kick off a business process for each row in the spreadsheet with various data set from those rows.

We are running Laserfiche Cloud.  

Any suggestions on how to accomplish? I have already tried Lookup Tables but those can only be refreshed programmatically once an hour.  Any other ideas before writing some custom C# using the Cloud API or something like that?

Thanks!

0 0

Replies

replied on August 4, 2022

In Excel you can save as a CSV and upload to the repository, from there a workflow can read the CSV text with the Retreive Document Text activity and simply split on new line, followed by splitting on comma. This way no agents, ODBC connections, or C# code is required. Just upload the CSV and your done.

1 0
View 3 previous replies
replied on August 5, 2022

I guess what I was not clear on is that there could be 1 or 50 of these that come in on any day.  We have a Client Order Form that allows the file to be uploaded with some supplemental information.   I guess if they uploaded a CSV file instead I could move forward with your suggestion.  For the moment I ended up with a multi-line field and are pasting the tab delimited text that comes from Excel when you copy/paste excel contents into a text field.  Then I'm leveraging some regular expression magic to parse that data.  Forms makes a bit more challenging because it decides to alter that data from what is pasted into the text field but so far it seems to be working.

 

0 0
replied on August 5, 2022

Why not just upload the CSV file and read from the document text? Having them copy and paste is overkill and prone to error.

0 0
replied on August 6, 2022

Only reason is I can think of is the number of steps.  They get excel so they would have to open sheet, go through options of saving as csv and then navigating to the appropriate folder to drag the file onto the business process form versus just opening sheet, hit ctrl-a and then ctrl-c then click on order form and press ctrl-v.   

Or is there a way within Laserfiche to get the text in tsv or csv format while the sheet is saved as Excel?

 

0 0
replied on August 8, 2022

The Excel file type must be read with the Excel driver, it is not a text format.

You could write a program to batch convert excel files to csv

1 0
replied on August 8, 2022

Yeah, I'm going to second Chad's suggestion to programmatically convert Excel to CSV and then read the CSVs so all users need to do is upload the Excel doc.

You'll need a Remote Agent and one or more Script Rules that run on it. Use whatever Starting Events make sense, have the Remote Agent script pull down the .xlsx, convert to CSV via script method of your choice, upload the result file back to your LF Cloud repository, and then have Workflow read the text.

One other possible route is to upload the Excel spreadsheets to Lookup Tables (which can take .xlsx directly), potentially using the Replace Lookup Table Data activity to automate some portions.

0 0
replied on August 18, 2022

I'll look into that Remote Agent/Script Rules combination.  That would be the only way to streamline this process for my client.  If I understand what you guys are thinking, they upload the excel sheet via a form.  The form saves it to the repository and then workflow can call what it needs in regards to the remote agent/script to grab the uploaded excel, convert it to CSV and then do the processing I'm currently doing.

I wish I could use Replace Lookup Table Data.  Unfortunately, that is not an option because there is a limitation of only updating the table once an hour in Cloud.  If that weren't a limitation, it would be a perfect solution because you can do that in Workflow, and I would be able to just "single-thread" all the orders so they would be processed in sequence.

0 0
replied on August 4, 2022

Have you looked at Bots? You can have them run through the spreadsheet and start a form for each row.

0 0
replied on August 5, 2022

The bot would run on a machine where the spreadsheet does not exist, right?  How would I have it do that?

0 0
replied on August 18, 2022

No, the bot would run on a local machine in your network.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...