How to add 1300 rows to SQLite table?

Question

In Android Studio, I need to create a SQLite table for a list of cities and population. It would have 1300 rows aprox. The list is in a 2 columns Excel file so I can copy it from there to my code.

I have a class to manage DB and a method to create the table, but I don´t know how to add the list of cities-population to the table.

This is the part of my code I have to create the table:

String crearTablaCities = "CREATE TABLE "+MI_TABLA_CITIES+" (ID_CITY INTEGER PRIMARY KEY AUTOINCREMENT, NAME_CITY TEXT, POPULATION_CITY INTEGER)";
        db.execSQL(crearTablaCities);

I know how to add one row getting values from somewhere, but I don´t know how to add more than 1300 rows automatically so that I don´t have to write or modify more than 1300 lines by hand.

    public void insertCity(ObjectCity newCity){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();

        //cv.put("ID_CITY", newCity.getCityID());
        cv.put("NAME_CITY", newCity.getCityName());
        cv.put("POPULATION_CITY", newCity.getCityPopulation());

        db.insert(MI_TABLA_CITIES,null,cv);

        //db.close();
    }

I don´t know how to face the next step. Can someone please give me some hint to find the way to do it? Thanks a lot in advance

Answer 1

There are 3 relatively simple ways that you can accomplish this.

a) Create a formula that generates the code on your behalf and then copy and paste the generated code to a suitable place.

b) Save the data as a csv and copy the file as an asset you can then open and read the file extracting the data and inserting it perhaps in the onCreate method of the database helper.

c) Save the data as a csv and use an SQLite tool to import and load the data and to then copy that database as an asset and then use that database.

Option A is a very simple, assuming that you have something like :-

And your code shows that you have an insertCity method that takes a City object. Assuming that you construct a City using City("Tokyo",37339804) then to insert a single city, your code could be something like :-

`insert(new ObjectCity("Tokyo",37339804L));`

• Long for population has been assumed.

Then you could enter a formula in cell C1 as ="insertCity(new ObjectCity("&CHAR(34)&A1&CHAR(34)&","&B1&"L));". You can then copy this cell to c2-c1300 (c2-c17 using the spreadsheet above). The resultant Speadsheet would be like:-

You can then just drop copy and paste the generated code (cells c1-c1300) into a suitable place.

Here's a working example:-

The ObjectCity class :-

public class ObjectCity {

    private String cityName;
    private Long cityPopulation;

    public ObjectCity(String cityName, Long cityPopulation) {
        this.cityName = cityName;
        this.cityPopulation = cityPopulation;
    }

    public String getCityName() {
        return cityName;
    }

    public Long getCityPopulation() {
        return cityPopulation;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public void setCityPopulation(Long cityPopulation) {
        this.cityPopulation = cityPopulation;
    }
}

The DatabaseHelper DBHelper class (based upon the code in the question) :-

public class DBHelper extends SQLiteOpenHelper {

    public static final String MI_TABLA_CITIES = "city";

    public DBHelper(Context context) {
        super(context, "theDatabase", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crearTablaCities = "CREATE TABLE "+MI_TABLA_CITIES+" (ID_CITY INTEGER PRIMARY KEY AUTOINCREMENT, NAME_CITY TEXT, POPULATION_CITY INTEGER)";
        db.execSQL(crearTablaCities);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public void insertCity(ObjectCity newCity){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();

        //cv.put("ID_CITY", newCity.getCityID());
        cv.put("NAME_CITY", newCity.getCityName());
        cv.put("POPULATION_CITY", newCity.getCityPopulation());
        db.insert(MI_TABLA_CITIES,null,cv);
        //db.close();

    }

    /* function to load the country data */
    public void loadData() {
        if(DatabaseUtils.queryNumEntries(this.getWritableDatabase(),MI_TABLA_CITIES)> 0) return;

        insertCity(new ObjectCity("Tokyo",37339804L));
        insertCity(new ObjectCity("Delhi",31181376L));
        insertCity(new ObjectCity("Shanghai",27795702L));
        insertCity(new ObjectCity("Sao Paulo",22237472L));
        insertCity(new ObjectCity("Mexico City",21918936L));
        insertCity(new ObjectCity("Dhaka",21741090L));
        insertCity(new ObjectCity("Cairo",21322750L));
        insertCity(new ObjectCity("Beijing",20896820L));
        insertCity(new ObjectCity("Mumbai",20667656L));
        insertCity(new ObjectCity("Osaka",19110616L));
        insertCity(new ObjectCity("Karachi",16459472L));
        insertCity(new ObjectCity("Chongqing",16382376L));
        insertCity(new ObjectCity("Istanbul",15415197L));
        insertCity(new ObjectCity("Buenos Aires",15257673L));
        insertCity(new ObjectCity("Kolkata",14974073L));
        insertCity(new ObjectCity("Kinshasa",14970460L));
        insertCity(new ObjectCity("Lagos",14862111L));
    }
}

• Note the loadData method.

  • This uses the DatabaseUtils queryNumEntries method to check if any data exists and only loads the data if there are no rows in the table.
  • The rest of the code was copy and pasted from the spreadsheet as above (2nd image).

Last an invoking activity MainActivity :-

public class MainActivity extends AppCompatActivity {

    DBHelper db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = new DBHelper(this);
        db.loadData(); //<<<<< LOADS THE CITY DATA (if not already loaded)
    }
}

Result

Running the above and using Android Studio's Database Inspector reveals:-