How to Import csv configuration file to device’s SQLite db

How to Import csv configuration file to device’s SQLite db

SQLite_csv_1323

Like web applications, some android apps need to be initialized with certain configuration value for testing and deploying the app with different environment:

  • We used different web services API to access data on server, so we need to use same android APK to access data in dev server as well as stager server.
  • We need to control the camera configuration integrated in android APK
  • Similarly, we need different configuration settings to set dynamically in APK without changing the source code at run-time.

Considering the above scenario, the best thing is to use CSV files, such as the properties files used in Java and .NET platform. In Android, we put CSV file in the device’s phone storage (in this example, the file path is “/storage/sdcard0/Android/data/com.example/”). We have to write codes to read the CSV file and then spilt the data programmatically using separator. Then it has to be inserted in to a SQLite table (in this example uses ‘appconfigtbl‘).
The sample program in this blog will perform the below mentioned steps to achieve the above mentioned initialization of configuration value inside android app:

  • Check if the CSV file exists in the device with proper file path location defined in the APK
  • Use the asynchronous task that runs in the background of the app and initialize the CSV content to the targeted table.
  • While the data is imported from the CSV file to database, the user will see the progress bar of data being uploaded. In case of larger data, the progress bar dialog will be shown with the value of %completed.
  • Since the data is stored in a config-table, the app can retrieve the record from this table and use it in appropriate section.

Benefits:

  • The APK will be re-initialized with basic configuration data at any time.
  • No need to re-write the APK and compile it again.
  • Easier to setup and run inside the device. We get the changed configuration data at run time.
  • APK can be set up to a different configuration data base when required and can be deployed with different config data (CSV file) at different location easily.

Example of CSV File contents (appconfig.csv)

[sourcecode]
Config ID,Config Name,Config Value
validate_url,http://50.56.70.140/
webservice_url,http://50.56.70.140:8080/HOSTAPP/resources/inspection
camera_resolution_hight,800
camera_resolution_width,600
camera_flashlight_mode,auto
long_password,abc123
[/sourcecode]

Example of Layout xml

[sourcecode]
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity" >

<TextView
android:id="@+id/showResult"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="" />

[/sourcecode]

Example of  Main form Activity class

1. MainActivity.java

[sourcecode]
package com.example.csvdemo;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.util.Date;

import android.os.AsyncTask;
import android.os.Bundle;
import android.os.Environment;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.Dialog;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.content.SharedPreferences;
import android.content.res.Resources;
import android.database.Cursor;
import android.util.Log;
import android.view.Menu;
import android.widget.TextView;

public class MainActivity extends Activity {

private boolean isCsvFileNeedToInitialize = true;
private static final int DIALOG_DOWNLOAD_PROGRESS = 0;
private DBHelper mDbHelper;
private ProgressDialog mProgressDialog;
private static int totalRowsUpdate = 0;

public static final String external_sd = Environment.getExternalStorageDirectory().getAbsolutePath();
public static final File sdCard = Environment.getExternalStorageDirectory();
public static final String sdcardBaseDir = sdCard.getAbsolutePath();
public static final String externalPath = "/Android/data/com.example/";
public static final String csvFileName = "appconfig.csv";

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

mDbHelper = new DBHelper(this);
mDbHelper.open();
totalRowsUpdate = 0;

// create empty directory if not exist
File appDir = new File(sdcardBaseDir + externalPath);
if(!appDir.exists())
appDir.mkdirs();

File externalResourceFile = new File(sdcardBaseDir + externalPath + csvFileName);
isCsvFileNeedToInitialize = externalResourceFile.exists();
TextView lableResult = (TextView) findViewById(R.id.showResult);

if(isCsvFileNeedToInitialize)
{
new InitializeCSVFileAsync().execute("");

lableResult.setText( totalRowsUpdate + " fetched from ‘appconfig.csv’ into database successfully.");

} else {
lableResult.setText("’appconfig.csv’ not found!");
PopIt("Exit Application", "’appconfig.csv’ not found!");
}
}

