Excel to SQL via System.Data.DataSet

using System;
using System.Data;
using System.IO;
using ExcelDataReader; // Include ExcelDataReader for reading Excel files
using System.Data.SqlClient; // Include for SQL interoperability

class Program
{
    static void Main()
    {
        // Provide the file path for the input file
        string filePath = "path_to_your_file.xlsx";

        // Call the method to convert the file
        DataTable dataTable = ConvertToDataTable(filePath);

        // Process the DataTable or populate a data grid
    }

    /// <summary>
    /// Converts a supported spreadsheet or delimited file into a DataTable.
    /// </summary>
    /// <param name="filePath">Path to the input file</param>
    /// <returns>DataTable containing the data from the spreadsheet or delimited file</returns>
    static DataTable ConvertToDataTable(string filePath)
    {
        // Validate file existence
        if (!File.Exists(filePath))
        {
            throw new FileNotFoundException("The specified file was not found.");
        }

        // Initialize DataTable to be returned
        DataTable dataTable = new DataTable();

        // Determine file extension for appropriate handling
        string fileExtension = Path.GetExtension(filePath).ToLower();

        // Handle based on file type
        switch (fileExtension)
        {
            // Excel format (XLSX, XLS, etc.)
            case ".xlsx":
            case ".xls":
            case ".xlsm":
            case ".xltx":
                using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
                {
                    using (var reader = ExcelReaderFactory.CreateReader(stream))
                    {
                        // Use reader to work through Excel structure
                        var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration()
                        {
                            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                            {
                                UseHeaderRow = true // Use the first row as column headers
                            }
                        });

                        // Copy the first table of the dataset to the dataTable
                        dataTable = dataSet.Tables[0];
                    }
                }
                break;

            // CSV and TSV formats
            case ".csv":
            case ".tsv":
                char delimiter = fileExtension == ".csv" ? ',' : '\t';
                // Read all lines from the file
                var lines = File.ReadAllLines(filePath);
                if (lines.Length > 0)
                {
                    // Process header line and add columns
                    string[] headers = lines[0].Split(delimiter);
                    foreach (string header in headers)
                    {
                        dataTable.Columns.Add(header);
                    }

                    // Read the rest of the file, adding rows to the DataTable
                    for (int i = 1; i < lines.Length; i++)
                    {
                        string[] cells = lines[i].Split(delimiter);
                        dataTable.Rows.Add(cells);
                    }
                }
                break;

            default:
                throw new NotSupportedException("File extension is not supported for conversion.");
        }

        return dataTable;
    }
}

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.IO
Imports ExcelDataReader ' Include ExcelDataReader for reading Excel files
Imports System.Data.SqlClient ' Include for SQL interoperability

Friend Class Program
	Shared Sub Main()
		' Provide the file path for the input file
		Dim filePath As String = "path_to_your_file.xlsx"

		' Call the method to convert the file
		Dim dataTable As DataTable = ConvertToDataTable(filePath)

		' Process the DataTable or populate a data grid
	End Sub

	''' <summary>
	''' Converts a supported spreadsheet or delimited file into a DataTable.
	''' </summary>
	''' <param name="filePath">Path to the input file</param>
	''' <returns>DataTable containing the data from the spreadsheet or delimited file</returns>
	Private Shared Function ConvertToDataTable(ByVal filePath As String) As DataTable
		' Validate file existence
		If Not File.Exists(filePath) Then
			Throw New FileNotFoundException("The specified file was not found.")
		End If

		' Initialize DataTable to be returned
		Dim dataTable As New DataTable()

		' Determine file extension for appropriate handling
		Dim fileExtension As String = Path.GetExtension(filePath).ToLower()

		' Handle based on file type
		Select Case fileExtension
			' Excel format (XLSX, XLS, etc.)
			Case ".xlsx", ".xls", ".xlsm", ".xltx"
				Using stream = File.Open(filePath, FileMode.Open, FileAccess.Read)
					Using reader = ExcelReaderFactory.CreateReader(stream)
						' Use reader to work through Excel structure
'INSTANT VB TODO TASK: Underscore 'discards' are not converted by Instant VB:
'ORIGINAL LINE: var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } });
						Dim dataSet = reader.AsDataSet(New ExcelDataSetConfiguration() With {
							.ConfigureDataTable = Function(underscore) New ExcelDataTableConfiguration() With {.UseHeaderRow = True}
						})

						' Copy the first table of the dataset to the dataTable
						dataTable = dataSet.Tables(0)
					End Using
				End Using

			' CSV and TSV formats
			Case ".csv", ".tsv"
				Dim delimiter As Char = If(fileExtension = ".csv", ","c, ControlChars.Tab)
				' Read all lines from the file
				Dim lines = File.ReadAllLines(filePath)
				If lines.Length > 0 Then
					' Process header line and add columns
					Dim headers() As String = lines(0).Split(delimiter)
					For Each header As String In headers
						dataTable.Columns.Add(header)
					Next header

					' Read the rest of the file, adding rows to the DataTable
					For i As Integer = 1 To lines.Length - 1
						Dim cells() As String = lines(i).Split(delimiter)
						dataTable.Rows.Add(cells)
					Next i
				End If

			Case Else
				Throw New NotSupportedException("File extension is not supported for conversion.")
		End Select

		Return dataTable
	End Function
End Class