how to read csv file record verically

Question

i want to import the following file data in database as

 Id    Name 
10001 Hemant Desai

as regular table data so how can i read this data i got some confusion in this.

here is csv file data....

ID=,10001,
Name=,Hemant Desai, 
Age=,60,
Sex=,male,
Doctor=,Pathak,
Mobile=,9021412202,
Alignment=,brain tumour,
No of medicins=,3,
12:02,Stamlo-5,1mg,oral,after meal,*XE0280916*
12:01,Atorfit-CV-10,4mg,oral,after meal,*XE0283337*
12:01,Losar,3mg,oral,after meal,*XE0284350*
12:02,Appointment,X ray of right chest at 11.00 am on Wed 11th Dec
12:01,procedure,Sponge patient with warm water
Temperature =,222
Blood Pressure =, 555/555
Pulse Rate =, 555
Respiratory Rate =, 999

and in No of medicin field i have to write all data till temperature field with placing quamma between them and remove all = signs

i tried with following code

public void import() { try { con.Open(); string sourceDir = @"directory path"; var IcsvFile = Directory.EnumerateFiles(sourceDir, "*.csv"); DataTable dt = new DataTable(); //string line1, line8;

            foreach (string currentFile in IcsvFile)
            {
                //string filename = @"FullFileNameWithPath.csv";
                //get all lines from csv file
                string[] lines = File.ReadAllLines(currentFile);
                //get only id's
                var ids = lines
                    .Where(a => a.Trim() != string.Empty && a.Contains("ID="))
                    .Select((a, x) => new { ID = Convert.ToInt32(a.Split(',')[1]), index = x });
                //get names
                var names = lines
                    .Where(a => a.Trim() != string.Empty && a.Contains("Name="))
                    .Select((a, x) => new { Name = a.Split(',')[1], index = x });
                //get patiens; join ids and names on index
                var patients = from id in ids
                               join name in names on id.index equals name.index
                               select new
                               {
                                   ID = id.ID,
                                   Name = name.Name
                               };
                foreach (var p in patients)
                {
                    Console.WriteLine("{0}\t{1}", p.ID, p.Name);
                }

                //DataTable dt = new DataTable();
                //DataRow row;

                //using (StreamReader sr = new StreamReader(currentFile))
                //{
                //    line1 = sr.ReadLine();
                //    line8 = sr.ReadLine();
                //}
            }

        }

Answer 1

As you probably know, this is not the proper way to use csv to store data. You will have to write a code that will go over each line of the csv and parse it. Start by reading the entire file into a string array by using System.IO.File.ReadAllLines(FilePath), and then analyze the strings. I would suggest using a DataTable (assuming your column names are fixed) note that you will have to check for each string in the array if it contains the = character, if it does, then what is before this char is the column name and whatever is after it as data. if it doesn't, use the last column and append the data of the entire row to it's current data.

Once completed, you should have a DataTable that contains all of the data from the 'csv' file. Then all you have to do is send it to a stored procedure that will add or update the data in the database.

Here is what I would probably do:

    private DataTable ReadFiles(string sourceDir)
    {
        var IcsvFile = Directory.EnumerateFiles(sourceDir, "*.csv");
        DataTable dt = new DataTable();
        dt.Columns.Add("Id", System.Type.GetType("System.Int32"));
        dt.Columns.Add("Name", System.Type.GetType("System.String"));
        dt.Columns.Add("Age", System.Type.GetType("System.Int32"));
        dt.Columns.Add("Sex", System.Type.GetType("System.String")); // Gender in proper english 
        dt.Columns.Add("Doctor", System.Type.GetType("System.String"));
        dt.Columns.Add("Mobile", System.Type.GetType("System.String"));
        dt.Columns.Add("Alignment", System.Type.GetType("System.String"));
        dt.Columns.Add("No of medicins", System.Type.GetType("System.String"));
        dt.Columns.Add("Temperature ", Type.GetType("System.Int32"));
        dt.Columns.Add("Blood Pressure", System.Type.GetType("System.String"));
        dt.Columns.Add("Pulse Rate", System.Type.GetType("System.String"));
        dt.Columns.Add("Respiratory Rate", System.Type.GetType("System.Int32"));
        foreach (string currentFile in IcsvFile)
        {
            ImportSingleFile(currentFile, ref dt);
        }
        return dt;
    }

    private void ImportSingleFile(string FilePath, ref DataTable dt)
    {
        string[] Lines = File.ReadAllLines(FilePath);
        string ColumnName, ColumnData;
        int EqualSignIndex, intColumnData;
        DataRow Row = null;
        ColumnName = string.Empty;
        foreach (string Line in Lines)
        {
            EqualSignIndex = Line.IndexOf("=");
            if (EqualSignIndex > -1)
            {
                ColumnName = Line.Substring(0, EqualSignIndex);
                // after = there is always , that we don't want in the data, 
                // and the line sometimes ends with a , that we also don't want
                ColumnData = Line.Substring(EqualSignIndex + 2).TrimEnd(','); 
            }
            else
            {
                ColumnData = Line;
            }

            if (ColumnName == "ID")
            {
                Row = dt.NewRow();
            }

            if (Row != null)
            {
                if(dt.Columns[ColumnName].DataType == Type.GetType("System.Int32")) {
                    if(int.TryParse(ColumnData, out intColumnData)) 
                    {
                        Row[ColumnName] = intColumnData;
                    } else {
                        throw new InvalidDataException(string.Format("For column {0} an integer value is expected", ColumnName));
                    }
                } else {
                    Row[ColumnName] = ColumnData;
                }                   
            }
        }
    }

After that you should have the data table full, and it should be very easy to insert/update the database from it.