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

Question

Question

We need to import orders from a Excel sheet

asked on August 4

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

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
replied on August 5

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

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

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

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

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 4

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

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

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

Sign in to reply to this post.