asked
missing rows using Import Wizard
HI, I am using the import Wizard to load an existing table with data from an Excel file. My first step is to export the table data to excel, add new rows, and then import the data back into the same table. I use the delete rows option on edit mapping so I do not get duplicates. On an inconsistant basis the rows that are added to the file do not upload during the import. Three is no error. I find out by double checking the number of rows in the file against the table. I have tried inserting the new rows within the data and then saving the file, resorting and saving the file again. Sometimes this works and sometimes it doesn't. Additonal info: We save the file with new data under a different name. It happens within SSIS (2005 BIDS) and on the Database (SQL 2005) Tasks menu option. It happens with both 2010 and 2003 Excel. Anybody else having this issue? Any ideas? Thanks! Paws27284
When you are exporting to the excel using the Export wizard, it creates a sheet with the name of the table, but also creates a named range with the same name. The named range then contains only the region with data. Then when you add new data, this named region is not extended. And when you are later importing the data, you probably chose the name without the $ character on end. The name without the $ in the name represents the named region, which probably doesn't contain your newly inserted data. To load all the date chose the name with $ character or extent the named region to cover all the data in the sheet.
Thanks Pavel, I never new why there was a choice with a $ sign in the list. That information is very helpful. I tried loading the data with the $ to load all data in worksheet, but I get an error. It seems the import wizard wants to load all the rows in the worksheet including the blank ones. Because I cannot have duplicate seq numbers, the task fails with "duplicates found". These duplicates being the blank rows in the worksheet. It does load the non-duplicates, but becasue the task fails, my subsequent tasks are not executed. I can easily get the user to adjust the named range, so I will probably go that route unless you have another suggestion. Thanks for your help! paws27284
