Convert excel to text format using delimiters

Question

0.00/5 (No votes)

See more:

Hi
I need to convert Excel into text format using delimiters.
I have coded using Interop. But it takes only 254 columns. I am using 2007 excel format.
I have 400 columns in Excel. Oledb takes only 254 columns while reading Excel

Please anyone help me how to convert Excel into text

What I have tried:

tring excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;';";
                        OleDbConnection excelConn = new OleDbConnection(excelConnStr);
                        excelConn.Open();
                        DataTable dbSchema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        string strsheetName = "Sheet1$";
                        if (dbSchema != null && dbSchema.Rows.Count > 0)
                        {
                            strsheetName = Convert.ToString(dbSchema.Rows[0]["TABLE_NAME"]);
                        }




                        System.Data.DataTable dtPatterns = new System.Data.DataTable();
                      excelCommand = new OleDbComnd("SELECT * FROM [" + strsheetName + " ]", excelConn);
                        
        
                        excelDataAdapter.Fill(dtPatterns);
                       
                        List<string> xcelData = new List<string>();
                        List<string> ColumnNames = new List<string>();
                        ds.Tables.Add(dtPatterns);
                        totalRowsCount = dtPatterns.Rows.Count;
                        totalColumnsCount = dtPatterns.Columns.Count;

                        this.lblRowsCount.Invoke((MethodInvoker)delegate { this.lblRowsCount.Text = totalRowsCount.ToString(); });
                       
                        for (int c = 0; c < dtPatterns.Columns.Count; c++)
                        {
                            if (c == dtPatterns.Columns.Count - 1)
                            {
                                SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString());
                            }
                            else
                            {
                                SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString() + sValue.ToString());
                            }
                        }
                        SB1.Append("\r\n");
                        for (int d = 0; d < dtPatterns.Rows.Count; d++)
                        {
                            this.toolStripStatusLabel1.Text = string.Format("Processing Please Wait ...{0} of {1}", (count + 1), totalRowsCount);
                            for (int c = 0; c < dtPatterns.Columns.Count; c++)
                            {
                                int g = dtPatterns.Columns.Count - 1;
                                if (c == g)
                                {
                                    SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString());
                                }
                                else
                                {
                                    SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString() + sValue.ToString());
                                }
                            }
                            SB1.Append("\r\n");
                            count++;
                        }

Posted 20-Nov-17 18:00pm

SukirtiShetty

Updated 20-Nov-17 20:23pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-11-20T20:23:00

Solution 1

It's a provider limit for the sheet - see here: Import data from Excel File into a dataTable: column limit?[^]

Quote:
Yes I have tried to merge two datasets but its merging not as columns.

The Merge method doesn't add columns: it adds table rows together where the table schema is the same. That isn't the case for you: you need to add columns.
Try this:

C#

public static DataTable MergeTables(DataTable t1, DataTable t2)
    {
    // Build combined table columns
    DataTable merged = t1.Clone();                  // Include all columns from t1 in result.
    foreach (DataColumn col in t2.Columns)
        {
        string newColumnName = col.ColumnName;
        merged.Columns.Add(newColumnName, col.DataType);
        }
    // Add all rows from both tables
    var mergedRows = t1.AsEnumerable().Zip(t2.AsEnumerable(), (r1, r2) => r1.ItemArray.Concat(r2.ItemArray).ToArray());
    foreach (object[] rowFields in mergedRows)
        {
        merged.Rows.Add(rowFields);
        }
    return merged;
    }

It's pretty obvious how to use it:

C#

DataTable dt1 = new DataTable();
dt1.Columns.Add("ID", typeof(int));
dt1.Columns.Add("Name", typeof(string));
dt1.Rows.Add(1, "Mike");
dt1.Rows.Add(2, "Susan");
var dt2 = new DataTable();
dt2.Columns.Add("Country", typeof(string));
dt2.Rows.Add("UK");
dt2.Rows.Add("France");

DataTable dtMerged = MergeTables(dt1, dt2);

Posted 20-Nov-17 20:23pm

OriginalGriff

Updated 20-Nov-17 22:04pm

v3

Comments

SukirtiShetty 21-Nov-17 2:27am

Yes I have tried to merge two datasets but its merging not as columns.

For Ex:
MY DS1:
1 2
3 4

DS2:
5 6
7 8

I want output like below
1 2 5 6
3 4 7 8

But i m getting
1 2
3 4
5 6
7 8

OriginalGriff 21-Nov-17 2:38am

And how am I supposed to know how you tried to merge them? I can't see your screen, access your HDD, or read your mind!

SukirtiShetty 21-Nov-17 3:20am

System.Data.DataTable dtPatterns = new System.Data.DataTable();

excelCommand = new OleDbCommand("SELECT * FROM [Sheet1$A:Z]", excelConn);
excelDataAdapter.SelectCommand = excelCommand;
excelDataAdapter.Fill(dtPatterns);

List<string> xcelData = new List<string>();
List<string> ColumnNames = new List<string>();
ds.Tables.Add(dtPatterns);

System.Data.DataTable dtPatterns1 = new System.Data.DataTable();

excelCommand = new OleDbCommand("SELECT * FROM [Sheet1$AA:KZ]", excelConn);
excelDataAdapter.SelectCommand = excelCommand;
excelDataAdapter.Fill(dtPatterns1);

List<string> xcelData1 = new List<string>();
List<string> ColumnNames1 = new List<string>();
ds1.Tables.Add(dtPatterns1);
dtPatterns.Merge(dtPatterns1);

OriginalGriff 21-Nov-17 4:04am

Answer updated.

SukirtiShetty 21-Nov-17 5:50am

Thank you sir but can you pls guide how to pass merged values to string builder(Text file using delimiters)

Previously i used code like below

totalRowsCount = dtPatterns.Rows.Count;
totalColumnsCount = dtPatterns.Columns.Count;

this.lblRowsCount.Invoke((MethodInvoker)delegate { this.lblRowsCount.Text = totalRowsCount.ToString(); });

for (int c = 0; c < dtPatterns.Columns.Count; c++)
{
if (c == dtPatterns.Columns.Count - 1)
{
SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString());
}
else
{
SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString() + sValue.ToString());
}
}
SB1.Append("\r\n");
for (int d = 0; d < dtPatterns.Rows.Count; d++)
{
this.toolStripStatusLabel1.Text = string.Format("Processing Please Wait ...{0} of {1}", (count + 1), totalRowsCount);
for (int c = 0; c < dtPatterns.Columns.Count; c++)
{
int g = dtPatterns.Columns.Count - 1;
if (c == g)
{
SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString());
}
else
{
SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString() + sValue.ToString());
}
}
SB1.Append("\r\n");
count++;
}

OriginalGriff 21-Nov-17 7:00am

And?
What does it do that you didn't expect, or not do that you did?

And why does that look like you are converting a DataTable to CSV data yourself? Why not use one of the many available code examples?
https://www.google.co.uk/search?q=datatable+to+CSV+c%23&oq=datatable+to+CSV+c%23&aqs=chrome..69i57j0l5.6624j0j7&sourceid=chrome&ie=UTF-8

SukirtiShetty 22-Nov-17 7:17am

Thank you it worked for me

OriginalGriff 22-Nov-17 8:20am

You're welcome!