Making an SQL lite DB from a CSV file in Android Studio

Question

I got a CSV file, basically a list of cities with some codes. In my app users write their city of birth, a list of cities appears suggesting it, when chose the city's code is used for other stuff. Can I just move the .csv file in an Android Studio folder and just use it as a database made with sql lite? If no, should I make the sql lite database in Android Studio (a DatabaseManager class with SqlOpenHelper and some queries if i got it), then copy the .csv? How can I just "copy" that?

EDIT: Sorry but I realized that my CSV file had too much columns and that'd be ugly and tiring to manually add the columns. So I used DB Browser for SQLite, now I got a .db file. Can I just put it in a specific database folder and querying it in my app?

Answer 1

Try this for adding the.csv info to your DB

FileReader file = new FileReader(fileName);
BufferedReader buffer = new BufferedReader(file);
String line = "";
String tableName = "TABLE_NAME";
String columns = "_id, name, dt1, dt2, dt3";
String str1 = "INSERT INTO " + tableName + " (" + columns + ") values(";
String str2 = ");";
db.beginTransaction();
while ((line = buffer.readLine()) != null) {
  StringBuilder sb = new StringBuilder(str1);
  String[] str = line.split(",");
  sb.append("'" + str[0] + "',");
  sb.append(str[1] + "',");
  sb.append(str[2] + "',");
  sb.append(str[3] + "'");
  sb.append(str[4] + "'");
  sb.append(str2);
  db.execSQL(sb.toString());
}
db.setTransactionSuccessful();
db.endTransaction();

Answer 2

Can I just move the .csv file in an Android Studio folder and just use it as a database made with sql lite?

No.

A sqlite database, i.e. the file, has to be formatted so that the SQLite routines can access the data enclosed therein. e.g. the first 16 bytes of the file MUST BE SQLite format 3\000 and so on, as per Database File Format

If no, should I make the sql lite database in Android Studio (a DatabaseManager class with SqlOpenHelper and some queries if i got it), then copy the .csv?

You have various options e.g. :-

1. You could copy the csv file into an appropriate location so that it will be part of the package (e.g. the assets folder) and then have a routine to generate the appropriate rows in the appropriate table(s). This would require creating the database within the App.

2. You could simply hard code the inserts within the App. Again this would require creating the database within the App.

3. You could use an SQLite Tool to create a pre-populated database, copy this into the assets folder (assets/databases if using SQLiteAssetHelper) and copy the database from the assets folder. No need to have a csv file in this case.

Example of option 1

As an example that is close to option 1 (albeit that the data isn't stored in the database) the following code extracts data from a csv file from the assets folder.

This option is used in this case as the file changes on an annual basis, so changing the file and then distributing the App applies the changes.

The file looks like :-

# This file contains annual figures
# 5 figures are required for each year and are comma seperated
# 1) The year to which the figures are relevant
# 2) The annualised MTAWE (Male Total Average Weekly Earnings)
# 3) The annual Parenting Payment Single (used to determine fixed assessment)
# 4) The fixed assessment annual rate
# 5) The Child Support Minimum Annual Rate
# Lines starting with # are comments and are ignored
2006,50648,13040,1040,320
2007,52073,13315,1102,330
2008,54756,13980,1122,339
2009,56425,13980,1178,356
2010,58854,14615,1193,360
2011,61781,15909,1226,370
2012,64865,16679,1269,383
2013,67137,17256,1294,391
2014,70569,18197,1322,399
2015,70829,18728,1352,408
2016,71256,19011,1373,414
2017,72462,19201,1390,420
2018,73606,19568,1416,427

It is stored in the assets folder of the App as annual_changes.txt The following code is used to obtain the values (which could easily be added to a table) :-

private void BuildFormulaValues() {
    mFormulaValues = new ArrayList<>();
    mYears = new ArrayList<>();
    StringBuilder errors = new StringBuilder();
    try {
        InputStream is = getAssets().open(formula_values_file);
        BufferedReader bf = new BufferedReader(new InputStreamReader(is));
        String line;
        while ((line = bf.readLine()) != null ) {
            if (line.substring(0,0).equals("#")) {
                continue;
            }
            String[] values = line.split(",");
            if (values.length == 5) {
                try {
                    mFormulaValues.add(
                            new FormulaValues(
                                    this,
                                    Long.parseLong(values[0]),
                                    Long.parseLong(values[1]),
                                    Long.parseLong(values[2]),
                                    Long.parseLong(values[3]),
                                    Long.parseLong(values[4])
                            )
                    );
                } catch (NumberFormatException e) {
                    if (errors.length() > 0) {
                        errors.append("\n");
                    }
                    errors.append(
                            this.getResources().getString(
                                    R.string.invalid_formula_value_notnumeric)
                    );
                    continue;
                }
                mYears.add(values[0]);
            } else {
                if (errors.length() > 0) {
                    errors.append("\n");
                    errors.append(
                            getResources().getString(
                                    R.string.invalid_formula_value_line)
                    );
                }
            }
        }
    } catch (IOException ioe) {
        ioe.printStackTrace();
    }
    if (errors.length() > 0) {
        String emsg = "Note CS CareCalculations may be inaccurate due to the following issues:-\n\n" +
                errors.toString();
        Toast.makeText(
                this,
                emsg,
                Toast.LENGTH_SHORT
        ).show();
    }
}