Importing excel worksheet data to SQLite database table with tricky datatypes

Question

I want to import data from a excel workbook/worksheet to a SQLite database table. Right now I'm using the below method(using EPPlus for workbook manipulation):

		public void Excel2DB()  
		{  
			DataTable dt = new DataTable();  
  
			dt.Columns.Add("Party");  
			dt.Columns.Add("Bill No");  
			dt.Columns.Add("Bill Date");  
			dt.Columns.Add("Amount");  
			dt.Columns.Add("Due Date");  
			dt.Columns.Add("Paid On");  
  
			using (ExcelPackage excelPackage = new ExcelPackage(new System.IO.FileInfo(xlFile), false))  
			{  
  
				ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();  
  
				for (int i = worksheet.Dimension.Start.Row+1; i <= Convert.ToInt32(LastRow(xlFile)); i++)  
				{  
					DataRow dr = dt.NewRow();  
  
					dt.Rows.Add(worksheet.Cells[i, 1].Text, worksheet.Cells[i, 2].Text, worksheet.Cells[i, 3].Text, worksheet.Cells[i, 4].Text, worksheet.Cells[i, 5].Text, worksheet.Cells[i, 8].Text);  
					dt.AcceptChanges();  
					  
				}  
				  
				using(SQLiteConnection conn= new SQLiteConnection(@"Data Source="+Path.GetFullPath("./TestDB.db")+";"))  
				{  
					conn.Open();  
					string str;  
					SQLiteCommand com;  
  
					foreach (DataRow row in dt.Rows)   
					{  
						str = "insert into ExcelData(Party,BillNo,BillDt,Amt,DueDt,PaidOn)values(@Party,@BillNo,@BillDt,@Amt,@DueDt,@PaidOn)";  
						com = new SQLiteCommand(str, conn);  
						com.Parameters.AddWithValue("@Party", row.Field(0));  
						com.Parameters.AddWithValue("@BillNo", row.Field(1));  
						com.Parameters.AddWithValue("@BillDt", row.Field(2));  
						com.Parameters.AddWithValue("@Amt", row.Field(3));  
						com.Parameters.AddWithValue("@DueDt", row.Field(4));  
						com.Parameters.AddWithValue("@PaidOn", row.Field(5));  
  
						com.ExecuteNonQuery();  
					}  
					  
					conn.Close();  
				}  
  
			}  
        }

I created a database TestDB.db with table ExcelData using DB browser for SQLite as below

My first question is whether the above method can be made faster/more efficient and less error prone like SQL injection issues and/or database read-write, sudden loss of connection to database error ?

Further, when I try to filter the database by date filter like below, I get a blank Datagrid :

			using(SQLiteConnection conn= new SQLiteConnection(@"Data Source="+Path.GetFullPath("./TestDB.db")+";"))  
			{  
				conn.Open();  
  
				SQLiteCommand command = new SQLiteCommand("SELECT * FROM ExcelData WHERE DueDt >= date('now') AND DueDt < date('now','100 days')", conn);  
				command.ExecuteNonQuery();  
				  
				SQLiteDataAdapter adap = new SQLiteDataAdapter(command);  
				  
				DataTable dt = new DataTable("ExcelData");  
				adap.Fill(dt);  
				  
				dataGrid1.ItemsSource=dt.DefaultView;  
				  
				conn.Close();  
			}

I've even tried doing the date filter like SQLiteCommand command = new SQLiteCommand("SELECT * FROM ExcelData WHERE DueDt BETWEEN '2020-02-26' AND '2022-02-02'", conn); but that doesn't work either.

Also, the amount column does not show 2 decimal points when I see it in Datagrid.

How do I solve these issues ?

Note: I'm using .Net 4.5 and no entity framework.

The sample excel worksheet looks like

Accepted Answer

You could try to refer to the code below.

Install System.Data.SQLite using NuGet.
App.Config

...

MainWindow.xaml:

MainWindow.xmal.cs:

using OfficeOpenXml;  
using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Data.SQLite;  
using System.Globalization;  
using System.IO;  
using System.Linq;  
using System.Windows;  
using System.Windows.Data;  
  
