[sqlite] TEST: please ignore and delete

[Winfried]

I'm just checking that the list is again reachable from Nabble after an admin
changed the mail address from @sqlite.org to @mailinglists.sqlite.org

http://sqlite.1065341.n5.nabble.com/

Admins: If need be, please delete this message later.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/TEST-please-ignore-and-delete-tp96561.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Winfried]

Hello,

I have a bunch of hyperlinks in an SQLite database, and need to read each
page for validation before deleting the record.
To make it easier, the DB manager should launch the default web browser when
I double click on a column that contains a hyperlink.

Before I build a GUI, is there a Windows SQLite database manager that can do
this?

I tried DB Browser for SQLite, SQLitespeed, and SQLiteStudio, but none seems
to support this feature.

Thank you.



--
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] SQLite Windows GUI alternative to Excel?

[Winfried]

Hello,

After reading this article…

"In the workplace, spreadsheet experts face a constant barrage of help
requests"
https://www.wsj.com/articles/the-first-rule-of-microsoft-exceldont-tell-anyone-youre-good-at-it-1538754380

… I'd like to check if there is a Windows GUI available as a valid
alternative to Excel, either free or commercial, that would let people
import XLS data and provide most of the Excel features.

Thank you.



--
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

Re: [sqlite] Simple way to import GPX file?

[Winfried]

Thanks to both of you. Problem solved :-)



--
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

Re: [sqlite] Simple way to import GPX file?

[Winfried]

Good call, thank you.

For others' benefit:

1. Copy the file, open the copy in a text editor, use a regex to turn the
data into tab-separated columns

2. Create a new file, and create the table:
sqlite3 waypoints.sqlite

sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id
INTEGER PRIMARY KEY);

3. Import data:
sqlite> .separator "\t"
sqlite> .import waypoints.tsv waypoints
select * from waypoints where id=1;



--
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] Simple way to import GPX file?

[Winfried]

Hello,

I need to importe a GPX file that contains a few thousand waypoints, eg.
Some name

I tried https://mygeodata.cloud and https://geoconverter.hsr.ch, but for
some reason, the "waypoints" table doesn't contain latitude + longitude
infos.

Before I write a Python script, is there a simple way to import GPX data
into SQLite?

Thank you.




--
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

Re: [sqlite] Import XLS file?

[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] Import XLS file?

[Winfried]

Hello,

I need to import an XLS file into SQLite.

The  web site    doesn't seem to show how, and
searching the archives  here    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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does Python turn UTF8 data in SQLite into Latin1?

[Winfried]

Sorry. Windows 7 32 bits US + with French locale + Python 3.7.0.

Turns out it had nothing to do with SQLite, and everything to do with
Python's write(), which uses the default locale, so the following is
required to output data as UTF-8 instead of cp1252 used on this computer:


testoutput = open("check.from.Python.txt", "w",,encoding='UTF-8')


Thank you.



--
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] Why does Python turn UTF8 data in SQLite into Latin1?

[Winfried]

Hello,

For some reason, the following script turns UTF8 data in SQLite into Latin1:

==
import sqlite3

"""
OK!
sqlite3 mydb.sqlite
.output check.from.sqlite.txt
select * from table1;
"""

testoutput = open("check.from.Python.txt", "w")

con = sqlite3.connect('mydb.sqlite')
con.row_factory = sqlite3.Row
cur = con.cursor()

cur.execute("SELECT * FROM table1");
results = cur.fetchall()
for row in results:
testoutput.write(row["name"])
==

Did I miss a switch somewhere to tell Python to leave encoding alone?

Thank you.



--
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] Can SQLite import Latin1 data?

[Winfried]

Hello,

I have a big CSV file that's encoded in Latin1 (cp1252), while SQLite stores
strings as Unicode.

Neither Google nor ".help" helped to find if SQLite offers a switch to
convert Latin1 to UTF-8 on the fly before running the ".import" command.

Should I first convert the file into UTF-8 before importing it into SQLite?

Thank you.



--
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

Re: [sqlite] Can SQLite import Latin1 data?

[Winfried]

It's odd that SQLite doesn't support this, since it's not uncommun to have
big Latin1 input files that can take a while to load in eg. Excel.

For others' benefit, GNUWin32's iconv is very fast:

"C:\Program Files\GnuWin32\bin\iconv.exe" -f "windows-1252" -t "UTF-8"
"input.1252txt" > "output.UTF8.txt"

Thank you.



--
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

Re: [sqlite] [Importing CSV] Empty colums != NULL?

[Winfried]

For other newbies' benefit: Yes, indeed, importing empty columns doesn't set
them to NULL.

UPDATE Shops SET "contact:postcode" = NULL WHERE "contact:postcode" = "";
UPDATE Shops SET "addr:postcode" = NULL WHERE "addr:postcode" = "";

SELECT name, IFNULL("contact:postcode","addr:postcode") zipcode from Shops
where id="251373376";
Acme;12345



--
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] [Importing CSV] Empty colums != NULL?

[Winfried]

Hello,

