Switch Question

[strido]

Looking into getting Switch, and I've got a quick question.

A client sends us a list of files to activate via Excel Spreadsheet attached to an email. One of the fields is the SKU name (FileName) of the PDF. This includes every SKU they want, which means they'll need to be routed to different workflows based on needs.

Is it possible to leverage that email attachment in order to kick off such-and-such workflow, or will we still have a manual process? I think Switch can read the excel spreadsheet, but not so sure about it while it's an attachment to an email.

The actual files will reside on an FTP server. So I envision a scenario where Switch somehow reads that excel file, sees "File1.PDF" is included in the activation list and takes it from the FTP server and sends it through whatever workflow is required.

...thinking while writing...

Have the client send the email to a dedicated Switch SMTP account in addition to client services?

[gabrielp]

Yeah, something like that can be done with Switch. You can accept files via the email receive configurator. You would have to write a script or find an app which would parse the spreadsheet for you. Excel spreadsheets are tricky to parse, but I have done it within Switch scripts before and I'm sure others have as well. We had a discussion on here recently on the FTP configurator, but I forget if you can query and download specific files (similar to injecting) but if you can't, you could always download the contents of the FTP and just inject into them (or download it via HTTP).

So yeah, you can do it with Switch with a bit of work. But honestly, I wouldn't. What if the customer doesn't build the Excel file correctly? What if the asset that is supposed to be in the FTP is missing or has a slightly different name? It seems way too complicated and there's probably much better way for your customer to order stuff from you. If you're using Excel to make it easy for your customer, make a simple web app that he can punch in an order which generates an XML file. Switch consumes that XML file and downloads the assets directly from absolute URLs in the XML file. If you're using Excel because another system is generating it, change the system so it generates something that's easier to parse, or better yet, have that system deliver the PDF as the incoming job to really simplify things.

[strido]

I'm a bit downstream from the decisions on how we receive files, but I can certainly talk about how we get this excel file with client services. We're also hamstrung by not being able to use a web app. Maybe they can supply an XML file instead of an Excel.

I note you're from Boston. Go Sox!

[gabrielp]

If you can talk them into csv, then there's apps in the app store that readily parse csv files. If you find you need to parse the excel file, make sure you buy the scripting module and I can show you my method.

[sander]

I'm using a PowerShell script to convert incoming xlsx files to csv for use with SmartStream VDP. Or, you can pickup with Switch but I never did that with a csv.

If you run Switch on Windows, it's PowerShell, that's what you need I guess. I'm on my phone at the moment, but if you are interested I can post some details tomorrow.

[cstevens]

Just a word of caution. Excel has an export to XML option. I would avoid that if at all possible. It's difficult to parse without knowing what row/column you need in advance, and if they change the format of the spreadsheet it will most likely break your switch flow.

I've run into situations in the past where we've asked for XML instead of an Excel file and received the XML conversion which is arguably worse.