OledbDataAdapter.fill gives a blank line when used to import some excel files
-
Hello everyone. I have a little application which accepts an excel file, performs a formal check on it (nothing special, it just tries to see if there's a number or a string where they should be) and then inserts the records in a database. This application loads the excel file, puts all the data in a datatable, then performs the formal check. To do so, I use an instance of OleDbDataAdapter and call the Fill(table) method to fill up a table. What is startling me the most is that with some Excel files, if I erase the last row manually (e.g. in MSExcel, clicking in the cells and pressing DEL), the row is not completely erased, so the datatable resulting from the Fill() operation has one last row empty, which is causing the formal check to fail - thus preventing the file to be loaded, since it works in an all-or-nothing basis (one rows fail, the whole file is discarded). The thing that is driving my crazy is that this happens only on SOME excel files! Even using the same Excel installation on the very same machine, some files will show this behaviour, some others won't. Has anything similar ever happened to some of you? I cannot really get a grasp of this. Thanks in advance Rey9999
~~~ From Milano to The Hague, easy as it goes ~~~
-
Hello everyone. I have a little application which accepts an excel file, performs a formal check on it (nothing special, it just tries to see if there's a number or a string where they should be) and then inserts the records in a database. This application loads the excel file, puts all the data in a datatable, then performs the formal check. To do so, I use an instance of OleDbDataAdapter and call the Fill(table) method to fill up a table. What is startling me the most is that with some Excel files, if I erase the last row manually (e.g. in MSExcel, clicking in the cells and pressing DEL), the row is not completely erased, so the datatable resulting from the Fill() operation has one last row empty, which is causing the formal check to fail - thus preventing the file to be loaded, since it works in an all-or-nothing basis (one rows fail, the whole file is discarded). The thing that is driving my crazy is that this happens only on SOME excel files! Even using the same Excel installation on the very same machine, some files will show this behaviour, some others won't. Has anything similar ever happened to some of you? I cannot really get a grasp of this. Thanks in advance Rey9999
~~~ From Milano to The Hague, easy as it goes ~~~
Hi, this reminds me of the many problems i've experienced with sql server DTS using excel files as the source. The solution is to ensure there are no blank lines at the bottom of your dataset in the spreadsheet (use Ctrl-End in excel to see if there are any blank lines). you can do this by copying the data to a new worksheet before running your app. Alternatively, you could check each row for blanks as you populate the dataTable. Rob
"An eye for an eye only ends up making the whole world blind"
-
Hi, this reminds me of the many problems i've experienced with sql server DTS using excel files as the source. The solution is to ensure there are no blank lines at the bottom of your dataset in the spreadsheet (use Ctrl-End in excel to see if there are any blank lines). you can do this by copying the data to a new worksheet before running your app. Alternatively, you could check each row for blanks as you populate the dataTable. Rob
"An eye for an eye only ends up making the whole world blind"
Thank you. Indeed, I solved the issue with a "dirty trick": after I've populated the DataTable, I check the last row and erase it if it's blank (I do not need to check them all, because the excel files they give me never have blank lines in between, apart from the last one - and that was actually causing the problem amongst my program's users) Thanks again! Rey9999
~~~ From Milano to The Hague, easy as it goes ~~~