In my previous article http://davidz.afriklink.com/post/Creating-and-Deploying-a-dtsx-package-to-upload-CSVxcl-in-Database.aspx
I encounter a problem where the system generating csv file create some files with empty cell.As you know it is hard inserting empty data to an integer field in a database. I did not want to change my emailId that is Integer in the table to be a varchar for example.
So my solution was to create another function that will get all the files that was rejected by the dtsx package and read them line by line
private void ErrorCorrection(Settings setting) //setting has all my configuration meaning directories for all files
{
string[] files = Directory.GetFiles(setting.ErrorFolder, @"*.csv", SearchOption.TopDirectoryOnly); //Loop through all csv files error s ones
for (int i = 0; i < files.Length; i++) foreach (string errorFile in _logFilesError) // Log exist
{
_logFilesError.Add(files[i]);
}
{
if (!File.Exists(errorFile)) continue;
var fileInfo = new FileInfo(errorFile);
string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileInfo.DirectoryName + ";Extended Properties='Text;HDR=Yes;FMT=CSVDelimited'";
OleDbConnection cn = new OleDbConnection(conn);
OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM [" + fileInfo.Name + "]"
, cn); OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
cn.Open();
var properties = (from r in
dt.AsEnumerable()
where r[3].ToString() != string.Empty && r[4].ToString() != string.Empty select r);
foreach (var data in properties) //
{
TESTCSVdAO dao = new TESTCSVdAO();
int.Parse(data[2].ToString()), int.Parse(data[3].ToString()),
int.Parse(data[4].ToString()));
////move to archive folder csv folder
FileInfo fi = new FileInfo(errorFile.ToString());
string destination = setting.Archive;
}
}
###########Linq Action ########## we can accomplish the sAME with LINQ to CSV
string[] csvlines = File.ReadAllLines(@errorFile);
var query = from csvline in csvlines let data = csvline.Split(',')
select new
{
type = data[0],dsnStatus = data[1],batchid = data[2],EmailId = data[3],
pBatchid = data[3]};
var result = from q in
query where q.batchid != null || q.pBatchid != null select q;
foreach (var r in result)
TESTCSVdAO dao = new TESTCSVdAO();
int.Parse(r.batchid), int.Parse(r.EmailId), int.Parse(r.pBatchid));
dao.GetSumaryCampaign(r.type, r.dsnStatus,
{
}
dao.GetSumaryCampaign(data[0].ToString(), data[1].ToString(),
da.Fill(dt);