CODEWITHSUNDEEP
androidsqliteexport-to-csv
Hector
Hector

Reputation: 5356

Exporting Sqlite database table as csv from Android Application using sqlite3 programatically

I am attempting to add a Cloud Export/Import feature to my current Android Application.

I want to allow my users to export a particular Sqlite database table to Google Drive in csv format.

I realise I can perform a select statement and "manually" construct the csv file.

However I would like a approach that resembles running these sqlite3 commands

>sqlite3 c:/sqlite/chinook.db
sqlite> .headers on
sqlite> .mode csv
sqlite> .output data.csv
sqlite> SELECT customerid,
   ...>        firstname,
   ...>        lastname,
   ...>        company
   ...>   FROM customers;
sqlite> .quit

Ideally I would like to be able to directly export to Google sheets

Is it possible to package sqlite3 with my application and programmatically execute the above commands?

Upvotes: 0

Views: 1249

Answers (1)

Aditya
Aditya

Reputation: 3673

Is exporting possible, sqlite3 to csv?

Yeah, is it pretty simple operation, we can do this programatically through various method. As also answered here export sqlite into csv and here Exporting SQLite Database to csv file in android and also here How to implement Export sqlite To excel/csv file in android? and many answers and solutions are available on The StackOverFlow.

But how through sqlite3 commands?

If you really want to do it via commands, you can do it. But it is gonna be a really haptic way, because commands looks simple on terminal but they are really horrible programatically. You need super-user privilege or you need to install your application as a System application. If you really wanna do this, so lets start,

First lets us get the basics right. Android run Linux kernel underneath. Now if you have to run your process on it with super user privileges(run it as root) the only way is to execute your process is via command line because it is the only way you can directly interact with the kernel. Also you need to use su before running any command.

Process process = Runtime.getRuntime().exec("su");

will accomplish nearly nothing. It will just ask for super use privilege using dialog. What you can do is instead of just executing su you can execute your process with su as following

Process process = Runtime.getRuntime().exec(new String[] { "su", "-c", yourCommand});

So your commands goes like,

Process process = Runtime.getRuntime().exec(new String[] { "su", "-c", "sqlite3 /data/data/your-package-name/databases/file.db", ".headers on",".mode csv","...and all other operations"});

The -c Option

Among the most commonly used of su's few options is -c, which tells su to execute the command that directly follows it on the same line. Such command is executed as the new user, and then the terminal window or console from which su was run immediately returns to the account of the former user after the command has completed execution or after any program that it has launched has been closed.

Alternate Option

Alternative to above method one another way that might work is to use command line to copy you app to /system/app/ directory. Then your application will run automatically with root privileges(same as System apps)

Which way is preferable?

I personally like the first way, because working on shell commands in android application is not flexible. Now a days many providers blocks su commands on kernel level executing from third party applications.

Hope, this helps. Thanks!

Upvotes: 3

Related Questions