You must Sign In to post a response.
  • Category: [Competition Entries]

    How to read csv file in vertical format and put into table in horizontal format

    Are you looking for a way to read csv file in vertical format and put into table in horizontal format ? then read this thread to know how to do it

    Hey friends i am stuck while doing this . i want to import the following csv file and save in database table into horizontal format and in line8 there is "No of medicins" and it contains many values and i want to store all that values in No of medicins column . i think i have to remove "," while reading this line and "=" also remove while importing . but im go through all stuffs but not find working answer. please help me . thnks

    here is the file>>>>>

    Name=,Hemant Desai,
    Alignment=,brain toumer,
    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
  • #757880
    When you say .CSV file it is a comma separated values after your 'no of medicines' columns there are different values separated by comma and all are then converted to different columns,
    In my opinion, first you need to remove all comma's and then read each line of file using simple textReader and split your line with '=', it will gives you proper values to insert to table

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #757882
    I can't remove quammas this format is fixed can we do to skip splitting after no of medicine and again start at temperature by comparing string .

  • #757890
    i am tried with this code following...

    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(',');
    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;
    throw new InvalidDataException(string.Format("For column {0} an integer value is expected", ColumnName));
    Row[ColumnName] = ColumnData;

    but it uses user defined table valued type to store data in table through stored procedure. bt im using sql server 2008 and there is no option for creating type so i don't know this code running or not .

    plz help me to sort this.

  • Sign In to post your comments