I need to import a csv file into sqlite database as a table. There are a lot of data in this csv file. I would like to know if there is a way to import such a lot of data programmatically in Kotlin way. I would appreciate any answer and comment.
{Edited} I tried to open a csv file but it shows "open failed" however there is no error showing in logcat. I have no idea where I did mistake. This is the code I tried.
DatabaseHelper Class
class DataBaseHelper(val context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
companion object{
private val DATABASE_NAME = "CSV2SQL"
private val DATABASE_VERSION = 1
private val TABLE_NAME = "table1"
private val COL_ID = "Id"
private val COL_COMPANY = "Company"
private val COL_PRODUCT = "Product"
private val COL_PRICE = "Price"
}
override fun onCreate(db: SQLiteDatabase) {
val CREATE_PRODUCTS_TABLE = ("CREATE TABLE " +
TABLE_NAME + "("
+ COL_ID + " INTEGER PRIMARY KEY," +
COL_COMPANY + " TEXT" +
COL_PRODUCT + "TEXT" +
COL_PRICE + "TEXT" +")")
db.execSQL(CREATE_PRODUCTS_TABLE)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME)
onCreate(db)
}
fun getAllProducts(): java.util.ArrayList<HashMap<String, String>> {
val proList: java.util.ArrayList<HashMap<String, String>>
proList = java.util.ArrayList()
val selectQuery = "SELECT * FROM $TABLE_NAME"
val db = this.writableDatabase
val cursor = db.rawQuery(selectQuery, null)
if (cursor.moveToFirst()) {
do {
//Id, Company,Name,Price
val map = HashMap<String, String>()
map["Id"] = cursor.getString(0)
map["Company"] = cursor.getString(1)
map["Name"] = cursor.getString(2)
map["Price"] = cursor.getString(3)
proList.add(map)
} while (cursor.moveToNext())
}
return proList
}
}
MainActivity Class
class MainActivity : ListActivity() {
internal lateinit var lbl: TextView
internal var db = DataBaseHelper(this)
internal lateinit var btnimport: Button
internal lateinit var lv: ListView
internal lateinit var myList: ArrayList<HashMap<String, String>>
val requestcode = 1
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
val txt = findViewById<TextView>(R.id.txtresulttext)
val mas = findViewById<Button>(R.id.btnupload)
lv = getListView()
mas.setOnClickListener {
val fileintent = Intent(Intent.ACTION_GET_CONTENT)
fileintent.type = "gagt/sdf"
try {
startActivityForResult(fileintent, requestcode)
} catch (e: ActivityNotFoundException) {
lbl.text = "No activity can handle picking a file. Showing alternatives."
}
}
myList = db.getAllProducts()
if (myList.size != 0) {
val lv = getListView()
var array = arrayOf("Company", "Name", "Price")
val adapter = SimpleAdapter(this,myList,
R.layout.v, array,intArrayOf(R.id.txtproductcompany, R.id.txtproductname, R.id.txtproductprice))
setListAdapter(adapter)
lbl.text = ""
}
}
override fun onActivityResult(requestCode: Int, resultCode: Int, data: Intent?) {
if (data == null)
return
if (requestCode <= requestcode){
val filepath = data.data!!.path
val db = db.getWritableDatabase()
val tableName = "table1"
db.execSQL("delete from $tableName")
try {
if (resultCode == Activity.RESULT_OK) {
try {
// db = DBHelper(applicationContext)
val file = FileReader(filepath!!)
val buffer = BufferedReader(file)
val contentValues = ContentValues()
var line = ""
db.beginTransaction()
while ({ line = buffer.readLine(); line }() != null) {
val str = line.split(",".toRegex(), 3)
.toTypedArray() // defining 3 columns with null or blank field //values acceptance
//Id, Company,Name,Price
val company = str[0]
val Name = str[1]
val Price = str[2]
contentValues.put("Company", company)
contentValues.put("Name", Name)
contentValues.put("Price", Price)
db.insert(tableName, null, contentValues)
lbl.text = "Successfully Updated Database."
}
db.setTransactionSuccessful()
db.endTransaction()
} catch (e: IOException) {
if (db.inTransaction())
db.endTransaction()
val d = Dialog(this)
d.setTitle(e.message.toString() + "first")
d.show()
// db.endTransaction();
}
} else {
if (db.inTransaction())
db.endTransaction()
val d = Dialog(this)
d.setTitle("Only CSV files allowed")
d.show()
}
} catch (ex: Exception) {
if (db.inTransaction())
db.endTransaction()
val d = Dialog(this)
d.setTitle(ex.message.toString() + "second")
d.show()
// db.endTransaction();
}
}
myList = db.getAllProducts()
if (myList.size != 0) {
val lv = getListView()
var array = arrayOf("Company", "Name", "Price")
val adapter = SimpleAdapter(this,myList,
R.layout.v, array,intArrayOf(R.id.txtproductcompany, R.id.txtproductname, R.id.txtproductprice))
setListAdapter(adapter)
lbl.text = "Data Imported"
}
}
This is the error message, Open Failed
