locked
How to Convert .xlsx file to Text File with Delimitor as | using c# RRS feed

  • Question

  • User2038048451 posted

    Hi ,

    I have a Excelsheet called "sample.xlsx", in this excel I have two sheets name sheet1& sheet2.below is the data in sheet1 and sheet2.

    Sheet 1 Data:-

    Id Name City
    1 testing1 Kurnool
    2 testing2 Bangalore

    Sheet2 Data:-

    id State Country
    1 AP India
    2 Karnataka India

    from above data , from sheet1 and want to create a sheet1.txt as shown below using c# code.

    Id|Name|City
    1|testing1|Kurnool
    2|testing2|Bangalore

    and from sheet2 I want to create sheet2.txt , as shown  below using c#.

    id|state|Country

    1|AP|India

    2|Karnataka|India

    after converting these two text files i want to save these files in a folder for example c:\\test folder\\sheet1.txt and c:\\test folder\\sheet2.txt.

    how this can be achieved using c#.

    Wednesday, July 22, 2015 3:48 PM

Answers

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 22, 2015 3:52 PM
  • User-219423983 posted

    Hi nrk_hi,

    I have created a demo that you could take a look.  The following code is based on the link Rajneesh Verma provided above, you could take a look. The links above provide use a very useful method and I think you’d better read them carefully.

            public static DataSet ImportExcelToDataSet(string FilePath, string Extension, bool isHDR)
            {
                //<add name ="XLSConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
                //<add name ="XLSXConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'"/>
                string conStr = "";
                switch (Extension.ToLower())
                {
                    case ".xls": //Excel 97-03
                        conStr = ConfigurationManager.ConnectionStrings["XLSConString"].ConnectionString;
                        break;
    
                    case ".xlsx": //Excel 07 above
                        conStr = ConfigurationManager.ConnectionStrings["XLSXConString"].ConnectionString;
                        break;
                }
                if (isHDR)
                {
                    conStr = String.Format(conStr, FilePath, "NO");
                }
                else
                {
                    conStr = String.Format(conStr, FilePath, "YES");
                }
                OleDbConnection connExcel = new OleDbConnection(conStr);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataSet ds = new DataSet();
                cmdExcel.Connection = connExcel;
    
                connExcel.Open();
                System.Data.DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });//get the sheet name
    
                for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
                {
                    System.Data.DataTable dt = new System.Data.DataTable();
                    string SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
                    dt.TableName = SheetName.Substring(0, SheetName.Length - 1);
                    SheetName = ("[" + SheetName + "]");
                    cmdExcel.CommandText = "SELECT * From " + SheetName;
                    oda.SelectCommand = cmdExcel;
                    oda.Fill(dt);
                    ds.Tables.Add(dt);
                }
                connExcel.Close();
                return ds;
            }
    

    About how to get the data and write it to txt, you could refer to the following code.

                string filepath = @"C:\Users\XXX\Desktop\sample.xlsx";
                DataSet ds = ComFunctions.ImportExcelToDataSet(filepath, ".xlsx", false); //here you just export the data,not the colunm name
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    DataTable dt = ds.Tables[i];
                    string OutputPath = @"C:\Users\XXX\Desktop\" + dt.TableName + ".txt";
                    StreamWriter sw = File.CreateText(OutputPath);
                    //write the column name
                    string titleStr = string.Empty;
                    for (int ii = 0; ii < dt.Columns.Count; ii++)
                    {
                        titleStr += (dt.Columns[ii].ColumnName + "|");
                    }
                    titleStr = titleStr.Substring(0, titleStr.Length - 1);
                    sw.WriteLine(titleStr);
    
                    //write the data
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        string rowstr = string.Empty;
                        for (int k = 0; k < dt.Columns.Count; k++)
                        {
                            rowstr += (dt.Rows[j][k] + "|");
                        }
                        rowstr = rowstr.Substring(0,rowstr.Length-1);
                        sw.WriteLine(rowstr);
                    }
                    sw.Flush();
                    sw.Close();
                }
    

    I hope it’s useful to you.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 23, 2015 10:43 PM

