Hi I need to upload a csv or excel file which has one column (A) to an aspx page. It can read the row data one by one and insert into MSSQL DB. After the row of data inserted into MSSQL DB, it needs to write the text "Inserted" in column B of the csv or excel file.
If there is interruption such as DB connection error, those row of data had not been inserted will not marked in column B of the csv or excel file.
How should I do that?
Solution: How to read and write a csv or excel file in C#?
Depends on whether you have Excel installed on your Web server. If so, it's quite easy: - upload the file using the FileUpload control, save it in a temp location on your Web server; - start Excel from your .aspx page, load the Excel file or CSV file; - use the Excel features to read the data row by row, if the insert succeeds use the Excel code features to add the text "inserted" next to the inserted data; - have Excel save the file to its final location.
If you don't have Excel on the server, the CSV route is still quite easy: - upload the file using a FileUpload control, save it in a temp location on your Web server; - use a StreamReader for reading the file, use a StreamWriter for writing the file to its final location - read one line from the StreamReader; try to insert into the DB. - on failure, just write the line to the StreamWriter; - on success, append the text ";inserted" to the string, then write it to the StreamWriter. - repeat until the entire CSV is processed - close the StreamReader and the StreamWriter. - delete the temporary file.
If you don't want to work with a temp file, you can use the StreamReader to read in the entire CSV into a StringCollection. Then you can iterate over the StringCollection, do the database inserts, and update the strings as needed. Finally, you can write out the entire StringCollection to the original file.