Remove/Replace comma from CSV file when using entity framework?

Question

I have come across this issue and I can't solve it and it's driving me insane. I've created an C# app that reads a csv file, then inserts the information into MSSQL. When I do this though, I'm getting some skewed inserts. Everything inserts fine until there is a comma within double quotes.. for example, if the Bank Name is "Capital One,Inc" it will insert 'Inc' into the next column. I have tried removing the comma's, but I'm getting the same results. I am using the entity framework to do this.. this is how my dataclass is set up.

namespace CIT
{
    class RawData
    {
        public string Branch { get; set; }
        public string Pfx { get; set; }
        public string AcctNo { get; set; }
        public string CustName { get; set; }
        public string CtrlNo { get; set; }
        public string RefNo { get; set; }
        public string Days { get; set; }
        public string DealNoCat { get; set; }
        public string BankNameFiCat { get; set; }
        public string FinMgrName { get; set; }
        public string Desc { get; set; }
        public string DealDateCat { get; set; }
        public string SchedNo { get; set; }
        public string SchedTypeDv { get; set; }
        public string SchedRemarks { get; set; }
        public string Amt { get; set; }
        public string Src { get; set; }
        public string SysDateCat { get; set; }
        public string JrnlDateDv { get; set; }
        public string DateY4 { get; set; }
        public string DaysOut { get; set; }

        public RawData(string csvString)
        {
            string[] citData = csvString.Replace(", ", " ").Replace(".", " ").Split(',');
            try
            {
                Branch = citData[0].Replace("\"", "");
                Pfx = citData[1].Replace("\"", "");
                AcctNo = citData[2].Replace("\"", "");
                CustName = citData[3].Replace("\"", "");
                CtrlNo = citData[4].Replace("\"", "");
                RefNo = citData[5].Replace("\"", "");
                Days = citData[6].Replace("\"", "");
                DealNoCat = citData[7].Replace("\"", "");
                BankNameFiCat = citData[8].Replace("\"", "");
                FinMgrName = citData[9].Replace("\"", "");
                Desc = citData[10].Replace("\"", "");
                DealDateCat = citData[11].Replace("\"", "");
                SchedNo = citData[12].Replace("\"", "");
                SchedTypeDv = citData[13].Replace("\"", "");
                SchedRemarks = citData[14].Replace("\"", "");
                Amt = citData[15].Replace("\"", "");
                Src = citData[16].Replace("\"", "");
                SysDateCat = citData[17].Replace("\"", "");
                JrnlDateDv = citData[18].Replace("\"", "");
                DateY4 = citData[19].Replace("\"", "");
                DaysOut = null;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Something went wrong. " + ex.ToString());
            }
        }
    }
}

I have also tried

BankNameFiCat = citData[8].Replace(",", " ").Replace("\"", "");

but I have no had any luck with that. I think the problem is that the comma has no spaces before or after it. It is like "Capital One,Inc" and it's like that with other bank names as well.

This is how I'm uploading the file to the list then writing it to the db..

string text = File.ReadAllText(lblFileName.Text);
string[] lines = text.Split('\n');
int total = 0, reduced = 0;
foreach (string line in lines)
{
   RawData temp = new RawData(line);
   total++;
   if (!(temp.Branch.Length == 0 || temp.Branch == "Branch"))
   {
          reduced++;
          data.Add(temp);
   }
 }

Linq/Entity

