how to import excel data to sqlite database

Question

0.00/5 (No votes)

See more:

how can we import excel data to SQLite database.
I can import to data table but unable to do for sqlite

CODE ADDED

C#

if (browsefile == true)
 {
 txtFilePath.Text = openfile.FileName;

 Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
 Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
 Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1); ;
 Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;

 string strCellData = "";
 double douCellData;
 int rowCnt = 0;
 int colCnt = 0;


 DataTable dt = new DataTable();

 for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
 {
 string strColumn = "";
 strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
 dt.Columns.Add(strColumn, typeof(string));
 }

 for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
 {
 string strData = "";
 for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
 {
 try
 {
 strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
 strData += strCellData + "|";
 }
 catch (Exception ex)
 {
 douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
 strData += douCellData.ToString() + "|";
 }
 }
 strData = strData.Remove(strData.Length - 1, 1);
 dt.Rows.Add(strData.Split('|'));
 }
 SQLiteConnection.CreateFile("MyDatabase.sqlite");
 SQLiteConnection m_dbConnection;
 m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
 m_dbConnection.Open();
 string sql = "create table signals(name varchar(20), date datetime)";
 SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
 command.ExecuteNonQuery();
 
//I wast stuck here to insert data pls help me

string sql = "insert into signals (name, date) values

 excelBook.Close(true, null, null);
 excelApp.Quit();
 }

Posted 12-Jul-15 20:59pm

jayanthik

Updated 12-Jul-15 21:42pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-12T21:08:00

Solution 1

If you can already import the data from an Excel file to a data table in your program, you're half way there.

The next step is that you:
- create a connection to SQLite
- create a command which will be used for inserting the data into the database
- create a loop which will loop through the rows in your data table
- for each row you set the parameters for the insert statement and execute it.

To get to the actual coding, have a look at Getting started with SQLite in C#[^]

Pseudo example added

C#

string sql = "insert into signals (name, date) values (:name, :date)"
SQLiteCommand insertCommand = new SQLiteCommand(sql, m_dbConnection);
insertCommand.Parameters.Add(new SQLiteParameter("name"));
insertCommand.Parameters.Add(new SQLiteParameter("date"));
foreach(DataRow originalRow in dt.Rows) {
   insertCommand.Parameters["name"].Value = originalRow[0].ToString();
   insertCommand.Parameters["date"].Value = originalRow[1].ToString();
   try {
      insertCommand.ExecuteNonQuery();
   } catch {
      ...
   }
}

Posted 12-Jul-15 21:08pm

Wendelius

Updated 12-Jul-15 22:04pm

v2

Comments

jayanthik 13-Jul-15 3:24am

hi mika, tx for replying
I am stuck at inserting

if (browsefile == true)
{
txtFilePath.Text = openfile.FileName;

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1); ;
Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;

string strCellData = "";
double douCellData;
int rowCnt = 0;
int colCnt = 0;

DataTable dt = new DataTable();

for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
string strColumn = "";
strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
dt.Columns.Add(strColumn, typeof(string));
}

for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
{
string strData = "";
for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
{
try
{
strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
strData += strCellData + "|";
}
catch (Exception ex)
{
douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
strData += douCellData.ToString() + "|";
}
}
strData = strData.Remove(strData.Length - 1, 1);
dt.Rows.Add(strData.Split('|'));
}
SQLiteConnection.CreateFile("MyDatabase.sqlite");
SQLiteConnection m_dbConnection;
m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
string sql = "create table signals(name varchar(20), date datetime)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
//stuck here string sql = "insert into signals (name, date) values

excelBook.Close(true, null, null);
excelApp.Quit();
}
pls let me know hw can I instert in table

Wendelius 13-Jul-15 3:27am

Can you modify the question so that the INSERT portion of your code is shown properly. Now there's only
string sql = "insert into signals (name, date) values
Use the "Improve question" link to make the modifications. I already added the code you posted in the comment.

jayanthik 13-Jul-15 3:34am

I don't see any changes in my code. pls let me know hw to insert data in table I was stuck to insert data in table from data table.

Wendelius 13-Jul-15 3:39am

Is this the code you curently have for the INSERT?

string sql = "insert into signals (name, date) values
 

 excelBook.Close(true, null, null);
 excelApp.Quit();

jayanthik 13-Jul-15 3:44am

mika I am unable to insert data from data table to signals table
string sql = "insert into signals (name, date) values.................... fill dis pls .
I tried with "string sql = "insert into signals (name, date) values from dt"
its not working

jayanthik 13-Jul-15 3:47am

if iam wrong with insert satatement pls advise me hw to do

Wendelius 13-Jul-15 4:05am

Oay, I see. I modified the answer to contain some pseudo example you cóuld start with.

Also have a look at https://www.devart.com/dotconnect/sqlite/docs/Devart.Data.SQLite~Devart.Data.SQLite.SQLiteCommand~Parameters.html[^]. There's a quite good example how to use parameters.

jayanthik 13-Jul-15 5:29am

thanks mik

Wendelius 13-Jul-15 5:31am

Glad to be of service :)