Geek Answers Handbook

Export sqlite database file to XML and then to Excel spreadsheet

I am trying to accomplish this task in the last 12 hours, but still can not do it ... Now I have a sqlite database file named "testDatabase.db" on the SD card as a backup file. Now I want to open the database in a spreadsheet format so that the end user can access the database and save this table in their system, and then further for printing and for other purposes.

I studied some of the tutorials that wrote that you can first convert the ur database file to XML and then export this file to excel format.

But I can’t export my data file to XML format ... As in accordance with my requirement, I want my button to convert my database file to XML format so that the end user can use it.

Thanks..

+5
source share
4 answers

As far as I know, there is no "one click" for this. You can create a .bat script to export each table to a CSV or html table ( commands can be found here ), and then import them into excel.
Or you can download software such as sqlite database browser, open your .db file, and then export each table to a csv file.

0
source

I know this may be a bit late, but if someone else is looking for this and sees this question, the best answer would be to export the database to a CSV file (comma delimited).

sqlite3 has a function just for this, called a "quote". You can read about it here:

http://www.sqlite.org/lang_corefunc.html#quote

Example:

create table t1 (c1 char, c2 int);

insert into the values ​​t1 ('a hi', 1);

insert into the values ​​of t1 ('b hello', 2);

select a quote (a) || ',' || b from t1;

this will return: 'a hi', 1 'b hello', 2

There are other ways to use the quote function, but it really depends on your data and how you want to use it.

+3
source

Should the conversion happen on the Android device?

If you are more flexible on how to do this, you can provide the user with a sqlite database file, and they can import data through the sqlite ODBC driver: http://www.ch-werner.de/sqliteodbc/

+2
source

Although this is not the technical generation of XML, you can easily skip this middle step and create an Excel spreadsheet directly from the command line.

Most table applications can import HTML tables and conveniently sqlite3 has a command line option to export queries in this format.

 echo "<HTML> <BODY> <TABLE>" > spreadsheet.xls sqlite3 -html sqlite.db "select * from table;" >> spreadsheet.xls echo "</TABLE> </BODY> </HTML>" >> spreadsheet.xls

For example, when I open this spreadsheet file in LibreOffice, I get the Import option. By clicking "Yes", the file opens like a regular spreadsheet. You can then tweak the formatting and reinstall if necessary to make it a true XML-compressed Excel file.

+1
source

More articles:


All Articles