public static void setTotalRecord(int ctr) {
totalRowsUpdate = ctr;
}

private void PopIt( String title, String message ){
AlertDialog.Builder alertbox = new AlertDialog.Builder(this);
alertbox.setTitle(title);
alertbox.setMessage(message);
alertbox.setPositiveButton("Ok", new DialogInterface.OnClickListener() {
public void onClick(DialogInterface arg0, int arg1) {
finish();
}
});
alertbox.show();
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}

@Override
protected Dialog onCreateDialog(int id) {
Resources res = getResources();
String reader = "";
int ctr = 0;
try {
File f = new File(sdcardBaseDir + externalPath + csvFileName);
BufferedReader in = new BufferedReader(new FileReader(f));
while ((reader = in.readLine()) != null) { ctr++; }
setTotalRecord(ctr);
}catch(Exception e) {    e.getMessage();  }

switch (id) {
case DIALOG_DOWNLOAD_PROGRESS:
mProgressDialog = new ProgressDialog(this);
mProgressDialog.setProgressDrawable(res.getDrawable(R.drawable.initialize_progress_bar_states));
mProgressDialog.setMessage("Initializing…");
mProgressDialog.setMax(ctr);
mProgressDialog.setProgressStyle(ProgressDialog.STYLE_HORIZONTAL);
mProgressDialog.setCancelable(false);
mProgressDialog.show();
return mProgressDialog;
default:
return null;
}
}

// Display Initialize progress bar for uploading CSVFiles to database
class InitializeCSVFileAsync extends AsyncTask<String, String, String>
{
@Override
protected void onPreExecute() {
super.onPreExecute();
mDbHelper.deleteCongigTableOldRecord();
if(isCsvFileNeedToInitialize)
showDialog(DIALOG_DOWNLOAD_PROGRESS);
}
@Override
protected String doInBackground(String… aurl) {
try {
float total = 0F;
float fctr = 1F;
String reader = "";
int ctr = 0;
boolean skipheader = true;
File f = new File(sdcardBaseDir + externalPath + csvFileName);
BufferedReader in = new BufferedReader(new FileReader(f));

while ((reader = in.readLine()) != null) {
// skip header column name from csv
if(skipheader) {
skipheader = false;
continue;
}
String[] RowData = reader.split(",");
mDbHelper.insertDB(RowData);
total += fctr;
publishProgress(""+(int)total);
//publishProgress((int)(total*100/lenghtOfFile));
}
in.close();
} catch(Exception e) {
e.getMessage();
}
return null;
}
protected void onProgressUpdate(String… progress) {
//Log.d("ANDRO_ASYNC",progress[0]);
mProgressDialog.setProgress(Integer.parseInt(progress[0]));
}
@Override
protected void onPostExecute(String unused) {

File f = new File(sdcardBaseDir + externalPath + csvFileName);
boolean result = f.delete();
if(isCsvFileNeedToInitialize)
dismissDialog(DIALOG_DOWNLOAD_PROGRESS);
mDbHelper.close();
//fillAllList();
}
protected void onDestroy() {
if (mDbHelper != null) {
mDbHelper.close();
}
}
}
}
[/sourcecode]

Example of  Helper class for sqlite database interaction:
Example of AbstractDbAdapter java

[sourcecode]
package com.example.csvdemo;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public abstract class AbstractDbAdapter {

protected static final String TAG = "ExampleDbAdapter";
protected DatabaseHelper mDbHelper;
protected SQLiteDatabase mDb;

protected static final String CONFIG_TABLE_CREATE =
"create table appconfigtbl (_id integer primary key," + "config_name text not null," + "config_value text," + " createdAt text, " + " updatedAt text);";

protected static final String DATABASE_NAME = "example";
protected static final int DATABASE_VERSION = 2;

protected final Context mCtx;

protected static class DatabaseHelper extends SQLiteOpenHelper {

DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CONFIG_TABLE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS CONFIG_TABLE_CREATE");
onCreate(db);
}
}

