Skip to main content

Android Import excel into Sqlite Database Part 1

Android Import excel into Sqlite Database – Part 1

csv

STEP 1 : Creating a new Project

First make a new Project in Android Studio as :

csv pro 1

csv pro 2

And Click Next
Select your minimum SDK version in next Screen
Select Blank Activity
Dont change the Activity name (if you wish change it, no big deal.)
And Click Finish

STEP 2 : Building up the design of our Application.

Open activity_main.xml [it is here res>layout>activity_main.xml] and edit it as following


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:weightSum="9"
    android:background="#FFC7C7C7"
    >


    <LinearLayout
        android:id="@+id/lvcontainer"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="0.5"
        android:layout_alignParentTop="true"
        android:orientation="horizontal"
        android:padding="1dp"
        android:background="#FFC7C7C7"
        android:weightSum="3">

        <TextView
            android:id="@+id/txtproductcompany"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="left"
            android:text="COMPANY"
            android:textColor="#000000"
            android:textSize="13sp" />

        <TextView
            android:id="@+id/txtproductname"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="left"
            android:padding="3dp"
            android:text="PRODUCT"
            android:textColor="#000000"
            android:textSize="13sp" />

        <TextView
            android:id="@+id/txtproductprice"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="left"
            android:text="PRICE"
            android:padding="3dp"
            android:textColor="#000000"
            android:textSize="13sp" />
    </LinearLayout>

    <ListView
        android:id="@android:id/list"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_below="@+id/lvcontainer"
        android:layout_weight="7"></ListView>

    <TextView
        android:id="@+id/txtresulttext"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_marginLeft="5dp"
        android:layout_below="@android:id/list"
        android:layout_marginTop="2dp"
        android:layout_weight="0.5"
        android:gravity="left"
        android:text=""
        android:textColor="#FFF55F54"
        android:textSize="20sp"
        android:textStyle="italic|bold"></TextView>

    <LinearLayout
        android:id="@+id/lvbottom"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_alignParentBottom="true"
        android:orientation="horizontal"
        android:layout_weight="1"
        android:weightSum="1">

        <Button
            android:id="@+id/btnupload"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:gravity="center"
            android:text="UPLOAD"
            android:textColor="#ffffff"
            android:background="#1083f5"
            android:textSize="15sp"
            android:textStyle="bold" />
    </LinearLayout>

</LinearLayout>

The lvcontainer linearlayout is just a Heading container for the Listview [id = list] in which we would be displaying our imported data.

The Button [btnupload] will be used to import our data.

When you run the app…it will look like this

csv design 1

Now add a new layout file with name v.xml (I know its meaningless name. ๐Ÿ™„ ).
And edit it as following :

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical" >

    <LinearLayout
        android:id="@+id/lvh"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:scrollbars="horizontal"
        android:background="#ffe6e6e6"
        android:weightSum="3" >
        <TextView
            android:id="@+id/txtproductcompany"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="left"
            android:text="COMPANY"
            android:textColor="#000000"
            android:textSize="13sp" />

        <TextView
            android:id="@+id/txtproductname"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="left"
            android:padding="3dp"
            android:text="PRODUCT"
            android:textColor="#000000"
            android:textSize="13sp" />

        <TextView
            android:id="@+id/txtproductprice"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:gravity="left"
            android:text="PRICE"
            android:padding="3dp"
            android:textColor="#000000"
            android:textSize="13sp" />

    </LinearLayout>

</LinearLayout>

This file will be making the List layout.

Ok now the coding part ๐Ÿ™‚ ๐Ÿ˜‰

STEP 3 : Coding Part.
First Create a new File with name
DBController.java

And edit it as following :


package androidcsv.demo;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.HashMap;

public class DBController extends SQLiteOpenHelper {
    private static final String LOGCAT = null;

    public DBController(Context applicationcontext) {
        super(applicationcontext, "PrdouctDB.db", null, 1);  // creating DATABASE
        Log.d(LOGCAT, "Created");
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        String query;
        query = "CREATE TABLE IF NOT EXISTS proinfo ( Id INTEGER PRIMARY KEY, Company TEXT,Name TEXT,Price TEXT)";
        database.execSQL(query);
    }


    @Override
    public void onUpgrade(SQLiteDatabase database, int version_old,
                          int current_version) {
        String query;
        query = "DROP TABLE IF EXISTS proinfo";
        database.execSQL(query);
        onCreate(database);
    }

