September 20, 2011 at 8:29 am
Hi
We're developing a system whereby data gets exported to a spreadsheet, users can add/insert/delete rows, then the data gets reimported.
The problem I am having is that if a user ADDS a row to the spreadsheet it does not appear to be imported, it's as though the row isn't there.
September 20, 2011 at 8:38 am
One of our developers started down that same road, because we had so many users familiar with Excel, but we ended up switching to a web page instead;
we had more control over good/bad data edits, and was a lot easier to manage in the postback of the page...we could bounce the data right back to the user upon submission to fix mistakes, missing but required data, etc.
Lowell
September 20, 2011 at 8:39 am
Lowell (9/20/2011)
One of our developers started down that same road, because we had so many users familiar with Excel, but we ended up switching to a web page instead;we had more control over good/bad data edits, and was a lot easier to manage in the postback of the page...we could bounce the data right back to the user upon submission to fix mistakes, missing but required data, etc.
Agreed.
A simple access data project is also a good candidate here. Performance is good and dev is faster.
September 20, 2011 at 8:59 am
Lowell (9/20/2011)
One of our developers started down that same road, because we had so many users familiar with Excel, but we ended up switching to a web page instead;we had more control over good/bad data edits, and was a lot easier to manage in the postback of the page...we could bounce the data right back to the user upon submission to fix mistakes, missing but required data, etc.
Not an option I'm afraid.
September 20, 2011 at 9:03 am
it could be a number of things,
in the data source, are you accessing Excel directly or have you used a query with a specified range that may need extending?
the data types they are using for the new rows may be inconsitent with the previous rows so excel will happily ignore them.
There may also be a where clause somewhere your package
I would put a data view in the data flow to see if the rows are getting missed at the source or dropped somewhere else in the data flow.
September 20, 2011 at 9:05 am
Cowboy DBA (9/20/2011)
Lowell (9/20/2011)
One of our developers started down that same road, because we had so many users familiar with Excel, but we ended up switching to a web page instead;we had more control over good/bad data edits, and was a lot easier to manage in the postback of the page...we could bounce the data right back to the user upon submission to fix mistakes, missing but required data, etc.
Not an option I'm afraid.
What are your limitations?
September 20, 2011 at 9:44 am
Seems to have been sorted. In the Excel source editor I changed the table source from the worksheet name to worksheet name $
So from "SourceSheet" to "SourceSheet$" and it worked as expected 🙂
Any ideas what the essential difference it between the two?
September 20, 2011 at 9:47 am
That's how SQL makes a linked connection to excel. Never tried to figure out why.
I'm guessing it needs a terminator.
September 20, 2011 at 9:49 am
Cowboy DBA (9/20/2011)
Seems to have been sorted. In the Excel source editor I changed the table source from the worksheet name to worksheet name $So from "SourceSheet" to "SourceSheet$" and it worked as expected 🙂
Any ideas what the essential difference it between the two?
"SourceSheet" would be refering to a named range of cells, so I guess the new rows were not part of the named range and were ignored
"SourceSheet$" refers to the entire worksheet
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply