Import csv to database

Question

I'm trying to transfer the data from a csv file to the database on my app. The csv file wasn't generated from my database, so there are fields that I'm not interested in transferring. How would I go about doing that?

CSV: date, ref, quantity, type, color, category
DB: date, quantity, color, category

Post the shape of the CSV, and the shape of your table. – p.campbell Apr 21 '11 at 03:18 — p.campbell, Apr 21 '11 at 03:18
Please see this link stackoverflow.com/a/72113923/12272687 – Mori May 04 '22 at 13:39 — Mori, May 04 '22 at 13:39

score 2 · Accepted Answer · answered Apr 21 '11 at 06:00

Try something like this...

FileReader fr = new FileReader(fileName);
BufferedReader br = new BufferedReader(fr);
String data = "";
String tableName ="MY_TABLE";
String columns = "date,quantity,color,category";
String InsertString1 = "INSERT INTO " + tableName + " (" + columns + ") values(";
String InsertString2 = ");";

db.beginTransaction();
while ((data = br.readLine()) != null) {
    StringBuilder sb = new StringBuilder(InsertString1);
    String[] sarray = data.split(",");
    sb.append("'" + sarray[0] + "',");
    sb.append(sarray[2] + "',");
    sb.append(sarray[4] + "',");
    sb.append(sarray[5] + "'");
    sb.append(InsertString2);
    db.execSQL(sb.toString());
}
db.setTransactionSuccessful();
db.endTransaction();

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

To package static data, use any UI or csv-import command at development time, ship this sqlite db file into asset folder, You can then copy the entire sqlite file onto the device when your application is first run.

To insert a lot of data at run time, bulk insert using transactions is best for performance

You can also use BufferReader

BufferedReader in = new BufferedReader(csvfilepath);
        String reader = "";
        while ((reader = in.readLine()) != null){
            String[] RowData = reader.split(",");

edited May 23 '17 at 12:13

Community

1
1

answered Apr 21 '11 at 03:24

Priyank

10,503
2
27
25

This is a one-time thing, for my personal database, it's not to be included with the app. And the issue is that the csv file has more columns than my database. – Roger Apr 21 '11 at 03:28
1

Roger, I would import the csv file into temp table with all columns and then delete unnecessary column. this would be your most simple and fastest way of doing this task. look at my csv-import command link. – Priyank Apr 21 '11 at 03:37

score 0 · Answer 3 · answered Jan 26 '18 at 14:22

0

For Java programmer, dbis is useful. You can achieve without single line of code. Check it below.

https://dbisweb.wordpress.com/

answered Jan 26 '18 at 14:22

compyutech

578
3
8
26

Import csv to database

3 Answers3