All replies

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 22, 2015 3:52 PM
  • User-219423983 posted

    Hi nrk_hi,

    I have created a demo that you could take a look.  The following code is based on the link Rajneesh Verma provided above, you could take a look. The links above provide use a very useful method and I think you’d better read them carefully.

            public static DataSet ImportExcelToDataSet(string FilePath, string Extension, bool isHDR)
            {
                //<add name ="XLSConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
                //<add name ="XLSXConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'"/>
                string conStr = "";
                switch (Extension.ToLower())
                {
                    case ".xls": //Excel 97-03
                        conStr = ConfigurationManager.ConnectionStrings["XLSConString"].ConnectionString;
                        break;
    
                    case ".xlsx": //Excel 07 above
                        conStr = ConfigurationManager.ConnectionStrings["XLSXConString"].ConnectionString;
                        break;
                }
                if (isHDR)
                {
                    conStr = String.Format(conStr, FilePath, "NO");
                }
                else
                {
                    conStr = String.Format(conStr, FilePath, "YES");
                }
                OleDbConnection connExcel = new OleDbConnection(conStr);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataSet ds = new DataSet();
                cmdExcel.Connection = connExcel;
    
                connExcel.Open();
                System.Data.DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });//get the sheet name
    
                for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
                {
                    System.Data.DataTable dt = new System.Data.DataTable();
                    string SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
                    dt.TableName = SheetName.Substring(0, SheetName.Length - 1);
                    SheetName = ("[" + SheetName + "]");
                    cmdExcel.CommandText = "SELECT * From " + SheetName;
                    oda.SelectCommand = cmdExcel;
                    oda.Fill(dt);
                    ds.Tables.Add(dt);
                }
                connExcel.Close();
                return ds;
            }
    

    About how to get the data and write it to txt, you could refer to the following code.

                string filepath = @"C:\Users\XXX\Desktop\sample.xlsx";
                DataSet ds = ComFunctions.ImportExcelToDataSet(filepath, ".xlsx", false); //here you just export the data,not the colunm name
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    DataTable dt = ds.Tables[i];
                    string OutputPath = @"C:\Users\XXX\Desktop\" + dt.TableName + ".txt";
                    StreamWriter sw = File.CreateText(OutputPath);
                    //write the column name
                    string titleStr = string.Empty;
                    for (int ii = 0; ii < dt.Columns.Count; ii++)
                    {
                        titleStr += (dt.Columns[ii].ColumnName + "|");
                    }
                    titleStr = titleStr.Substring(0, titleStr.Length - 1);
                    sw.WriteLine(titleStr);
    
                    //write the data
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        string rowstr = string.Empty;
                        for (int k = 0; k < dt.Columns.Count; k++)
                        {
                            rowstr += (dt.Rows[j][k] + "|");
                        }
                        rowstr = rowstr.Substring(0,rowstr.Length-1);
                        sw.WriteLine(rowstr);
                    }
                    sw.Flush();
                    sw.Close();
                }
    

    I hope it’s useful to you.

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 23, 2015 10:43 PM
  • User503812343 posted

    you can read the excel data using OleDbconnection. See below code

     OleDbConnection oconn = null;
            string FilePath = "C:\\dotnetmentors\\Products.xlsx";
            oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;
                    Data Source=" + FilePath + ";
                            Extended Properties=Excel 8.0");
    
            DataTable dtCategories = new DataTable();
            oconn.Open();
            dtCategories = oconn.GetOleDbSchemaTable(
                        OleDbSchemaGuid.Tables, null);
            oconn.Close();

    Now you have your excel data in DataTable

    for more info on reading data from excel

    http://dotnetmentors.com/aspnet/display-excel-data-in-web-page.aspx

    you can simply convert DataTable to CSV

    StringBuilder sb = new StringBuilder(); 
    
    IEnumerable<string> columnNames 
            = dt.Columns.Cast<DataColumn>().
                Select(column => column.ColumnName);
    sb.AppendLine(string.Join(",", columnNames));
    
    foreach (DataRow row in dt.Rows)
    {
        IEnumerable<string> fields  
            = row.ItemArray.Select(
            field => field.ToString()); 
        sb.AppendLine(string.Join(",", fields));
    }
    
    File.WriteAllText("test.csv", sb.ToString());

    for more info on converting DataTable to CSV or List<T> object or JSON

    http://dotnetmentors.com/adonet/convert-datatable-to-csv-or-list-or-json-string.aspx

    Friday, July 24, 2015 2:06 AM