I imported data from a CSV file.

Some of the columns are empty, so I expected them to be set to NULL, but it
looks like it's not how SQLite handles them internally:

=
sqlite> .schema
CREATE TABLE Shops(
  "id" TEXT,
  "name" TEXT,
  "contact:postcode" TEXT,
  "contact:city" TEXT,
  "addr:postcode" TEXT,
  "addr:city" TEXT,
  "contact:phone" TEXT,
  "email" TEXT,
  "website" TEXT
);
=

Here's a record where "addr:postcode" is filled, but "contact:postcode" is
not:
=
sqlite> select * from Shops limit 10;
id;name;contact:postcode;contact:city;addr:postcode;addr:city;contact:phone;email;website
251373376;Acme;;;12345;My City;;;http://www.acme.com
etc.
=

But SQLite returns an empy column when using IFNULL:
=
sqlite> select name, IFNULL("contact:postcode","addr:postcode") zipcode from
Shops where id="251373376";
name;zipcode
Acme;
=

Should I use a different command when importing data or running IFNULL?

Thank you.



--
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] [PHP+PDO] SELECT fails when using prepare + placeholder

[Winfried]

Hello,

I can't figure out why PHP isn't happy with the following query:

==
prepare('INSERT INTO mytable (date, home_id, mode_id) VALUES
(?, ?, ?)');
$query->execute(array($today,$home, $mode));

//CREATE TABLE mode (mode_id INTEGER, mode_txt TEXT);
//SELECT OK
//$query = $dbh->prepare("SELECT mode_txt FROM mode WHERE mode_id=0 LIMIT
1");

//NOK !
$query = $dbh->prepare("SELECT mode_txt FROM mode WHERE mode_id=? LIMIT 1");
$result=$query->execute(array($mode));
if ($result) {
//! Fatal error: Call to a member function fetch() on boolean in 
blah.php
$mode = $result->fetch(PDO::FETCH_COLUMN);
}   

$dbh = null;
?>
==

Is it due to some incompatibility between SQLite's INTEGER and PDO?

Thank you.



--
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

Re: [sqlite] [PHP+PDO] SELECT fails when using prepare + placeholder

[Winfried]

Zakrzewski, Jakub wrote
> It's because `execute()` returns a boolean (as stated in the manual
> https://www.php.net/manual/en/pdostatement.execute.php ). `fetch()` must
> be called on `$query`

Thanks much!

=
$query = $dbh->prepare("SELECT mode_txt FROM mode WHERE mode_id=? LIMIT 1");
$query->execute(array($mode));
$result = $query->fetch(PDO::FETCH_ASSOC);
if($result){
print($result["mode_txt"] . ""); 
} else
print("Bad!");
=



--
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] Can SQLite do this in a single query?

[Winfried]

Hello,

Before I dive into https://www.sqlitetutorial.net, I'd like to check with
experienced users whether SQLite is up to the task, or I should maybe run
multiple queries possibly with some help from PHP.

Using the following tables, I need to find how employees from each city come
to work.
== Employees table:
EMPLOYEE_ID | CITY_ID
Cities table:
CITY_ID | CITY_TXT
Mode table:
MODE_ID | MODE_TXT

This is the type of output I need to get ultimately:
CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER
City1 | 15% | 5% | 50% [ 25% | 5%

Do you think it can it be done in a single query using a mixture of COUNT(),
SUM(), GROUP BY, HAVING etc., or will I have to split the task into a few
independent queries?

Thank you.



--
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

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Thank you very much to all. There's no way I could have come up with those
queries.

Considering the wealth/complexity of SELECT even in SQLite, it seems a whole
book could be written on just that command.

https://www.sqlite.org/images/syntax/select-stmt.gif

Is there a good book you would recommend to learn about SQLite, especially
regarding SELECT?



--
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

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Simon Slavin-3 wrote
> https://sqlite.org/books.html

Thanks.

"You have to paint the output table yourself." "You still have to "paint the
fence" yourself, though now the table data is not sparse, thus easier to
paint"

Looks like "paint" is DB lingo: Does it mean formatting the output after
running the right SELECT ?



--
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

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Keith Medcalf wrote
> Well, "paint" is to draw your output.

Thank you.




--
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

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Yes, I forgot the column mode_id in the Employee's table.

Thanks, I'll read up on the features SQLite's SELECT has to offer. At worst,
I'll just run a simpler query multiple times.

https://www.sqlite.org/lang_select.html



--
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

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Winfried [via SQLite]]

Thanks for the infos.




__
If you reply to this email, your message will be added to the discussion below:
http://sqlite.1065341.n5.nabble.com/How-to-search-for-fields-with-accents-in-UTF-8-data-tp96249p96294.html
This email was sent by Winfried (via Nabble)
To receive all replies by email, subscribe to this discussion: 
http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=subscribe_by_code=96249=c3FsaXRlLXVzZXJzQG1haWxpbmdsaXN0cy5zcWxpdGUub3JnfDk2MjQ5fC0xNDUwNjI0MDQ5
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users