Copy Data from CSV file to Sqlite file in Android

I would like to suggest using SQLite Studio for importing CSV data and create an SQLite database file from that CSV file. You can find the details here. You can also do that from your command line argument as shown in the link provided.

$ sqlite3 mydb // This creates a new database
.mode csv // Enable to import from the csv
.import c:/sqlite/city.csv cities // import a csv file and create a table having the column name same as found in the first row of your csv file

Once you have got your SQLite database, you can now add, drop or modify columns and their names using the database queries.

Once you have prepared the database, you can put this database file in the asset directory of your Android Studio project and while launching the application, copy the database from your asset directory into your local storage so that it can be accessed by your Android application. I am attaching a sample code for copying database file from the asset directory to your internal storage.

private void copyFromAssetsAndCreateDatabase() {

    InputStream mInputStream;
    private String DB_PATH = "/data/data/" + "your.application.package.goes.here" + "/databases/";

    try {
        mInputStream = getApplicationContext().getAssets().open(YOUR_DATABASE_NAME);

        File dir = new File(DataHelper.DB_PATH);
        if (!dir.exists()) dir.mkdir();
        File f = new File(DataHelper.DB_PATH + YOUR_DATABASE_NAME);
        if (!f.exists()) {
            f.createNewFile();
        }

        OutputStream mOutput = new FileOutputStream(f);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInputStream.read(mBuffer)) > 0) {
            mOutput.write(mBuffer, 0, mLength);
        }

        mOutput.flush();
        mOutput.close();
        mInputStream.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Hope that helps!

The main way is Read File like this:

 FileReader fr = new FileReader(file);
 BufferedReader br = new BufferedReader(fr);

Get title of the columns:

String dataA = br.readLine();
String[] eachLineA = dataA.split(",");

Read line by line :

while ((dataA = br.readLine()) != null)

Split and Remove " from each cell,

fitData.add(eachLineA[i].replaceAll("\"", ""));

Finally add to database

 myDb.addHandler(fitData.get(0), fitData.get(1), fitData.get(2), fitData.get(3), fitData.get(4));

This is a while loop:

 while ((dataA = br.readLine()) != null) {

                    while (way && dataA != null) {

                        eachLineA = dataA.split(",");//
                        if (eachLineA.length < leA && fitData.size() < leA) {
                            way = true;

                            dataA = br.readLine();
                        } else way = false;

                        for (int i = 1; i <= eachLineA.length - 1; i++) {
                            fitData.add(eachLineA[i].replaceAll("\"", ""));
                            Log.i("Tag", "N:" + i);
                        }

                    }

                    myDb.addHandler(
                            fitData.get(0), fitData.get(1), fitData.get(2), fitData.get(3), fitData.get(4)
                    );

                    fitData.clear();
                    way = true;
                }

If the columns in your CSV file don't map into the columns in your SQLite table then you need to do some data transformation first i.e. reduce the number of columns to the number of columns in your table, and name them accordingly. Then you can use the built-in CSV importer to import the data.

1. Open a terminal and launch a sqlite prompt.

2. Set mode to csv

   sqlite> .mode csv

3. Import the data from your csv file with the following command:

   sqlite> .import c:/path/to/your/file/use/forward/slashes destination_table_name

This is a really good tutorial. http://www.sqlitetutorial.net/sqlite-import-csv/