TechQA.

How do I make Integers and Doubles save properly to ContentValues?

110 views Asked by At

I'm trying to retrieve data from CSV to SQLite DB, all the Strings are properly saved. But Integers and Doubles are not, all of them saves as null. But how to split Integers and Doubles and put them in ContentValues? Here I'm trying to make this with ContentValues: 

 public static void importSomeDBfromCSV(){

            try{
                FileReader file = new FileReader(currentFilePathOfsavingCSVtoSD);
                BufferedReader buffer = new BufferedReader(file);

                String line = "";
                SQLiteDatabase db = mDbHelper.getWritableDatabase();
                db.beginTransaction();
                try {
                    while ((line = buffer.readLine()) != null) {
                        String[] colums = line.split(",");
                        if (colums.length != 8) {
                            Log.d("CSVParser", "Skipping Bad CSV Row");
                            continue;
                        }
                        ContentValues cv = new ContentValues(8);
                        cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_NAME, colums[0].trim());
                        // needs to be saved as Double
                        cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_PRICE, colums[1].trim());
                        // needs to be saved as Integer
                        cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_QUANTITY, colums[2].trim());
                        // needs to be saved as Integer
                        cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_SOLD, colums[3].trim());
                        cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_BARCODE, colums[4].trim());
                        cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_LOCATION, colums[5].trim());
                        cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_CATEGORY, colums[6].trim());
                        cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_NOTE, colums[7].trim());
                        db.insert(TABLE_NAME, null, cv);
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
                db.setTransactionSuccessful();
                db.endTransaction();
                successImportingFromCSV=true;
                Log.v(LOG_TAG, "Restore is successful");

            }catch(Exception e){
                e.printStackTrace();
                successImportingFromCSV=false;
                Log.e(LOG_TAG, "Failed to restore from csv " + e);
            }

        }
android sqlite csv content-values
Original Q&A
2

There are 2 answers

1
MikeT On BEST ANSWER

I don't believe that there is anything wrong with the code that you have supplied, rather that the data itself may be lacking or alternately the subsequent data retrieval is incorrect. I suspect that the latter (see additional lines added that appears to rule out anything wrong with the input data).

Take this file (in downloads/product.csv) :-

Baked beans,12.56,50,10,barcode,London,Tinned Foods,red beany like things in sauce
Yellow Fin Tuna 12g tin,5.76,100,12,barcode,New York,Tinned Foods,fishy stuff

and a slightly modified importSomeDBfromCSV method :-

