I am trying to use Query Excel Services in a workflow to import an Excel spreadsheet. If I specify the whole range I need to import, it seems to work great. But there are problems with this:
1. All cells are imported into one huge collection. I would need how to figure out how to parse the collection into rows (items). Not difficult but cumbersome.
2. I don't know how many rows will be in the sheet. I don't want to have to ask the user for the number of rows. I want them to simply drop the file into a folder then a scheduled workflow will do the import.
What I did was to create a workflow that imports one row at a time. I read until I hit a blank row. A good idea as it solves both issues above. But the problem is the workflow just errors out. It reads and processes the first row just fine.
When it goes to query the next row in excel, I get the error:
Error querying Excel Services. We're sorry. We ran into a problem completing your request.
Is there a better way to do this import?