public AbstractDbAdapter(Context ctx) {
this.mCtx = ctx;
}

public AbstractDbAdapter open() throws SQLException {
mDbHelper = new DatabaseHelper(mCtx);
mDb = mDbHelper.getWritableDatabase();
return this;
}

public void close() {
if (mDbHelper != null) {
mDbHelper.close();
}
//mDbHelper.close();
}
}
[/sourcecode]

Example of DBHelper java

[sourcecode]
package com.example.csvdemo;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Vector;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;

public class DBHelper extends AbstractDbAdapter{

public static final String KEY_ROWID = "_id";
public static final String KEY_CONFIG_NAME = "config_name";
public static final String KEY_CONFIG_VALUE = "config_value";
public static final String KEY_POSTED_DATE = "createdAt";
public static final String KEY_EDITED_DATE = "updatedAt";
public int maxLevelOnCurrentMenu = 1;

public int getMaxLevelOnCurrentMenu() {
return maxLevelOnCurrentMenu;
}

public void setMaxLevelOnCurrentMenu(int maxLevelOnCurrentMenu) {
this.maxLevelOnCurrentMenu = maxLevelOnCurrentMenu;
}

public static final String DATABASE_TABLE = "appconfigtbl";

public DBHelper(Context ctx) {
super(ctx);
}

public long insertDB(String config_name,String config_value, String createdAt, String updatedAt) {
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_CONFIG_NAME, config_name);
initialValues.put(KEY_CONFIG_VALUE, config_value);
initialValues.put(KEY_POSTED_DATE,createdAt);
initialValues.put(KEY_EDITED_DATE,updatedAt);
return mDb.insert(DATABASE_TABLE, null, initialValues);
}

public long insertDB(String[] RowData)
{
long result = 0;
ContentValues values = new ContentValues();
values.put(KEY_CONFIG_NAME, RowData[0]);
values.put(KEY_CONFIG_VALUE, RowData[1]);
values.put(KEY_POSTED_DATE, "");
values.put(KEY_EDITED_DATE, "");
result = mDb.insert(DATABASE_TABLE, null, values);
return result;
}

public boolean deleteList(long rowId) {

return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}

public boolean deleteCongigTableOldRecord() {

return mDb.delete(DATABASE_TABLE,  null, null) > 0;
}

}
[/sourcecode]

Example of initialize_progress_bar_states.xml [Used to show progressbar UI %progress value]

[sourcecode]

<gradient
android:startColor="#98887b"
android:centerColor="#ddd7c9"
android:centerY="0.95"
android:endColor="#0d1522"
android:angle="270"
/>

<corners
android:bottomRightRadius="7dp" android:bottomLeftRadius="7dp"
android:topLeftRadius="7dp" android:topRightRadius="7dp"/>

<gradient
android:startColor="#74c43f"
android:centerColor="#4a9c14"
android:centerY="0.85"
android:endColor="#06101d"
android:angle="270"
/>
<corners
android:bottomRightRadius="7dp" android:bottomLeftRadius="7dp"
android:topLeftRadius="7dp" android:topRightRadius="7dp"/>

[/sourcecode]

Jay
Jayadev Das
jayadev.das@andolasoft.com

Do what you do best in – that’s what I’ve always believed in and that’s what I preach. Over the past 25+ years (yup that’s my expertise ‘n’ experience in the Information Technology domain), I’ve been consulting to small, medium and large companies ‘bout Web Technologies, Mobile Future as well as on the good-and-bad of tech. Blogger, International Business Advisor, Web Technology Expert, Sales Guru, Startup Mentor, Insurance Sales Portal Expert & a Tennis Player. And top of all – a complete family man!