Android sqllite csv to database
to import data from a CSV file into an android database, use the following code
Note: set your csv file name to match your database table name
public boolean csvTodatabase() {
FileStuff f = new FileStuff();
//check if extarnal drive is readerble
if (!f.isExternalStorageReadable()) {
f.fileError = "can not read external storage";
f.fileinfo = "Please remount your SD card";
return false;
}
//get all files from extarnal drive data directory
ArrayList<File> files = new ArrayList<File>();
File directory = new File(FileStuff.DATA_DIRECTORY);
if (!directory.exists()) {
return false;
}
File[] fList = directory.listFiles();
for (File file : fList) {
if (file.isFile()) {
files.add(file);
}
}
for (File csvfile : files) {
readFromFile(csvfile);
}
return true;
}
private void readFromFile(File file) {
boolean gotColunms = false;
String tableName = file.getName().replaceAll(".csv$", ""), sql, colunmNames="",colunmValues;
try {
BufferedReader br = new BufferedReader(new FileReader(file));
String line;
SQLiteDatabase database = this.getWritableDatabase();
while ((line = br.readLine()) != null) {
if (!gotColunms) {
//get colunm names
line = line.replaceAll("\"", "");
colunmNames = line;
gotColunms = true;//set flag to show we have colunms
} else {
line = line.replaceAll("\"", "'");
colunmValues = line;
//create sql query
sql = "INSERT INTO " + tableName + " ("+colunmNames+") VALUES ("+colunmValues+")";
//update datebase tables
database.execSQL(sql);
}
}
database.close();
br.close();
} catch (IOException e) {
//You'll need to add proper error handling here
}
}
&amp;amp;amp;nbsp;
any CSV file found in the data directory, will be read and the records will be inserted into the corresponding table whose name is similar to the file name. E.G. if you have a file called table_one.csv the records will be added into a database table called table_one. make sure that this table already exists in the database
look for code on how to create the CSV here
{ 6 comments… read them below or add one }
Is there a way just to select one csv file, like not all of the csv on a directory
you can change the logic to just get one file then call this function, readFromFile(csvfile); with the file.
just add a simple check before files.add(file); this will ensure only your file is added
how we will define the names of the different columns in the database table ?
the first row in the csv file, defines the table colunms
FileStuff cannot be resolved into a type.. What do i need to do ?
FileStuff is a class in my code below is the code
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.your.package;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.net.Uri;
import android.os.Environment;
import android.preference.PreferenceManager;
import android.util.Base64;
import android.util.Log;
import android.widget.Toast;
import java.io.ByteArrayOutputStream;
import java.io.File;
import static java.io.File.separator;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.nio.channels.FileChannel;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.zip.GZIPOutputStream;
/**
*
* @author
* freelancer
*/
public class FileStuff {
private static final String EXTERNAL_DIRECTORY = Environment.getExternalStorageDirectory().toString();//text
public static final String DATA_DIRECTORY = EXTERNAL_DIRECTORY + separator + "your dir" + separator + "Data";
private static final String INNER_DELIMITER = ",";//text
private static final String QUOTES = "\"";//text
private static final String LINE_END = "\n";//text
public String fileError, fileinfo, TAG = this.getClass().getSimpleName();
;
/* Checks if external storage is available for read and write */
private boolean isExternalStorageWritable() {
String state = Environment.getExternalStorageState();
if (Environment.MEDIA_MOUNTED.equals(state)) {
return true;
}
return false;
}
/* Checks if external storage is available to at least read */
public boolean isExternalStorageReadable() {
String state = Environment.getExternalStorageState();
if (Environment.MEDIA_MOUNTED.equals(state)
|| Environment.MEDIA_MOUNTED_READ_ONLY.equals(state)) {
return true;
}
return false;
}
/* Create data dir or return it */
private File getDataDir(String tableCsv) throws IOException {
// Get the directory for the app data directory.
String f = tableCsv + ".csv";
File dataDirectory = new File(DATA_DIRECTORY, f);
dataDirectory.delete();
if (!dataDirectory.exists()) {
File path = new File(DATA_DIRECTORY);//create directory
path.mkdirs();
if (!dataDirectory.createNewFile()) {
Log.e("getStorageDir", "file " + f + " not created");
fileError = "file " + f + " not created";
fileinfo = "unable to create backup file";
return null;
}
}
return dataDirectory;
}
public boolean createCsvSaveToFile(Cursor cursor, String fileName, Context c) throws IOException {
String ccsv = "", csv = "";
int colunmCount = cursor.getColumnCount(), i;
//check if extarnal drive is readerble
if (!isExternalStorageWritable()) {
fileError = "Can not save to external storage";
fileinfo = "Please mount your SD card";
return false;
} else {
//create CSV
for (i = 0; i < colunmCount; i++) {//GET COLUNM NAMES
csv += QUOTES + cursor.getColumnName(i).toString() + QUOTES + INNER_DELIMITER;
}
csv = csv.replaceAll(",$", "");
csv += LINE_END;
if (cursor.moveToFirst()) {
do {
for (i = 0; i < colunmCount; i++) {//GET COLUNM values
ccsv += QUOTES + cursor.getString(i) + QUOTES + INNER_DELIMITER;
}
ccsv = ccsv.replaceAll(",$", "");
//encrypt
ccsv = encry(ccsv, keey);
csv += ccsv;
ccsv = "";
csv += LINE_END;
} while (cursor.moveToNext());
}
//save file
File file = getDataDir(fileName);
FileOutputStream out = new FileOutputStream(file);
out.write(csv.getBytes());
out.flush();
out.close();
if (!cursor.isClosed()) {
cursor.close();
}
return true;
}
}
}