Export Multiple Text Files to Excel sheet using C# and VB.Net

Hi setwell,

Please refer below updated code.

Namespaces

C#

using System.IO;
using System.Data;
using OfficeOpenXml;

VB.Net

Imports System.IO
Imports System.Data
Imports OfficeOpenXml

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    // Read all Text file from folder.
    string[] files = Directory.GetFiles(@"C:\Users\dell\Desktop\Files");
    // Create DataTable.
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] {
        new DataColumn("Id"),
        new DataColumn("Name"),
        new DataColumn("Country")
    });

    foreach (string filePath in files)
    {
        // Read all lines from the text file.
        string[] lines = File.ReadAllLines(filePath);
        for (int i = 0; i < lines.Length; i++)
        {
            if (!string.IsNullOrEmpty(lines[i]))
            {
                DataRow dr = dt.NewRow();
                // Loop through the lines and split by '|'.
                string[] values = lines[i].Split('|');
                for (int j = 0; j < values.Length; j++)
                {
                    dr[j] = values[j];
                }
                dt.Rows.Add(dr);
            }
        }
    }

    if (dt.Rows.Count > 0)
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        // Initialize a new Excel package.
        using (var package = new ExcelPackage())
        {
            // Add a new worksheet to the empty workbook.
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Customer");
            // Loads DataTable.
            worksheet.Cells["A1"].LoadFromDataTable(dt, true);
            string outputFilePath = @"C:\Users\dell\Desktop\Customers.xlsx";
            // Save the Excel file.
            package.SaveAs(outputFilePath);
        }
    }
}

VB.Net

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'Read all Text file from folder.
    Dim files As String() = Directory.GetFiles("C:\Users\dell\Desktop\Files")
    'Create DataTable.
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn(2) {
                        New DataColumn("Id"),
                        New DataColumn("Name"),
                        New DataColumn("Country")})
    For Each filePath As String In files
        'Read all lines from the text file.
        Dim lines As String() = File.ReadAllLines(filePath)
        For i As Integer = 0 To lines.Length - 1
            If Not String.IsNullOrEmpty(lines(i)) Then
                Dim dr As DataRow = dt.NewRow()
                'Loop through the lines and split by '|'.
                Dim values As String() = lines(i).Split("|")
                For j As Integer = 0 To values.Length - 1
                    dr(j) = values(j)
                Next
                dt.Rows.Add(dr)
            End If
        Next
    Next

    If dt.Rows.Count > 0 Then
        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial
        'Initialize a new Excel package.
        Using package = New ExcelPackage()
            'Add a new worksheet to the empty workbook.
            Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets.Add("Customer")
            'Loads DataTable.
            worksheet.Cells("A1").LoadFromDataTable(dt, True)
            Dim outputFilePath As String = "C:\Users\dell\Desktop\Customers.xlsx"
            'Save the Excel file.
            package.SaveAs(outputFilePath)
        End Using
    End If
End Sub

Screenshot

Text Files

Excel File