    public ArrayList<HashMap<String, String>> getAllProducts() {
        ArrayList<HashMap<String, String>> proList;
        proList = new ArrayList<HashMap<String, String>>();
        String selectQuery = "SELECT  * FROM proinfo";
        SQLiteDatabase database = this.getWritableDatabase();
        Cursor cursor = database.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                //Id, Company,Name,Price
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("Id", cursor.getString(0));
                map.put("Company", cursor.getString(1));
                map.put("Name", cursor.getString(2));
                map.put("Price", cursor.getString(3));
                proList.add(map);
            } while (cursor.moveToNext());
        }

        return proList;
    }

}

EXPLANATION of DBController.java
Here I am creating a Database with name PrdouctDB.db

The Code :

 @Override
    public void onCreate(SQLiteDatabase database) {
        String query;
        query = "CREATE TABLE IF NOT EXISTS proinfo ( Id INTEGER PRIMARY KEY, Company TEXT,Name TEXT,Price TEXT)";
        database.execSQL(query);
    }

will create the table proinfo with columns Id, Company, Name and Price

The method getAllProducts() will create a arraylist of fetch the information from our table.

The csv file be of 3*3

csv file data

MainActivity.java

Open the MainActivity.java [it’s here java > androidcsv.demo > MainActivity.java]

Import the following :

import android.app.Dialog;
import android.app.ListActivity;
import android.content.ActivityNotFoundException;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

Now declare following variables :

public class MainActivity extends ListActivity {

    TextView lbl;
    DBController controller = new DBController(this);
    Button btnimport;
    ListView lv;
    final Context context = this;
    ListAdapter adapter;
    ArrayList<HashMap<String, String>> myList;
    public static final int requestcode = 1;

Our Activity will be extending ListActivity to make ListView.

Now declare the value of the variables in onCreate method

lbl = (TextView) findViewById(R.id.txtresulttext);
        btnimport = (Button) findViewById(R.id.btnupload);
        lv = getListView();

Set onClick Listener for the Import Button.

 btnimport.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                Intent fileintent = new Intent(Intent.ACTION_GET_CONTENT);
                fileintent.setType("gagt/sdf");
                try {
                    startActivityForResult(fileintent, requestcode);
                } catch (ActivityNotFoundException e) {
                    lbl.setText("No app found for importing the file.");
                }

            }
        });

Setting up the listview

Now add the following code in onCreate method

myList= controller.getAllProducts();
        if (myList.size() != 0) {
            ListView lv = getListView();
            ListAdapter adapter = new SimpleAdapter(MainActivity.this, myList,
                    R.layout.v, new String[]{"Company", "Name", "Price"}, new int[]{
                    R.id.txtproductcompany, R.id.txtproductname, R.id.txtproductprice});
            setListAdapter(adapter);
            lbl.setText("");
        }

Adding the Activity Result method upon click importing.

Add the following code after completion of onCreate method.

 protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        if (data == null)
            return;
        switch (requestCode) {
            case requestcode:
                String filepath = data.getData().getPath();
                controller = new DBController(getApplicationContext());
                SQLiteDatabase db = controller.getWritableDatabase();
                String tableName = "proinfo";
                db.execSQL("delete from " + tableName);
                try {
                    if (resultCode == RESULT_OK) {
                        try {

                            FileReader file = new FileReader(filepath);

                            BufferedReader buffer = new BufferedReader(file);
                            ContentValues contentValues = new ContentValues();
                            String line = "";
                            db.beginTransaction();

                            while ((line = buffer.readLine()) != null) {

                                String[] str = line.split(",", 3);  // defining 3 columns with null or blank field //values acceptance
                                //Id, Company,Name,Price
                                String company = str[0].toString();
                                String Name = str[1].toString();
                                String Price = str[2].toString();


                                contentValues.put("Company", company);
                                contentValues.put("Name", Name);
                                contentValues.put("Price", Price);
                                db.insert(tableName, null, contentValues);
                                lbl.setText("Successfully Updated Database.");
                            }
                            db.setTransactionSuccessful();
                            db.endTransaction();
                        } catch (IOException e) {
                            if (db.inTransaction())
                                db.endTransaction();
                            Dialog d = new Dialog(this);
                            d.setTitle(e.getMessage().toString() + "first");
                            d.show();
                            // db.endTransaction();
                        }
                    } else {
                        if (db.inTransaction())
                            db.endTransaction();
                        Dialog d = new Dialog(this);
                        d.setTitle("Only CSV files allowed");
                        d.show();
                    }
                } catch (Exception ex) {
                    if (db.inTransaction())
                        db.endTransaction();

                    Dialog d = new Dialog(this);
                    d.setTitle(ex.getMessage().toString() + "second");
                    d.show();
                    // db.endTransaction();
                }
        }
        myList= controller.getAllProducts();

        if (myList.size() != 0) {
            ListView lv = getListView();
            ListAdapter adapter = new SimpleAdapter(MainActivity.this, myList,
                    R.layout.v, new String[]{"Company", "Name", "Price"}, new int[]{
                    R.id.txtproductcompany, R.id.txtproductname, R.id.txtproductprice});
            setListAdapter(adapter);
            lbl.setText("Data Imported");
        }
    }

