Re: [sqlite] Import XLS file?

[Dingyuan Wang]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite trig/geometry error

[Dingyuan Wang]

Hello,

Obviously these trig functions comes from SpatiaLite.

http://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.0.html

2019/1/4 1:01, Brent Wood:
> Yep, either a SQL file run from a shell script or SQL's directly piped into 
> the sqlite3 command, eg:
> 
> 
> 
> echo "insert into qgis_gps
> (stat_code, station_no, point_type, depth, geom) 
>values 
> ('R1A1',2,'s',23, setsrid(makepoint(175.5,-43.5),4326));" | \
>sqlite3 -cmd "SELECT load_extension('mod_spatialite');" $DB
> 
> Thanks...
> 
> 
> From: Clemens Ladisch 
> To: sqlite-users@mailinglists.sqlite.org 
> Sent: Thursday, January 3, 2019 10:52 PM
> Subject: Re: [sqlite] sqlite trig/geometry error
> 
> 
> 
> Brent Wood wrote:
> 
>> Is there an easy way to tell where the sqlite math functions are coming from?
> 
> 
> Are you using the sqlite3 command-line shell, or something else?
> 
> 
> 
> Regards,
> 
> Clemens
> 
> ___
> 
> sqlite-users mailing list
> 
> sqlite-users@mailinglists.sqlite.org
> 
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Doing math in sqlite

[Dingyuan Wang]

You can use recently supported window function (portable):

SELECT * FROM (
  SELECT car, lag(date) OVER w last_date, date,
od_reading - (lag(od_reading) OVER w) diff
  FROM mileage
  WINDOW w AS (PARTITION BY car ORDER BY date)
) q
WHERE diff IS NOT NULL;

2018/12/21 11:48, Jungle Boogie:
> On Thu 20 Dec 2018  6:26 PM, Barry Smith wrote:
>>
>>
>>> On 20 Dec 2018, at 4:21 pm, Jungle Boogie  wrote:
>>>
>>> Hi All,
>>>
>>> Some sample data:
>>> 2018/04/15,foo,170644
>>> 2018/04/15,bar.69625
>>> 2018/04/22,foo,170821
>>> 2018/04/22,bar,69914
>>> 2018/04/29,foo,171006
>>> 2018/04/29,bar,70123
>>> 2018/05/06,foo,171129
>>> 2018/05/06,bar,70223
>>> 2018/05/09,foo,171178
>>> 2018/05/20,foo,171304
>>> 2018/05/20,bar,70517
>>>
>>
>> I wouldn't call my solution elegant, but if you put a unique constraint on 
>> the "date" column, and want your query to return null if there is not a 
>> record exactly on that date:
>>
>> SELECT
>>(SELECT od_reading FROM mileage WHERE date = '2018/05/20' AND car='bar')
>>-
>>(SELECT od_reading FROM mileage WHERE date='2018/05/06' AND car='bar')
>> As ExactMileage
>>
>> You can rephrase that as:
>> SELECT end.od_reading - start.od_reading FROM mileage AS start, mileage AS 
>> end WHERE end.date='2018/05/20' AND start.date='2018/05/06' AND end.car 
>> ='bar' and start.car='bar'
>>
>> Or even you could use CTEs to repeat yourself less:
>> WITH OnlyBar AS SELECT * FROM mileage WHERE car='bar'
>> SELECT end.od_reading - start.od_reading FROM OnlyBar AS start, OnlyBar AS 
>> end WHERE end.date='2018/05/20' AND start.date='2018/05/06
>>
>> If you might query between two dates that don't necessarily have a record, 
>> the best you can do is give a range of what the possible driven distance may 
>> be:
>> SELECT
>>(SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/20' AND 
>> car='bar')
>>-
>>(SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/06' AND 
>> car='bar')
>> AS MaxPossible,
>>(SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/20' AND 
>> car='bar')
>>-
>>(SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/06' AND 
>> car='bar')
>> AS MinPossible
>>
>> The second query finds the records before and after the dates in question 
>> (or uses data on exactly that date) to figure out the minimum and maximum 
>> possible distances that may have been driven between the dates of interest.
>>
>> Note that there is a more efficient form of the second query, where one 
>> selects just od_reading and orders by date, with a limit of 1 instead of 
>> using the MIN and MAX functions; if your table is going to be large then 
>> that is a better option (with an index on (car, date), the value can then be 
>> computed with an index seek instead of a table scan). I used the min/max 
>> version because it's simpler and easier to read
>>
> 
> Thanks all for for the helpful replies!
> 
> All of them work, as expected, but I see I wasn't exactly clear with my second
> part of the question.
> 
> Is it possible to determine how many miles were driven the previous week with
> current week's data in one query.
> 
> For example with foo:
> 2018/05/20 - 2018/05/09 = 126
> 2018/05/09 - 2018/04/29 = 172
> 2018/04/29 - 2018/04/22 = 185
> 
> Basically some kind of for loop through all the matches of 'foo'
> 
>>>
>>> Thanks,
>>> j.b.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP!

