Trying to put the final touches on a workflow I've been setting up and am running into a bit of a problem.
What I am hoping to do is as follows:
- Have an Excel spreadsheet with multiple tabs that I give to an "end user"
- Have one tab on which the end user inputs data and have another tab that has a bunch of values that are calculated based on the user input tab and some other lookups, etc.
- Have an SSIS package that imports non-blank rows from the calculated tab into a table in SQL.
The way the spreadsheet is currently set up is I have an IF check in the fields to either set them to a value or set them to nothing ('').
The problem I have is that when I do the import into SQL, the rows with '' fields are imported as empty rows, presumably because the fields aren't actually blank (they have formulas in them).
Anyone know of a way to fix / work around this? I tried changing the formulas to set the fields to #N/A instead of '', but if I do that, the import fails.
Thanks!
Best Answer
I would import the entire spreadsheet into an empty "temp" table, then run a sql statement to copy the rows I want across to my permanent table.
This way it is easy to setup rules so that you only get the rows you want and it is easy to test both the import and the copy jobs separately.