Add the following Permissions to AndroidManifest file
>> Above application tag in Manifest file you should declare this permission.

    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

When you run this app :

SCREEN 1

csv design 1

Click Upload button

SCREEN 2

csv 2

Select any file explorer to import in my case I am using ES file explorer

SCREEN 3
Then select the csv file from your desired folder
Then select Normal Android way to import

file select
SCREEN 4

Data will be imported ๐Ÿ™‚ ๐Ÿ˜‰


If you like my work, please click the like button and like my page on facebook. You can also comment belowโ€ฆI will try my best to help you out.

csv 4

Here’s the complete code of MainActivity.java


package androidcsv.demo;


import android.app.Dialog;
import android.app.ListActivity;
import android.content.ActivityNotFoundException;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

public class MainActivity extends ListActivity {

    TextView lbl;
    DBController controller = new DBController(this);
    Button btnimport;
    ListView lv;
    final Context context = this;
    ListAdapter adapter;
    ArrayList<HashMap<String, String>> myList;
    public static final int requestcode = 1;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        lbl = (TextView) findViewById(R.id.txtresulttext);
        btnimport = (Button) findViewById(R.id.btnupload);
        lv = getListView();

        btnimport.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                Intent fileintent = new Intent(Intent.ACTION_GET_CONTENT);
                fileintent.setType("gagt/sdf");
                try {
                    startActivityForResult(fileintent, requestcode);
                } catch (ActivityNotFoundException e) {
                    lbl.setText("No activity can handle picking a file. Showing alternatives.");
                }

            }
        });
        myList= controller.getAllProducts();
        if (myList.size() != 0) {
            ListView lv = getListView();
            ListAdapter adapter = new SimpleAdapter(MainActivity.this, myList,
                    R.layout.v, new String[]{"Company", "Name", "Price"}, new int[]{
                    R.id.txtproductcompany, R.id.txtproductname, R.id.txtproductprice});
            setListAdapter(adapter);
            lbl.setText("");
        }
    }

    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        if (data == null)
            return;
        switch (requestCode) {
            case requestcode:
                String filepath = data.getData().getPath();
                controller = new DBController(getApplicationContext());
                SQLiteDatabase db = controller.getWritableDatabase();
                String tableName = "proinfo";
                db.execSQL("delete from " + tableName);
                try {
                    if (resultCode == RESULT_OK) {
                        try {

                            FileReader file = new FileReader(filepath);

                            BufferedReader buffer = new BufferedReader(file);
                            ContentValues contentValues = new ContentValues();
                            String line = "";
                            db.beginTransaction();

                            while ((line = buffer.readLine()) != null) {

                                String[] str = line.split(",", 3);  // defining 3 columns with null or blank field //values acceptance
                                //Id, Company,Name,Price
                                String company = str[0].toString();
                                String Name = str[1].toString();
                                String Price = str[2].toString();


                                contentValues.put("Company", company);
                                contentValues.put("Name", Name);
                                contentValues.put("Price", Price);
                                db.insert(tableName, null, contentValues);
                                lbl.setText("Successfully Updated Database.");
                            }
                            db.setTransactionSuccessful();
                            db.endTransaction();
                        } catch (IOException e) {
                            if (db.inTransaction())
                                db.endTransaction();
                            Dialog d = new Dialog(this);
                            d.setTitle(e.getMessage().toString() + "first");
                            d.show();
                            // db.endTransaction();
                        }
                    } else {
                        if (db.inTransaction())
                            db.endTransaction();
                        Dialog d = new Dialog(this);
                        d.setTitle("Only CSV files allowed");
                        d.show();
                    }
                } catch (Exception ex) {
                    if (db.inTransaction())
                        db.endTransaction();

                    Dialog d = new Dialog(this);
                    d.setTitle(ex.getMessage().toString() + "second");
                    d.show();
                    // db.endTransaction();
                }
        }
        myList= controller.getAllProducts();

        if (myList.size() != 0) {
            ListView lv = getListView();
            ListAdapter adapter = new SimpleAdapter(MainActivity.this, myList,
                    R.layout.v, new String[]{"Company", "Name", "Price"}, new int[]{
                    R.id.txtproductcompany, R.id.txtproductname, R.id.txtproductprice});
            setListAdapter(adapter);
            lbl.setText("Data Imported");
        }
    }


}

 

