filter excel.import()?

May 8, 2014 at 5:01 AM
I'm looking for a way to filter the excel import. I'm trying to simplify this for users, they have exported excel sheets from another app that need imported. The file has a number of blank lines at the end that the imported brings in as nulls. Sometimes the data is only a few lines but then after the import there are 500 null lines.

i've tried importing the sheet, then iterating through the data removing the nulls, but the importer runs concurrently so:

excel.import(me.table1, map)
function_loop_and_delete_nulls

runs the function before the datagrid is populated, so it basically does nothing.

The other annoyance is that it takes a long time to load 1000 null rows into the datagrid.

thirdly, because of the concurrent running, I can't trigger a save, or the function, a refresh, anything to happen after the excel import.


I would also add that there is often duplicate data, it would be handy if the importer could handle this. I'm handling it in t-sql though so I can live for the moment.

Thanks
May 28, 2014 at 7:39 AM
Actually, the Excel.Import() methods should provide an overload which would return an IEnumerable<T>, a list of objects of the developers choice.
Thus, a signature similar to this would be useful: IEnumerable<T> Excel.Import<T>(List<ColumnMapping> mappings) where T : class
Type T would provide properties with names defined in mappings and thus an import from Excel would generate a collection of objects (POCO) in memory. Then you could filter any way you want and eventually store the data to a LightSwitch table.