locked
How to read csv file containing many special characters? RRS feed

  • Question

  • here is the csv file::::::

    PTNAME,REGNO/ID,BLOOD GRP,WARD NAME,DOC NAME,XRAY,PATHO,MEDICATION,BLOOD GIVEN
    Mr.MISHRA SABHAPRASAD RAMNARESH,SH1312/00804,,SEMI DELUXE 05,SHELKE SAMEER,"X RAY LEFT HIP WITH THIGH AP/LAT --ACCEPTABLE WITH IMPLANT IN SITU WITH ACETABULAR CUP ARTHRITIC CHANGES 2 D ECHO-MILD CONC LVH GOOD LV DIASTOLIC FUNCTION ALTERED LV DIASTOLIC FUNCTION LVEF 55 % MRI BRAIN- FEW OLD ISCHEMIC CHNGES IN BILATERAL CEREBRAL WHITE MATTER MRI L-S SPINE WITH SCREENING OF WHOLE SPINE- PID-L3-4,L5-S1 PID C3- 4TO C6-7 ",HB- 11.4 BSL -206.4 SR CREAT-1.7 T3-0.74 T4-11.0 TSH-1.79 SR UREA-23 BLOOD GROUP- B RH POSITIVE PT INR-15/15/1 HIV AND HBSAG - NEGATIVE, IV DICLOGESIC RR DRIP 1-0-1 TAB TACIL 1-0-1 TAB ARCOPAN D 1-0-1 CAP GEMCAL PLUS 1 -0-1 TAB ANXIT 0.5 MG 0-0-1 ARCIZEN GEL LA 1-1-1-1 ,I POINT PCV GIVEN ON 6/4/2015 B RH POSITIVE

    in this file from PTNAME to BLOOD GIVEN is headers and file is in proper format. i want to read this file and save in sql server database table. my simple import method doesnt work for this file i need to add some lines in my code bt im confused how to do that .

    here is my simple import method if anyone can just edit my method and write code to read and save data in table.

    public void ImportAllFilesOfFolder()//function declares methods to import//
    {
    try
    {
    SqlConnection=new SqlConnection(Properties.Settings.Default.HospitalProjectConnectionString);
    con.Open();
    string sourceDir = txtsend.Text;
    var IcsvFile = Directory.EnumerateFiles(sourceDir, "*.csv");

    foreach (string currentFile in IcsvFile)
    {
    StreamReader sr = new StreamReader(currentFile);
    string line = sr.ReadLine();
    string[] value = line.Split(',');
    DataTable dt = new DataTable();
    DataRow row;

    foreach (string dc in value)
    {
    dt.Columns.Add(new DataColumn(dc));
    }

    while (!sr.EndOfStream)
    {
    value = sr.ReadLine().Split(',');
    if (value.Length == dt.Columns.Count)
    {
    row = dt.NewRow();
    row.ItemArray = value;
    dt.Rows.Add(row);
    }
    }

    SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
    bc.DestinationTableName = "New";
    bc.BatchSize = dt.Rows.Count;
    bc.WriteToServer(dt);
    bc.Close();
    }
    }

    catch
    {

    }
    finally { con.Close(); }
    }
    Tuesday, April 21, 2015 9:42 AM

Answers

  • no this is sample data this is not original data. but i want to build method to read file like this format.

    can u just give me link for exact use of texfieldparser.

    i did all triels but not got perfect answer . im very new to c#

    Hi member,

    About texfieldparserClass.  Right-click the Reference, click Add Reference, select Microsoft.VisualBasic, and click OK button.

    The following is a sample code about import CSV file into DataTable C#

    using System;
    using System.Data;
    using Microsoft.VisualBasic.FileIO;
    
    namespace ReadDataFromCSVFile
      {
        static class Program
          {
            static void Main()
            {
                string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";
                DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
                Console.WriteLine("Rows count:" + csvData.Rows.Count);            
                Console.ReadLine();
            }
        private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
            {
                DataTable csvData = new DataTable();
                try
                {
                  using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                     {
                        csvReader.SetDelimiters(new string[] { "," });
                        csvReader.HasFieldsEnclosedInQuotes = true;
                        string[] colFields = csvReader.ReadFields();
                        foreach (string column in colFields)
                        {
                            DataColumn datecolumn = new DataColumn(column);
                            datecolumn.AllowDBNull = true;
                            csvData.Columns.Add(datecolumn);
                        }
                        while (!csvReader.EndOfData)
                        {
                            string[] fieldData = csvReader.ReadFields();
                            //Making empty value as null
                            for (int i = 0; i < fieldData.Length; i++)
                            {
                                if (fieldData[i] == "")
                                {
                                    fieldData[i] = null;
                                }
                            }
                            csvData.Rows.Add(fieldData);
                        }
                    }
                }
                catch (Exception ex)
                {
                }
                return csvData;
            }
          }
        }

    You can import CSV File into C# DataTable using the function ReadDataFromCSVFile which is available in above. Now you can pass this databale object to the following
    function to insert into SQL Server using SQL Bulk Copy. And I have tested on my side, it works fine.

               // Copy the DataTable to SQL Server using SqlBulkCopy
    function static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
    {
       using(SqlConnection dbConnection = new SqlConnection("your connectionstring"))
            {
              dbConnection.Open();
              using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = "Your table name";
                    foreach (var column in csvFileData.Columns)
                    s.ColumnMappings.Add(column.ToString(), column.ToString());
    
                    s.WriteToServer(csvFileData);
                 }
             }
      }

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    • Marked as answer by Fred Bao Wednesday, May 6, 2015 9:42 AM
    • Edited by Kristin Xie Wednesday, May 6, 2015 9:43 AM
    Wednesday, April 22, 2015 7:51 AM

