Importing excel worksheet data to SQLite database table with tricky datatypes

don bradman 621 Reputation points
2022-03-21T16:31:37.123+00:00

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<string>(0));  
						com.Parameters.AddWithValue("@BillNo", row.Field<string>(1));  
						com.Parameters.AddWithValue("@BillDt", row.Field<string>(2));  
						com.Parameters.AddWithValue("@Amt", row.Field<string>(3));  
						com.Parameters.AddWithValue("@DueDt", row.Field<string>(4));  
						com.Parameters.AddWithValue("@PaidOn", row.Field<string>(5));  
  
						com.ExecuteNonQuery();  
					}  
					  
					conn.Close();  
				}  
  
			}  
        }  

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

185258-image.png

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

185322-image.png

Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,686 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,364 questions
0 comments No comments
{count} votes

Accepted answer
  1. Hui Liu-MSFT 41,146 Reputation points Microsoft Vendor
    2022-03-23T06:52:56.907+00:00

    You could try to refer to the code below.

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

    <?xml version="1.0" encoding="utf-8"?>  
        <configuration>  
        ...  
         <connectionStrings>  
         <add name="SQLiteDbContext" connectionString="Data Source=MyDatabase.sqlite" providerName="System.Data.SQLite.EF6" />  
         </connectionStrings>  
         <appSettings>  
         <!--The license context used-->  
         <add key="EPPlus:ExcelPackage.LicenseContext" value="NonCommercial" />  
         </appSettings>  
        </configuration>  
    

    MainWindow.xaml:

    <Window.Resources>  
            <local:DateTimeConverter x:Key="converter"/>  
            <local:DecimalConverter x:Key="decimalConverter"/>  
        </Window.Resources>  
        <Grid>  
            <DataGrid x:Name="dg" Width="700" Height="400" AutoGenerateColumns="False">  
                <DataGrid.Columns>  
                    <DataGridTextColumn Header="Party" Width="40" Binding="{Binding Party}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="BillNo" Width="100" Binding="{Binding BillNo}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="BillDate" Width="100" Binding="{Binding BillDate,Converter={ StaticResource converter}, StringFormat=yyyy - MM - dd }"></DataGridTextColumn>  
                    <DataGridTextColumn Header="Amount" Width="100" Binding="{Binding Amount , Converter={StaticResource decimalConverter},StringFormat=N2}"></DataGridTextColumn>  
                    <DataGridTextColumn Header="DueDate" Width="100" Binding="{Binding DueDate,Converter={ StaticResource converter}, StringFormat=yyyy - MM - dd }"></DataGridTextColumn>  
                    <DataGridTextColumn Header="PaidOn" Width="100" Binding="{Binding PaidOn,Converter={ StaticResource converter}, StringFormat=yyyy - MM - dd }"></DataGridTextColumn>  
                </DataGrid.Columns>  
            </DataGrid>  
        </Grid>  
    

    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\testing.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<string> columnNames = new List<string>();  
      
          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:

    <configuration>  
        ...  
    	<appSettings>  
    		<!--The license context used-->  
    		<add key="EPPlus:ExcelPackage.LicenseContext" value="NonCommercial" />  
    	</appSettings>  
    	<connectionStrings>  
    		<add name="DB" connectionString="data source=.\DB\DB.db;version=3;"/>  
    	</connectionStrings>  
    </configuration>  
    

    Add the following code to the above code.

     <Button x:Name="filter" Content="filter 28-02-2022  and 25-03-2022" Click="filter_Click" />  
    
    
    public void Filter()  
        {  
          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 where ( (substr(DueDate,1,2) between '01' and '25' ) and (substr(DueDate,4,2)='03') and (substr(DueDate,7) = '2022') ) ", 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();  
        }  
        private void filter_Click(object sender, RoutedEventArgs e)  
        {  
          Filter();  
        }  
    

    The result:
    Excel:
    186372-image.png
    DataGrid :
    186257-image.png

    Filter:
    186334-image.png


    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. AgaveJoe 26,161 Reputation points
    2022-03-21T18:57:51.73+00:00

    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.