Android – Importing CSV file into SQLite Database. In this post we will create an Android app which will import data from a CSV file into SQLite database. The file will contain three columns (Company name, Product name and Price). I have attached an link at the end of this post for Downloading the source code for this tutorial, you can get sample CSV file from there. Apart from that I will show the file here too. So let’s begin.
The app is fairly simple. It contains only one activity for getting and parsing the file and showing the data on Android ListView from SQLite database. Create a layout design file with name activity_main.xml and edit it as below:
res > layout > activity_main.xml:
<?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:background="#fff" android:orientation="vertical" android:weightSum="10"> <LinearLayout android:id="@+id/lvcontainer" android:layout_width="match_parent" android:layout_height="0dp" android:layout_alignParentTop="true" android:layout_weight="0.5" android:background="#000" android:orientation="horizontal" android:padding="1dp" android:weightSum="3"> <TextView android:id="@+id/txtCompany" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:gravity="left" android:text="Company" android:textColor="#fff" android:textSize="16sp" /> <TextView android:id="@+id/txtProduct" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:gravity="left" android:padding="3dp" android:text="Product" android:textColor="#fff" android:textSize="16sp" /> <TextView android:id="@+id/txtPrice" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:gravity="left" android:padding="3dp" android:text="Price" android:textColor="#fff" android:textSize="16sp" /> </LinearLayout> <ListView android:id="@android:id/list" android:layout_width="match_parent" android:layout_height="0dp" android:layout_below="@+id/lvcontainer" android:layout_weight="8.5"> </ListView> <TextView android:id="@+id/txtresulttext" android:layout_width="match_parent" android:layout_height="0dp" android:layout_below="@android:id/list" android:layout_marginLeft="5dp" 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_weight="0.5" android:orientation="horizontal" android:weightSum="1"> <Button android:id="@+id/btnupload" android:layout_width="0dp" android:layout_height="match_parent" android:layout_weight="1" android:background="#1083f5" android:gravity="center" android:text="UPLOAD" android:textColor="#ffffff" android:textSize="15sp" android:textStyle="bold" /> </LinearLayout> </LinearLayout>
Also create template layout for our ListView as below
res > layout > lst_template.xml:
<?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/lvContainer" android:layout_width="fill_parent" android:layout_height="wrap_content" android:background="#ffe6e6e6" android:orientation="horizontal" android:scrollbars="horizontal" 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="NAME" 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="ISSN" 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:padding="3dp" android:text="IMPACTFACTOR" android:textColor="#000000" android:textSize="13sp" /> </LinearLayout> </LinearLayout>
Now in AndroidManifest.xml file, define the permission to read external storage as below:
AndroidManifest.xml:
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="parallelcodes.csvreader"> <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" /> <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" /> <application android:allowBackup="true" android:icon="@mipmap/ic_launcher" android:label="@string/app_name" android:roundIcon="@mipmap/ic_launcher_round" android:supportsRtl="true" android:theme="@style/AppTheme"> <activity android:name=".MainActivity" android:label="@string/app_name" android:theme="@style/AppTheme.NoActionBar"> <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> </application> </manifest>
Now, let’s code this app.
First we will create a Database class to help us create SQLite database. This will also get methods for creating, deleting and getting data from our table after importing the file. This link will help you learn more on Android SQLite database.
My app’s package name :parallelcodes.csvreader
DBController.java:
package parallelcodes.csvreader.sqldb;
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 static String tableName = "tblProducts";
public static String colCompany = "Company";
public static String colProduct = "Product";
public static String colPrice = "Price";
public DBController(Context applicationcontext) {
super(applicationcontext, "mydb.db", null, 1); // creating DATABASE
Log.d(LOGCAT, "Created");
}
@Override
public void onCreate(SQLiteDatabase database) {
String query;
query = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colCompany + " TEXT, " + colProduct +
" TEXT PRIMARY KEY, " + colPrice + " TEXT)";
Log.e("create Query", query);
database.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
String query;
query = "DROP TABLE IF EXISTS " + tableName;
database.execSQL(query);
onCreate(database);
}
public ArrayList<HashMap<String, String>> getAllProducts() {
ArrayList<HashMap<String, String>> productList;
productList = new ArrayList<HashMap<String, String>>();
String selectQuery = "SELECT * FROM " + tableName;
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("a", cursor.getString(0));
map.put("b", cursor.getString(1));
map.put("c", cursor.getString(2));
productList.add(map);
Log.e("dataofList", cursor.getString(0) + "," + cursor.getString(1) + "," + cursor.getString(2));
} while (cursor.moveToNext());
}
return productList;
}
}
Now edit your MainActivity.java class as below. We will create a button click listener, which will open the open “Pick File Interface”, so to select our CSV file. We will use Android Intent.ACTION_GET_CONTENT for this. And on successful selection of a valid CSV file we will pass the file path to onActivityResult method which will parse the file and read it line-by-line.
CSV file Snapshot:
MainActivity.java:
package parallelcodes.csvreader;
import android.app.ListActivity;
import android.content.ActivityNotFoundException;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
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 android.widget.Toast;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import parallelcodes.csvreader.sqldb.DBController;
public class MainActivity extends ListActivity {
TextView lbl;
DBController controller;
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);
controller = new DBController(this);
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("text/csv");
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.lst_template, new String[]{"a", "b", "c"}, 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();
Log.e("File path", filepath);
if (filepath.contains("/root_path"))
filepath = filepath.replace("/root_path", "");
Log.e("New File path", filepath);
controller = new DBController(getApplicationContext());
SQLiteDatabase db = controller.getWritableDatabase();
db.execSQL("delete from " + DBController.tableName);
try {
if (resultCode == RESULT_OK) {
Log.e("RESULT CODE", "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) {
Log.e("line", line);
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 Product = str[1].toString();
String Price = str[2].toString();
contentValues.put(DBController.colCompany, company);
contentValues.put(DBController.colProduct, Product);
contentValues.put(DBController.colPrice, Price);
db.insert(DBController.tableName, null, contentValues);
lbl.setText("Successfully Updated Database.");
Log.e("Import", "Successfully Updated Database.");
}
db.setTransactionSuccessful();
db.endTransaction();
} catch (SQLException e) {
Log.e("SQLError", e.getMessage().toString());
} catch (IOException e) {
Log.e("IOException", e.getMessage().toString());
}
} else {
Log.e("RESULT CODE", "InValid");
if (db.inTransaction())
db.endTransaction();
Toast.makeText(MainActivity.this, "Only CSV files allowed.", Toast.LENGTH_LONG).show();
}
} catch (Exception ex) {
Log.e("Error", ex.getMessage().toString());
if (db.inTransaction())
db.endTransaction();
Toast.makeText(MainActivity.this, ex.getMessage(), Toast.LENGTH_LONG).show();
}
}
myList = controller.getAllProducts();
if (myList.size() != 0) {
ListView lv = getListView();
ListAdapter adapter = new SimpleAdapter(MainActivity.this, myList,
R.layout.lst_template, new String[]{"a", "b", "c"}, new int[]{
R.id.txtproductcompany, R.id.txtproductname, R.id.txtproductprice});
setListAdapter(adapter);
lbl.setText("Data Imported");
}
}
}
After reading the file we are splitting the string object by (“,”) so that we can read Company,Product and Price value and pass it to SQLite database class. Once it is done, we are displaying all the rows from our SQL table and displaying on Android Listview.
As of now this code works with: Total Commander file manager.
We are working towards solving file selection issue.
Thank you.
Also see:
How to import excel sheet in Android SQLite database.