I have a situation where I am receiving an Excel spreadsheet from an external source. In this spreadsheet, the data contained in each row may be a duplicate of a previous row already processed. Even though the rows are duplicates, the data is still valid and must be treated as a separate entry. This data deals with financial data from multiple groups and I cannot get the sending party to insert any more data to make the rows unique.
Before anyone jumps on me about doing what I'm doing.... I realize this is not the ideal situation to import into a database because it is just basically emulating flat files with no primary key and no possibility of defining a unique index. However, the amount of data from these Excel spreadsheets is minimal and I need to be able store the data in a central place for archival and reporting purposes.
I want to be able to insert this data into a database and also determine if the row from the spreadsheet has been previously processed. Since I have no unique values to create a valid primary key, I was thinking of keeping track of rows processed in the spreadsheet.
I would like to be able to open the spreadsheet and determine if it contains a column called 'Processed'. If the column does not exist, I want to add the column to the spreadsheet and save it back to the server. If the column exists, then check the value to see if it contains a 'Y' indicating it has already been processed and move on to the next row. If the column is null (not processed), process the row and then update the 'Processed' column 'Y'. After processing all rows in the spreadsheet I want to be able to re-write the Excel spreadsheet back to the server.
I began coding using OLE DB methods to read and process the data and have some code already written but I did not have the complete picture of the data before I began. I would now like to prevent processing rows that have already been processed from being re-processed using the approach described above.
I am using Visual Studio .Net 2008 and C#. Also, this is a web application and the files are stored on the web server. Can anyone provide me with a preferred method of accomplishing the above and maybe a few code snippets? Any help would be greatly appreciated.
Thanks,
Jon