All replies

  • [Is that real data?  Did you just post a real person's medical information on the internet without anonymising it first? If so, then, that's not cool.]

    Regardless, there's a class called TextFieldParser that is useful for reading CSV data easily.

    FYI: Although it happens to be in a VisualBasic namespace and assembly, it really has nothing to do with Visual Basic.  You can use it from C# just fine just like any other .NET class.  Just add a reference to the appropriate assembly, which is Microsoft.VisualBasic (in Microsoft.VisualBasic.dll).

    Tuesday, April 21, 2015 1:11 PM
  • no this is sample data this is not original data. but i want to build method to read file like this format.

    can u just give me link for exact use of texfieldparser.

    i did all triels but not got perfect answer . im very new to c#

    Wednesday, April 22, 2015 6:07 AM
  • no this is sample data this is not original data. but i want to build method to read file like this format.

    can u just give me link for exact use of texfieldparser.

    i did all triels but not got perfect answer . im very new to c#

    Hi member,

    About texfieldparserClass.  Right-click the Reference, click Add Reference, select Microsoft.VisualBasic, and click OK button.

    The following is a sample code about import CSV file into DataTable C#

    using System;
    using System.Data;
    using Microsoft.VisualBasic.FileIO;
    
    namespace ReadDataFromCSVFile
      {
        static class Program
          {
            static void Main()
            {
                string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";
                DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
                Console.WriteLine("Rows count:" + csvData.Rows.Count);            
                Console.ReadLine();
            }
        private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
            {
                DataTable csvData = new DataTable();
                try
                {
                  using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                     {
                        csvReader.SetDelimiters(new string[] { "," });
                        csvReader.HasFieldsEnclosedInQuotes = true;
                        string[] colFields = csvReader.ReadFields();
                        foreach (string column in colFields)
                        {
                            DataColumn datecolumn = new DataColumn(column);
                            datecolumn.AllowDBNull = true;
                            csvData.Columns.Add(datecolumn);
                        }
                        while (!csvReader.EndOfData)
                        {
                            string[] fieldData = csvReader.ReadFields();
                            //Making empty value as null
                            for (int i = 0; i < fieldData.Length; i++)
                            {
                                if (fieldData[i] == "")
                                {
                                    fieldData[i] = null;
                                }
                            }
                            csvData.Rows.Add(fieldData);
                        }
                    }
                }
                catch (Exception ex)
                {
                }
                return csvData;
            }
          }
        }

    You can import CSV File into C# DataTable using the function ReadDataFromCSVFile which is available in above. Now you can pass this databale object to the following
    function to insert into SQL Server using SQL Bulk Copy. And I have tested on my side, it works fine.

               // Copy the DataTable to SQL Server using SqlBulkCopy
    function static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
    {
       using(SqlConnection dbConnection = new SqlConnection("your connectionstring"))
            {
              dbConnection.Open();
              using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = "Your table name";
                    foreach (var column in csvFileData.Columns)
                    s.ColumnMappings.Add(column.ToString(), column.ToString());
    
                    s.WriteToServer(csvFileData);
                 }
             }
      }

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    • Marked as answer by Fred Bao Wednesday, May 6, 2015 9:42 AM
    • Edited by Kristin Xie Wednesday, May 6, 2015 9:43 AM
    Wednesday, April 22, 2015 7:51 AM