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);
}
}
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
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
logDatabaseInfo02-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
logCursorData method02-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
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