namespace SqlLiteDemo  
{  
  public partial class MainWindow : Window  
  {  
    public MainWindow()  
    {  
      InitializeComponent();  
      CreateTable();  
    }  
    string xlFile = @"C:\Users\...\Desktop	esting.xlsx";  
    public void CreateTable()  
    {  
      SQLiteConnection.CreateFile("MyDatabase.sqlite");  
      SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");  
      m_dbConnection.Open();  
      string sql = "create table MyData (Party varchar(20), BillNo varchar(20), BillDate varchar(20) ,Amount varchar(20) ,DueDate varchar(20),PaidOn varchar(20)) ";  
  
      SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);  
      command.ExecuteNonQuery();  
      DataTable dt = new DataTable();  
      FileInfo existingFile = new FileInfo(xlFile);  
      ExcelPackage package = new ExcelPackage(existingFile);  
      dt = ExcelPackageToDataTable(package);  
  
      SQLiteDataAdapter sqliteAdapter = new SQLiteDataAdapter("SELECT * FROM MyData", m_dbConnection);  
      SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(sqliteAdapter);  
      sqliteAdapter.Update(dt);  
      SQLiteCommand sqlCom = new SQLiteCommand("Select * From MyData", m_dbConnection);  
      SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);  
      DataSet ds = new DataSet();  
      sda.Fill(ds);  
      if (ds.Tables[0].Rows.Count > 0)  
      {  
        dg.ItemsSource = ds.Tables[0].DefaultView;  
      }  
      m_dbConnection.Close();  
    }  
    public static DataTable ExcelPackageToDataTable(ExcelPackage excelPackage)  
    {  
      DataTable dt = new DataTable();  
      ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();   
      if (worksheet.Dimension == null)  
      {  
        return dt;  
      }  
      List columnNames = new List();  
  
      int currentColumn = 1;  
      foreach (var cell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])  
      {  
        string columnName = cell.Text.Trim();  
  
        if (cell.Start.Column != currentColumn)  
        {  
          columnNames.Add("Header_" + currentColumn);  
          dt.Columns.Add("Header_" + currentColumn);  
          currentColumn++;  
        }  
        columnNames.Add(columnName);  
        int occurrences = columnNames.Count(x => x.Equals(columnName));  
        if (occurrences > 1)  
        {  
          columnName = columnName + "_" + occurrences;  
        }  
        dt.Columns.Add(columnName);  
        currentColumn++;  
      }  
      for (int i = 2; i <= worksheet.Dimension.End.Row; i++)  
      {  
        var row = worksheet.Cells[i, 1, i, worksheet.Dimension.End.Column];  
        DataRow newRow = dt.NewRow();  
        foreach (var cell in row)  
        {  
          newRow[cell.Start.Column - 1] = cell.Text;  
        }  
        dt.Rows.Add(newRow);  
      }  
      return dt;  
    }  
  }  
  [ValueConversion(typeof(DateTime), typeof(String))]  
  public class DateTimeConverter : IValueConverter  
  {  
    public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)  
    {  
      return DateTime.ParseExact(value.ToString(), "d-M-yyyy", CultureInfo.InvariantCulture);  
    }  
    public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)  
    {  
      throw new NotImplementedException();  
    }  
  }  
  [ValueConversion(typeof(double), typeof(String))]  
  public class DecimalConverter : IValueConverter  
  {  
    public object Convert(object value, Type targetType, object parameter, CultureInfo culture)  
    {  
      return  double.Parse(value.ToString(), CultureInfo.InvariantCulture);  
    }  
    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)  
    {  
      throw new NotImplementedException();  
    }  
  }  
}

Update:
Install System.Data.SqLite.Core using NuGet.
App.config:

...

Add the following code to the above code.

The result:
Excel:

DataGrid :

Filter：

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

SqlLite does not have a date data type so you'll need to format the date as yyyy-MM-dd or yyyyMMdd and use an alpha filter (text) or an integer filter.

It pretty common for data import to use at least two tables. The first table, a staging table, has all text columns. This allows you to get the data into the database. Next, write a query to validate the data in the staging table will import correctly into the target table. Excel is notoriously difficult to import unless you have stick control over the Excel file because every cell can be a different data type. Finally insert the data from the staging table to the target table.

Share via

Importing excel worksheet data to SQLite database table with tricky datatypes

1 additional answer

Your answer