[Dingyuan Wang]

There are some cloud solutions:
https://dbhub.io/ for sharing
https://bedrockdb.com/ for distributed hosting

2018/11/11 8:49, am...@juno.com:
> November 10, 2018 Dear Good People: I work for a company that has a many 
> locations with more than one person in every location. I want to share the 
> databases I have built using SQLITE with some of the people in each location. 
> Do any of you good people know is SQLITE on the cloud? lf so, I  would be 
> most appreciative if you would tell me how to access it. 2) If SQLITE is on 
> the cloud, how do I restrict access--so that one information (not like 
> spelling errors) is inputted, the information cannot be removed. 3) Is there 
> a spell checker function on SQLITE? ?if so, please advise very specifically 
> how to find and use it. If any of you good people can help me solve these 
> dilemmas, I would be most appreciative. Thank you very much in advance for 
> helping me solve these problems. Respectfully yours, Alex Stavis
> 
> 1 Simple Trick Removes Lip Lines & Eye Bags in Seconds
> Fit Mom Daily
> http://thirdpartyoffers.juno.com/TGL3131/5be77c818908f7c813b2est01vuc
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] forming sqlite3_statements using stored sql

[Dingyuan Wang]

It seems that SpatiaLite (an extension for GIS) plans to support that:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Stored+Procedures

2018-11-02 03:53, Thomas Kurz:
>> Rather than inserting the SQL into a user-visible table, it might be
> preferable to support CREATE PROCEDURE.
> 
> +1 for that
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database identified as zip

[Dingyuan Wang]

It opens correctly now.

2018-10-30 22:37, Richard Hipp:
> On 10/30/18, Dingyuan Wang  wrote:
>>
>> Is this considered a bug?
> 
> Should be fixed on trunk, now.
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database identified as zip

[Dingyuan Wang]

Dear list,

When my database opened with sqlite3 command line program, it shows this:

SQLite version 3.26.0 2018-10-29 21:01:28
Enter ".help" for usage hints.
sqlite> .schema
CREATE VIRTUAL TABLE zip USING zipfile('funddata.db')
/* zip(name,mode,mtime,sz,rawdata,data,method) */;
sqlite> select * from zip;
Error: failed to read CDS at offset 119844

When I use python to open this database, it reads correctly.

There are a lot of zip files stored in this "datasave" table, where v is
the zip file.

CREATE TABLE datasave (k TEXT PRIMARY KEY, ver INTEGER, v BLOB)

I look at the database file, there is a half zip file in the end. So
maybe the PK header confused the SQLite command line.

Is this considered a bug?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python program to convert CSV to sqlite

[Dingyuan Wang]

Hi,

I've also written a similar script to convert csv to sql (SQLite and
PostgreSQL compatible). This script doesn't require fancy external
libraries, and will correctly identify data types.



2018-01-18 15:33, Simon Slavin:
> csvs-to-sqlite
> 
> 
> 
> Simple command line makes simple SQLite table definitions.  Or you can get 
> fancy with the command line and it will create tables linked with foreign 
> keys, or add a column with the filename to a table, or do other things.
> 
> I have not tried this program myself, I just thought it might be useful for 
> other people.
> 
> For those not familiar with GitHub, there’s a "clone or download" button 
> which lets you just download the project (source code) without having to 
> install any special client on your computer.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users