You will definitely like following posts I think :

ADDING DATA IN MS SQL DATABASE USING ANDROID
ANDROID LISTVIEW FROM MS SQL DATABASE TABLE
A Simple Android Game
A SIMPLE ANDROID APP SHOWING USE OF JSON IN ANDRID.
A complete look on Android SQLite database
How you can import a EXCEL FILE into your application
How you can use Google android maps API to integrate in your application
CSS – A complete guide
Learn about CSS selectors
Android ImageView from your local storage or SD card image file.
Making custom android navigation tabs

 

Source code download link

  • Pingback: Android Import excel into Sqlite Database Part 2 - ParallelCodes()

  • amrut

    I impemented above code but when i click on upload btn it shows “No activity can handle picking a file” what i misses?

    • Its probably because you don’t have a suitable file manager application to upload a file. Download from Play store

  • lovekesh kumar

    Great Job Bro!!!keep it up..Thanks for sharing!!!

  • Paul

    When I click on the upload button, it lead to CSV file but when I click it then don’t open it up

    • your format of the CSV file might not be correct.

      • Paul

        Hi Hitesh VIkani,
        I also tried part 2, the problem still occurs, I think similar errors it not by file format.

  • Sanjay Ramas

    Awesome and Clear.. Works perfectly if u save yo CSV as delimited with comma

  • Mohith Khanna

    Hello,
    i have implemented the code exactly as it is above but the problem is when i click upload and select the csv file nothing is displayed and no errors also. Please help.

    • Do you have any file manager installed on your phone?

      • Mohith Khanna

        Yep! i do. I have ES explorer!!
        I found out that i had to give permission on my phone in order access the storage.

        • How have you made the csv file? In excel or using notepad?
          Do one thing, open the csv file in notepad and see how many commas are their in each line.
          There should be two commas only, because there are three columns, if their is a comma after the third column means the total number of column must be four. In the above example I have used only three columns and CSV files are comma delimited files.
          Example :
          Right :
          A,B,C
          D,E,F
          X,Y,Z

          Wrong input will be
          A,B,C, <<== This will be the fourth column.
          I'm not sure, but it might be the problem.
          Also check the Logcat output.

      • navjyot

        Same problem sir….Can’t select any file (clicked on file but nothing happens…same screen even after clicking upon file many times) and sir I have saved file as delimited with comma only then also got same error

    • Arnav

      hello mohith, i want to know do u get the solution of ur problem bcoz i have the similiar kind of problem with my application

    • See the download link above

  • Arnav

    I have around 10,000 rows in my table and it is not showing even a single entry.. Plz help!!

    • Arnav please mention the issue you are facing with my code.

      • Arnav

        Sir, I have 7000 entries in my .csv file and i copied your code exactly as above but when i choose pick up the file as ‘Normal Android way’ the screen remains blank and nothing happens

        • See the download link

          • Vedant Bhagwat

            as u said i ran your project but still its not showing me the content of the .csv file which I have created.. Plz help

        • Vedant Bhagwat

          same problem with me as well

  • navjyot

    Hitesh sir…
    This code effectively works as I am getting my phone contents,it lists all folders as well as files but I cnt select the csv file(actually cant select any file of phone) when run in samsung j7 phone but same code works and uploads csv file well in another phone of lg

    • navjyot

      Please Hitesh sir…help me out of this problem as there is no error in code and everything works pretty good but I cnt select any file…
      Please reply at the earliest…
      Thank you in advance

      • which file explorer you are using? use inbuilt file explorer which is pre installed in the phone

        • Ajay Kumar

          I do not get the option to choose the explorer. PLease see the images in my question above by AjayB

    • Amitesh Prajapati

      Please change this ….
      fileintent.setType(“*/*”);

  • Vedant Bhagwat

    i have copied all these code, but when i run the app and upload the .csv file then it won’t work..
    please help..

  • AjayB

    1) I was able to compile and run the application on mobile but aoo is not allowing to select the CSV file from Drive or from mobile folder. The file is GREYED and not-selectable. I have not modified the mime type. What could ebe the issue?
    2) Instead of CSV if I want to import a excel what change should be done? I tried changing the mime type it it shows the excel greyed out and does not allow to pick the file.

Shares