foreach (RawData rData in data)
{

    tFIManager fimanag;
    tBank bank;
    tCustomer cust;

    fimanag = (context.tFIManagers.Any(fimanager => fimanager.FIName == rData.FinMgrName) ? context.tFIManagers.Where(fimanager => fimanager.FIName == rData.FinMgrName).FirstOrDefault() : context.tFIManagers.Add(new tFIManager { FIName = rData.FinMgrName }));

    bank = (context.tBanks.Any(banks => banks.BankName == rData.BankNameFiCat) ? context.tBanks.Where(banks => banks.BankName == rData.BankNameFiCat).FirstOrDefault() : context.tBanks.Add(new tBank { BankName = rData.BankNameFiCat }));

    cust = (context.tCustomers.Any(custs => custs.CustomerName == rData.CustName) ? context.tCustomers.Where(custs => custs.CustomerName == rData.CustName).FirstOrDefault() : context.tCustomers.Add(new tCustomer { CustomerName = rData.CustName }));

    DateTime DateY4, DealDate, SysDate, JrnlDate;
    bool hasDate = DateTime.TryParse(rData.DateY4, out DateY4);
    bool hasDeal = DateTime.TryParse(rData.DealDateCat, out DealDate);
    bool hasSys = DateTime.TryParse(rData.SysDateCat, out SysDate);
    bool hasJrnl = DateTime.TryParse(rData.JrnlDateDv, out JrnlDate);

    decimal amt;
    bool hasAmt = Decimal.TryParse(rData.Amt, out amt);

    tContractsInTransit cit = new tContractsInTransit
    {
        Branch = rData.Branch,
        Pfx = rData.Pfx,
        AcctNo = rData.AcctNo,
        CustomerID = cust.CustomerID,
        CtrlNo = rData.CtrlNo,
        RefNo = rData.RefNo,
        Days = rData.Days,
        DealNoCat = rData.DealNoCat,
        BankID = bank.BankID,
        FIManagerID = fimanag.FIManagerID,
        Desc = rData.Desc,
        DealDateCat = null,
        SchedNo = rData.SchedNo,
        SchedTypeDv = rData.SchedTypeDv,
        SchedRemarks = rData.SchedRemarks,
        Amt = hasAmt ? amt : 0,
        Src = rData.Src,
        SysDateCat = null,
        JrnlDateDv = null,
        DateY4 = null
    };

   if (hasDeal)
   {
       cit.DealDateCat = DealDate;
   }
   if (hasSys)
   {
       cit.SysDateCat = SysDate;
   }
   if (hasJrnl)
   {
       cit.JrnlDateDv = JrnlDate;
   }

   if (hasDate)
   {
       cit.DateY4 = DateY4;
   }

   context.tContractsInTransits.Add(cit);
   context.SaveChanges();
}

context.Dispose();

I'm not sure what else to try.. I probably am just tired of looking at it at this point. Thanks in advance!

They used to be but now it's only when the value contains a comma. — Humpy, Aug 20 '14 at 20:47
Is the comma used as a separator as well? If so, then there's a problem with the supplier of the CSV file, by using the separator inside the intrinsec values as well. Either replace separator or remove the separator from within the values and replace it with something else. — Alex Barac, Aug 20 '14 at 20:49
Instead of trying to roll your own CSV parser I would add a reference to VisualBasic (I know a cardinal sin to C# developers) and use the TextFieldParser class. It will take care of most of what you want. — CharlesNRice, Aug 20 '14 at 20:52

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

Just find commas in quotes and replace them prior to doing anything else. You could probably use the code from one of the answers in this question.

edited May 23 '17 at 12:05

Community

1
1

answered Aug 20 '14 at 22:15

Kit

20,354
4
60
103

T.S. · Answer 2 · 2015-01-02T18:26:42.013

Use Microsoft.ACE.OLEDB.12.0 to read Excel file. This should solve your problem.

public static DataSet ReadFile(string file)
    {

        string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='text;HDR=YES;FMT=Delimited';";

        string sql = "select * from " + name;

        DataSet ds = null;
        Using (OleDbConnection conn = new OleDbConnection(connstring))
        {
            conn.Open();
            using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strSql, connstring))
            {
                ds = new DataSet();
                myCommand.Fill(ds, "table1");
            } 
        }

        return ds;
    }

Remove/Replace comma from CSV file when using entity framework?

2 Answers2