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

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#.

Answer 1

User1992938117 posted

how this can be achieved using c#.

Checkout below links:

https://bytescout.com/products/developer/spreadsheetsdk/read-write-excel.html 

http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx 

Answer 2

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

Answer 3

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