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



One thought to “Android Import excel into Sqlite Database Part 1”

Comments are closed.