Search This Blog

Thursday, January 10, 2013

Missing records in CSV file while importing from DB(C# code)

Yesterday I faced a very strange problem in CSV files the data was mismatching from the db.
I have a small application which is used to pull data from db and write into a flat file csv . In other words its import data from db to CSV file.  All of sudden yesterday user has raised a P1 tickets regarding the data in the csv file is missing some data is not found in the csv my colleague check is that data present in the db or not but in the query result it was available and we find that the record count in the db and csv is miss matching almost 700 records are not present in csv files.  They have done all their effort  but  still they are not able to find out where actually the problem is ? Then it is come to me. And I have started debugging of code and checking every condition very carefully after a couple of hours I find that everything s are fine and record writing in the file is absolutely same.  But still I have open the csv file the records are missing and getting the same value as they are getting. I surprised to see this  then I have change the extension of the csv file to txt and open it with excel now what I see that record count is absolutely fine and matching with db. I surprised in normal cases in csv file data increase but this time data is missing. I made changes in the query and replace all commas with blank space but still the problem is same. I have also made some other changes like changing the delimiter “,” to “|” its solve the issue but I am not able to separate the columns as in excel.  After couple of hourse struggle and analyzing the data carefully I found that in some date there was some  “(double quotes)  and that’s why when in csv file all the data between the “ ”  treated as one record. After remove the “ quotes from data It solved the problem.
Hope my experience may guide you in such kind of problem.
Happy learning