public void importSomeDBfromCSV(){

    mDB.delete(TABLE_NAME,null,null); // Delete all rows for testing

    final String LOG_TAG = "ImportDB"; // Added just to resolve LOG_TAG
    String filetoimport = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS).getPath() + File.separator + "products.csv"; // get the file name for testing.

    try{
        FileReader file = new FileReader(filetoimport);
        BufferedReader buffer = new BufferedReader(file);

        String line = "";
        mDB.beginTransaction();
        try {
            while ((line = buffer.readLine()) != null) {
                Log.d(LOG_TAG,"Read in line :-"); //<<<< Added for logging
                String[] colums = line.split(",");
                if (colums.length != 8) {
                    Log.d("CSVParser", "Skipping Bad CSV Row"); //<<<< Added to log
                    continue;
                }
                for (String s: colums
                     ) {
                    Log.d(LOG_TAG,"Data extracted for a column = " + s); //<<<< Added to log
                }
                ContentValues cv = new ContentValues(8);
                cv.put(COLUMN_PRODUCT_NAME, colums[0].trim());
                // needs to be saved as Double
                cv.put(COLUMN_PRODUCT_PRICE, colums[1].trim());
                // needs to be saved as Integer
                cv.put(COLUMN_PRODUCT_QUANTITY, colums[2].trim());
                // needs to be saved as Integer
                cv.put(COLUMN_PRODUCT_SOLD, colums[3].trim());
                cv.put(COLUMN_PRODUCT_BARCODE, colums[4].trim());
                cv.put(COLUMN_PRODUCT_LOCATION, colums[5].trim());
                cv.put(COLUMN_PRODUCT_CATEGORY, colums[6].trim());
                cv.put(COLUMN_PRODUCT_NOTE, colums[7].trim());
                mDB.insert(TABLE_NAME, null, cv);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        mDB.setTransactionSuccessful();
        mDB .endTransaction();
        //successImportingFromCSV=true; //Commented out for ease
        Log.v(LOG_TAG, "Restore is successful");

    }catch(Exception e){
        e.printStackTrace();
        //successImportingFromCSV=false; //Commeneted out for ease
        Log.e(LOG_TAG, "Failed to restore from csv " + e);
    }
}

Then making use of some generic database/cursor routines from Are there any methods that assist with resolving common SQLite issues? the following is run :-

    mPHlpr = new ProductsDBHelper(this); //<<<< DBHelper
    mPHlpr.importSomeDBfromCSV(); //<<<< Do the import
    SQLiteDatabase db = mPHlpr.getWritableDatabase(); //<<<< Get DB
    CommonSQLiteUtilities.logDatabaseInfo(db); //<<< Inspect DB
    Cursor csr = CommonSQLiteUtilities.getAllRowsFromTable(db,ProductsDBHelper.TABLE_NAME,false,null); //<<<< Get Cursor
    CommonSQLiteUtilities.logCursorData(csr); //<<<< Inspect Cursor

Output/Conclusion

Part 1 - Output from importSomeDBfromCSV method.

02-08 06:04:26.773 1555-1555/? D/ImportDB: Read in line :-
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = Baked beans
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = 12.56
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = 50
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = 10
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = barcode
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = London
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = Tinned Foods
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = red beany like things in sauce
02-08 06:04:26.773 1555-1555/? D/ImportDB: Read in line :-
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = Yellow Fin Tuna 12g tin
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = 5.76
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = 100
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = 12
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = barcode
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = New York
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = Tinned Foods
02-08 06:04:26.773 1555-1555/? D/ImportDB: Data extracted for a column = fishy stuff
02-08 06:04:26.777 1555-1555/? V/ImportDB: Restore is successful
  • All looks Ok i.e. data extarcted into columns as expected.

Part 2 - Output from logDatabaseInfo

02-08 06:04:26.781 1555-1555/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/examples.mjt.sqliteassethelperexample/databases/products
02-08 06:04:26.781 1555-1555/? D/SQLITE_CSU: Database Version = 1
02-08 06:04:26.781 1555-1555/? D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table Name = products Created Using = CREATE TABLE products(_id INTEGER PRIMARY KEY, productname TEXT, productprice REAL, productquantity INTEGER, productsold INTEGER, productbarcode TEXT, productlocation TEXT, productcategory TEXT, productnote TEXT)
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = _id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = productname ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = productprice ColumnType = REAL Default Value = null PRIMARY KEY SEQUENCE = 0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = productquantity ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = productsold ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = productbarcode ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = productlocation ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = productcategory ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Table = products ColumnName = productnote ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
  • As expected

Part 3 - Output from logCursorData method

02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: logCursorData Cursor has 2 rows with 9 columns.
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Information for row 1 offset=0
                                                For Column _id Type is INTEGER value as String is 1 value as long is 1 value as double is 1.0
                                                For Column productname Type is STRING value as String is Baked beans value as long is 0 value as double is 0.0
                                                For Column productpriceType is FLOAT value as String is 12.56 value as long is 12 value as double is 12.56
                                                For Column productquantity Type is INTEGER value as String is 50 value as long is 50 value as double is 50.0
                                                For Column productsold Type is INTEGER value as String is 10 value as long is 10 value as double is 10.0
                                                For Column productbarcode Type is STRING value as String is barcode value as long is 0 value as double is 0.0
                                                For Column productlocation Type is STRING value as String is London value as long is 0 value as double is 0.0
                                                For Column productcategory Type is STRING value as String is Tinned Foods value as long is 0 value as double is 0.0
                                                For Column productnote Type is STRING value as String is red beany like things in sauce value as long is 0 value as double is 0.0
02-08 06:04:26.785 1555-1555/? D/SQLITE_CSU: Information for row 2 offset=1
                                                For Column _id Type is INTEGER value as String is 2 value as long is 2 value as double is 2.0
                                                For Column productname Type is STRING value as String is Yellow Fin Tuna 12g tin value as long is 0 value as double is 0.0
                                                For Column productpriceType is FLOAT value as String is 5.76 value as long is 5 value as double is 5.76
                                                For Column productquantity Type is INTEGER value as String is 100 value as long is 100 value as double is 100.0
                                                For Column productsold Type is INTEGER value as String is 12 value as long is 12 value as double is 12.0
                                                For Column productbarcode Type is STRING value as String is barcode value as long is 0 value as double is 0.0
                                                For Column productlocation Type is STRING value as String is New York value as long is 0 value as double is 0.0
                                                For Column productcategory Type is STRING value as String is Tinned Foods value as long is 0 value as double is 0.0
                                                For Column productnote Type is STRING value as String is fishy stuff value as long is 0 value as double is 0.0
  • Note each column has values as obtained via methods getString, getLong and getDouble.
  • Additional

Adding a line, resulted in Bad Row Skipped, as follows :-

,,,,,,,

Adding a line, resulted in Bad Row Skipped, as follows :-

Bananas,,,bardcode,Oxford,Fresh produce,yellow bendy things
0
Scooltr On

I've found that all the columns were copied to db enclosed by(""). So I changed my code to : 

.................................................
     sqLite.beginTransaction();

                    try {
                        while ((line=buffer.readLine()) != null) {
                            String[] values = line.split(",");

                            if(values.length != 8)
                                continue;

                            for(int i = 0; i < values.length; i++)
                                values[i] = values[i].replace("\"", "");


                            ContentValues cv = new ContentValues();
                            cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_NAME,values[0]);
                            cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_PRICE, values[1].trim());
                            cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_QUANTITY, values[2].trim());
                            cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_SOLD, values[3].trim());
                            cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_BARCODE, values[4].trim());
                            cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_LOCATION, values[5].trim());
                            cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_CATEGORY, values[6].trim());
                            cv.put(ProductContract.ProductEntry.COLUMN_PRODUCT_NOTE, values[7].trim());
                            sqLite.insert(TABLE_NAME,null, cv);
                            Log.e("Inserted",cv.toString());

                        }
                    } catch (SQLException | IOException e) {
                        e.printStackTrace();
                        Log.e("Exception",e.getLocalizedMessage());
                    }

                    sqLite.setTransactionSuccessful();
                    sqLite.endTransaction();
                    successImportingFromCSV=true;
                    Log.v(LOG_TAG, "Restore is successful");

                        } catch (Exception e) {
                    successImportingFromCSV=false;
                    Log.e(LOG_TAG, "Failed to restore from csv " + e);
                }

.................................................

And now it works perfectly

Related Questions in ANDROID

Related Questions in SQLITE

Related Questions in CSV

Related Questions in CONTENT-VALUES

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json

Trending Questions