Covert excel file into pipe delimited format in C# or VB

You'll have to make a OleDb connection to the Excel file:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

and read the following:

Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++   \"
VB6, VBScript   ""
xml (web.config etc)   "
or maybe use a single quota '.
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

Here's how you can do it.

1.  Query the Excel file, put it in a DataTable
2.  Loop through each row in the DataTable and write it out to a file.


    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click


        Dim excelDoc As String = "C:\TEMP\My Worksheet.xls"
        Dim delimitedFile As String = "C:\Temp\MyFile.txt"

        Dim delimter As String = "|"

        Dim table As DataTable = GetExcelData(excelDoc, "Sheet1", False)

        Dim writer As New IO.StreamWriter(delimitedFile, False)

        'Loop through each row and write it out
        For Each row As DataRow In table.Rows
            writer.WriteLine(Join(row.ItemArray, delimter))
        Next

        writer.Close()
        writer.Dispose()

        MsgBox("done")


    End Sub


    Public Shared Function GetExcelData( _
        ByVal path As String, ByVal tableName As String, _
        ByVal bFirstRowIsHeader As Boolean) _
        As DataTable

        Dim connectionString As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & _
                path & "';Extended Properties='Excel 8.0;HDR=" & _
                bFirstRowIsHeader & ";IMEX=1'"

        'Remove any dollar signs from the name,
        '    because they will be added below
        tableName = tableName.Replace("$", "")


        Dim table As New DataTable(IO.Path.GetFileNameWithoutExtension(path))

        Dim adapter As New OleDb.OleDbDataAdapter( _
            "SELECT * FROM [" & tableName & "$]", connectionString)

        adapter.Fill(table)
        adapter.Dispose()

        Return table

    End Function

Hi,
This certainly helps, but  if I use win form and open file dialog to pick up the two excel files and convert them to pipe delimited format and ftp them to a remote server. How do the codes look like in C#?

Again, thanks for your help.
Perry

private void Button1_Click(object sender, System.EventArgs e)
{
   
    string excelDoc = "C:\\TEMP\\My Worksheet.xls";
    string delimitedFile = "C:\\Temp\\MyFile.txt";
   
    string delimter = "|";
   
    DataTable table = GetExcelData(excelDoc, "Sheet1", false);
   
    IO.StreamWriter writer = new IO.StreamWriter(delimitedFile, false);
   
    //Loop through each row and write it out
    foreach (DataRow row in table.Rows) {
        writer.WriteLine(Strings.Join(row.ItemArray, delimter));
    }
   
    writer.Close();
    writer.Dispose();
   
    MessageBox.Show("done");
   
}


public static DataTable GetExcelData(string path, string tableName, bool bFirstRowIsHeader)
{
   
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties='Excel 8.0;HDR=" + bFirstRowIsHeader + ";IMEX=1'";
   
    //Remove any dollar signs from the name,
    // because they will be added below
    tableName = tableName.Replace("$", "");
   
   
    DataTable table = new DataTable(IO.Path.GetFileNameWithoutExtension(path));
   
    OleDb.OleDbDataAdapter adapter = new OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "$]", connectionString);
   
    adapter.Fill(table);
    adapter.Dispose();
   
    return table;
}

Hi,
I am still looking for a better solution to my project: a win application ( either in VB or C#) for end user to load excel files (output from crystal reports) and convert them to pipe delimited format and ftp them to a remote server.
 Requirement will be:
1) Load two excel files from open file dialog
2) Click button to convert them to pipe delimited text files
3) ftp these text files to a remote server.

Basically, I am a Sybase/Actuare/Crystal report developer, do not have much experience in VB/C#.

Thanks for your help!

Perry


 

Can you tell us what's wrong with the code posted above?
In what way you want it to be better.

Hi AlexCode,
There is nothing wrong with your code. I really appreciated your help. LIke I said, I am not a VB/C# developer. I just want the sample codes that can be easily followed through to do the following things:
1) Load two excel files through open file dialogue
2) Convert them into pipe delimited files at one shot
3) Ftp them to a remote server

What you're asking for is an application.
Here at Experts-Exchange we don't usually do applications, we point out solutions.

Applications demand a greater deal of time to assemble.
If you're whiling to wait I can write it for you some time between now and tomorrow.

Cheers,
Alex

AlexCode,

That will be great!  I can wait. Thanks for your help!
Perry

Hi,

as promised here's the code.
I'm prepared to send you is a full working console application that can be configured to match your needs.
Please give me your email so I can mail it to you. If you prefer mail me to mail@alexcode.com
On the application forder you'll find 3 files

• ETL.exe   (the application)
• ETL.exe.config   (the application's configuration file)
• ProjectPurpose.htm   (some instructions... nothing facy)
  

The code was developed using VS 2008 so you must have at least the Express edition to open it.

Please read the instructions and edit the configuration file before running the application, it will not run until you do so.

As this is a Console application you can even add to a windows task to run on a defined hour.

Send me the email and let me know what you think of it.

Cheers,
Alex

AlexCode,

Thanks for your help!

Here is my e-mail address:
pchen0688@gmail.com
Perry

The smple code provided by AlexCode is what I have been looking for. I tried it and it works.

Thanks all!

Perry