ImportXLS files

[Kyle Felipe Vieira Roberto]

Hi guys, again!

I was using, in python, XLRD to import some xls sheets to a spatialite, but it taking too long (800 rows, 6 minutes).

Using SELECT ImportXLR it takes only 2-3 SECONDS (WOOOOW), but i have a issue, my sheet have some rows that i need to ignore (2 rows) and the 3th i need to be the colunm's names, 

and i need to limit the colunms to a total (15)
how i do this using the FreeXLS???
I can work with XLSX files also?

I realy need to speed up this....

[a.fu...@lqt.it]

On Thu, 14 Dec 2017 07:26:09 -0800 (PST), Kyle Felipe Vieira Roberto
wrote:
> Hi guys, again!
>

Hi Kile,

> I was using, in python, XLRD to import some xls sheets to a
> spatialite, but it taking too long (800 rows, 6 minutes).
> Using SELECT ImportXLR it takes only 2-3 SECONDS (WOOOOW),
>

if I'm not wrong XLRD is a standard Python package supporting
Excel spreadsheets.
I was expecting that an implementation fully based on Python
should be someway slower that an equivalent C implementation,
but in this case the difference is really impressive.

> but i have
> a issue, my sheet have some rows that i need to ignore (2 rows) and
> the 3th i need to be the colunm's names,
> and i need to limit the colunms to a total (15)
> how i do this using the FreeXLS???
>

try using the VirtualXL driver, that is intended to
directly query an external XLS spreadsheet without
requiring to import the data within the DBMS.

once you've created a VirtualXL source you can then
query it using standard SQL, and this will make
easy and simple performing a customized import:
you can filter just a limited set of rows, you can
eventually ignore some columns and so on.
just a quick commented example:

CREATE VIRTUAL TABLE my_virt_xls USING
VirtualXL ( 'c:/my_spreadsheets/sample.xls' , 0 , 0 );

- the first argument is an absolute or relative path
pointing to the external XLS input spreadsheet.
- the second argument is the WorksheetIndex
- the third argument determines if the first line
contains column names or not (1=yes / 0=no)


SELECT * FROM my_virt_xls;

- as you can see, you can now query the VirtualXL table
using plain SQL statements.


CREATE TABLE my_table (
... freely define your columns here ...
);

- now you can create the final destination table
where to really store the imported data.


INSERT INTO my_table
SELECT col_3, col_4, col_10, col_22, col_23
FROM my_virt_xls
WHERE row_no BETWEEN 4 AND 100;

- now you can populate the final destination table
by freely selecting only the really interesting
columns from a limited number of rows.


DROP TABLE my_virt_xls;

- and finally you can drop the no longer useful
VirtualXL driver.

> I can work with XLSX files also?
>

absolutely no: FreeXL just supports the historical XLS format,
that was a reasonably simple and unsophisticated binary format.
the more recent XLSX format is fully based on XML thus
requiring a completely different approach.

hint: you can rather easily convert your xlsx spreadsheets
into the xls format by using LibreOffice Calc or MS Excel.

bye Sandro

[Even Rouault]

> > I can work with XLSX files also?

>

> absolutely no: FreeXL just supports the historical XLS format,

> that was a reasonably simple and unsophisticated binary format.

> the more recent XLSX format is fully based on XML thus

> requiring a completely different approach.

>

> hint: you can rather easily convert your xlsx spreadsheets

> into the xls format by using LibreOffice Calc or MS Excel.

 

Other option: use OGR which supports the XLSX format

http://gdal.org/drv_xlsx.html

(and the XLS one as well, relying on FreeXL to do the low level work)

 

Even

--

Spatialys - Geospatial professional services

http://www.spatialys.com

[Kyle Felipe Vieira Roberto]

Guys, thx so much for help me!

@Sandro

the whay that you show its much more faster (faster than light kkkk).

@ALL

Our Standarts here is , for while, only receive data in xls files, (laws, LOL), and next year we hope make an app that allow people send spatialiite files.

Thank you all for help...