'Copy Data from CSV file to Sqlite file in Android

I want to copy the data from CSV file to SQLite file for my android application.SQLite table structure i.e. the columns are different than the CSV file columns. There are more columns in the CSV file. I am trying to use DB Browser For SQLite software.

Is it possible to copy data through these this Application or should I be writing some code to do that? Can anyone please suggest?

Solution 1:^[1]

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!

Solution 2:^[2]

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;
                }

Solution 3:^[3]

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.

Open a terminal and launch a sqlite prompt.
Set mode to csv

sqlite> .mode csv
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/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Reaz Murshed
Solution 2	Mori
Solution 3

'Copy Data from CSV file to Sqlite file in Android

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]