Import XLS file?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Import XLS file?

Winfried
Hello,

I need to import an XLS file into SQLite.

The  web site <https://sqlite.org/docs.html>   doesn't seem to show how, and
searching the archives  here <http://sqlite.1065341.n5.nabble.com/>   didn't
help.

FWIW, the data are in the file's first sheet, while other sheets only
contain text infos about the data.

Thank you.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import XLS file?

Simon Slavin-3
On 1 Oct 2019, at 7:47pm, Winfried <[hidden email]> wrote:

> I need to import an XLS file into SQLite.

I recommend a two-part solution, because so many programs understand CSV file format.

1) export your XLS data in CSV format
2) Use the SQLite shell tool to import the CSV data into your database

For part 2, see chapter 8 of

<https://sqlite.org/cli.html#csv_import>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import XLS file?

Warren Young
On Oct 1, 2019, at 12:46 PM, Simon Slavin <[hidden email]> wrote:
>
> 1) export your XLS data in CSV format
> 2) Use the SQLite shell tool to import the CSV data into your database

Beware that SQLite’s CSV import requires that the imported data have the same number of columns as the table you’re importing it into.  There is currently no way in the SQLite .import shell command to leave some SQLite table columns at their default values, so that you can insert “narrower” data into the table.

The main case I have where I run into this is with “id” columns, since you can’t then depend on SQLite’s autoincrement behavior.  I’ve always ended up generating an “id” column in the input file just to get around this.

I miss MySQL’s LOAD DATA INFILE: it let you give a VALUES clause so you could let one or more columns take their default value.  From the docs, it appears that PostgreSQL’s COPY…VALUES would also work.  I hope SQLite gets such a feature someday.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import XLS file?

Winfried
In reply to this post by Simon Slavin-3
Simon Slavin-3 wrote
> 1) export your XLS data in CSV format
> 2) Use the SQLite shell tool to import the CSV data into your database

Thanks.

Is there a free (beer|speech) tool to convert an XLS file into CSV, for
Windows or Linux that you would recommend?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import XLS file?

Simon Slavin-3
On 1 Oct 2019, at 7:58pm, Winfried <[hidden email]> wrote:

> Is there a free (beer|speech) tool to convert an XLS file into CSV, for Windows or Linux that you would recommend?

Not really the topic of this forum.

XLS is Microsoft Excel format.  Excel has an 'export as CSV' feature. So if you used Excel to make that file, you can use it to do the conversion.  You should be able to find at least 10 utilities to do the conversion with a web search.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import XLS file?

Jake
In reply to this post by Winfried
You might like to consider the xlsx virtual table I wrote last year:

https://github.com/jakethaw/xlsx_vtab

Note that this only works for xlsx files, and may have unexpected
results in some circumstances (e.g. merged cells).

On Wed, Oct 2, 2019 at 4:39 AM Winfried <[hidden email]> wrote:

>
> Hello,
>
> I need to import an XLS file into SQLite.
>
> The  web site <https://sqlite.org/docs.html>   doesn't seem to show how, and
> searching the archives  here <http://sqlite.1065341.n5.nabble.com/>   didn't
> help.
>
> FWIW, the data are in the file's first sheet, while other sheets only
> contain text infos about the data.
>
> Thank you.
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import XLS file?

skywalk
If you are uncomfortable with exporting to csv and importing csv from the
command line,
you could install DB Browser for SQLite,
https://github.com/sqlitebrowser/sqlitebrowser.
Then manually build your SQLite tables and cut and paste into them from the
spreadsheet.

On Tue, Oct 1, 2019 at 8:58 PM Jake Thaw <[hidden email]> wrote:

> You might like to consider the xlsx virtual table I wrote last year:
>
> https://github.com/jakethaw/xlsx_vtab
>
> Note that this only works for xlsx files, and may have unexpected
> results in some circumstances (e.g. merged cells).
>
> On Wed, Oct 2, 2019 at 4:39 AM Winfried <[hidden email]> wrote:
> >
> > Hello,
> >
> > I need to import an XLS file into SQLite.
> >
> > The  web site <https://sqlite.org/docs.html>   doesn't seem to show
> how, and
> > searching the archives  here <http://sqlite.1065341.n5.nabble.com/>
>  didn't
> > help.
> >
> > FWIW, the data are in the file's first sheet, while other sheets only
> > contain text infos about the data.
> >
> > Thank you.
> >
> >
> >
> > --
> > Sent from: http://sqlite.1065341.n5.nabble.com/
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Import XLS file?

Dingyuan Wang
In reply to this post by Winfried
There is FreeXL (https://www.gaia-gis.it/fossil/freexl/index)
may suit your need.

2019/10/2 02:58, Winfried:

> Simon Slavin-3 wrote
>> 1) export your XLS data in CSV format
>> 2) Use the SQLite shell tool to import the CSV data into your database
>
> Thanks.
>
> Is there a free (beer|speech) tool to convert an XLS file into CSV, for
> Windows or Linux that you would recommend?
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users