[sqlite] Import data from stdin to sqlite3 in python

Hi, I see this post uses Pandas to import data to sqlite3. https://datatofish.com/create-database-python-using-sqlite3/ But I don't want to make my code depend on Pandas. I'd like to use something like ".import /dev/stdin" to directly import data from stdin in python. Is it possible? Thanks. --

Re: [sqlite] mport SQLite import Latin1 data?

On 11/15/19 4:45 PM, Winfried wrote: > 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-12

Re: [sqlite] Can SQLite import Latin1 data?

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

Re: [sqlite] Can SQLite import Latin1 data?

On Nov 15, 2019, at 2:15 PM, Jose Isaias Cabrera wrote: > > Shawn Wagner, on Friday, November 15, 2019 04:01 PM, wrote... >> >> If you're on Windows, which cp1252 suggests, just make sure that you don't >> end up with a BOM at the start of the file when you convert it. Windows >> tools that outp

Re: [sqlite] Can SQLite import Latin1 data?

Unlike UTF-16, which uses 2 byte code units without a fixed endianess (meaning to be robust you need to account for both little and big endian encodings when reading files using it), UTF-8 uses a 1 byte code unit and thus doesn't have any endian issues or a need for a byte order mark. On Fri, Nov

Re: [sqlite] Can SQLite import Latin1 data?

Shawn Wagner, on Friday, November 15, 2019 04:01 PM, wrote... > > If you're on Windows, which cp1252 suggests, just make sure that you don't > end up with a BOM at the start of the file when you convert it. Windows > tools that output utf-8 are sometimes prone to add one even though it's > pointle

Re: [sqlite] Can SQLite import Latin1 data?

If you're on Windows, which cp1252 suggests, just make sure that you don't end up with a BOM at the start of the file when you convert it. Windows tools that output utf-8 are sometimes prone to add one even though it's pointless to have. On Linux etc., you can try .import '| iconv -f cp1252 -t ut

Re: [sqlite] Can SQLite import Latin1 data?

Winfried, on Friday, November 15, 2019 03:13 PM, wrote... > > 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

Re: [sqlite] Can SQLite import Latin1 data?

On 15 Nov 2019, at 20:13, Winfried wrote: > 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. > >

[sqlite] Can SQLite import Latin1 data?

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 befor

Re: [sqlite] Import XLS file?

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

Re: [sqlite] Import XLS file?

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 J

Re: [sqlite] Import XLS file?

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 wrote: > > Hello, > > I need to

Re: [sqlite] Import XLS file?

orting 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

Re: [sqlite] Import XLS file?

On 1 Oct 2019, at 7:58pm, Winfried 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 ma

Re: [sqlite] Import XLS file?

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

Re: [sqlite] Import XLS file?

On 1 Oct 2019, at 7:47pm, Winfried 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,

[sqlite] Import XLS file?

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 info

Re: [sqlite] Failing SQLite .import returns exit status 0

A constraint doing its job is not an error. On Fri, Jun 7, 2019, 7:49 PM Faheem Mitha wrote: > > Hi, > > Consider the following shell script: > > rm -f empty.db empty.csv > echo "" > empty.csv > echo "a, b" >> empty.csv > sqlite3 empty.db \ > 'DROP TABLE IF EXISTS empty;' \ > '.mode csv' \ > 'CR

Re: [sqlite] Failing SQLite .import returns exit status 0

On 8 Jun 2019, at 4:15pm, Adrian Ho wrote: > Except...it's not bailing on the failed import. Does the SQLite shell > consider failed dot-commands as errors? That's not what I expected. One for the developers. ___ sqlite-users mailing list sqlite-users

Re: [sqlite] Failing SQLite .import returns exit status 0

On 8/6/19 9:26 PM, Simon Slavin wrote: > Use > > .bail ON > > If the shell bails out because of an error, it should be setting the exit > code to indicate an error. Except...it's not bailing on the failed import. Does the SQLite shell consider failed dot-commands as errors? $ cat empty.sh #

Re: [sqlite] Failing SQLite .import returns exit status 0

On 8-6-2019 15:26, Simon Slavin wrote: Use .bail ON If the shell bails out because of an error, it should be setting the exit code to indicate an error. unfortunatly still 0 is returned  (sqlite3 version 3.28.0) ~/temp> cat empty.sh #!/bin/bash rm -f empty.db empty.csv echo "" > empt

Re: [sqlite] Failing SQLite .import returns exit status 0

Use .bail ON If the shell bails out because of an error, it should be setting the exit code to indicate an error. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user

Re: [sqlite] Failing SQLite .import returns exit status 0

On 8/6/19 10:49 AM, Faheem Mitha wrote: > rm -f empty.db empty.csv > echo "" > empty.csv > echo "a, b" >> empty.csv > sqlite3 empty.db \ > 'DROP TABLE IF EXISTS empty;' \ > '.mode csv' \ > 'CREATE TABLE IF NOT EXISTS empty(A, B NOT NULL);' \ > '.import empty.csv empty' '.exit' > echo "error code is

[sqlite] Failing SQLite .import returns exit status 0

Hi, Consider the following shell script: rm -f empty.db empty.csv echo "" > empty.csv echo "a, b" >> empty.csv sqlite3 empty.db \ 'DROP TABLE IF EXISTS empty;' \ '.mode csv' \ 'CREATE TABLE IF NOT EXISTS empty(A, B NOT NULL);' \ '.import empty.csv empty' '.exit' echo "error code is" $? This re

[sqlite] .import into databases other than main doesn't work

The .import command doesn't play well with attempts to use tables in schemas other than main: sqlite> .mode csv sqlite> .import test.csv temp.test Error: no such table: temp.test It creates the table if needed, but fails on the insert part because it tries to execute `

Re: [sqlite] .import fails CHECK constraint on valid data

> > For good or bad, check constraints appear to be evaluated before this > conversion. I call that very bad. On Wed, Mar 20, 2019 at 7:58 AM Shawn Wagner wrote: > A manual INSERT demonstrates the same behavior, actually. Using your Tc > table: > > sqlite> insert into Tc values ('12'); > Error:

Re: [sqlite] .import fails CHECK constraint on valid data

A manual INSERT demonstrates the same behavior, actually. Using your Tc table: sqlite> insert into Tc values ('12'); Error: CHECK constraint failed: Tc The thing about .import is that, instead of guessing what type each value it reads is, they're all just bound to an insert statement as strings.

Re: [sqlite] .import fails CHECK constraint on valid data

On Sun, 10 Mar 2019 17:04:46 -0400 "James K. Lowden" wrote: > Why does the .import command cause the CHECK constraint to fail, when > an ordinary INSERT does not? On Sun, 10 Mar 2019 14:12:33 -0700 Shawn Wagner wrote: > The check constraint is probably being evaluated (with t as a string) >

Re: [sqlite] .import fails CHECK constraint on valid data

The check constraint is probably being evaluated (with t as a string) before any type conversion to match the column affinity is done. On Sun, Mar 10, 2019, 2:05 PM James K. Lowden wrote: > $ sqlite3 db "create table T (t integer not null);" > $ sqlite3 db "create table Tc (t integer not null >

[sqlite] .import fails CHECK constraint on valid data

$ sqlite3 db "create table T (t integer not null);" $ sqlite3 db "create table Tc (t integer not null check(typeof(t) = 'integer'));" $ echo 1 > dat $ sqlite3 db ".import 'dat' T" $ sqlite3 db ".import 'dat' Tc" dat:1: INSERT failed: CHECK constraint failed: Tc $ s

Re: [sqlite] Import data into a temporary table

On Thursday, 7 March, 2019 17:12, D Burgess wrote: >The big downside of the csv extension, is that no matter what you do >your table ends up with every column with a type of text. >A trap if you are you are using union/except/intersect clauses. You are more or less but not entirely correct. If

Re: [sqlite] Import data into a temporary table

Not sure how I got this please remove me Sent from my iPad > On Mar 7, 2019, at 4:00 PM, Keith Medcalf wrote: > > > On Thursday, 7 March, 2019 14:45, Eric Tsau asked: > >> Is it possible to add the option of importing data into a temporary >> table? > >> Currently you have to create a tempo

Re: [sqlite] Import data into a temporary table

I should have said that "It appears that you are correct, the .import shell command does not know how to create a table in other than the default "main" schema" and that you are requesting this be changed so that the table being imported into does get created in the specified schema if a the ta

Re: [sqlite] Import data into a temporary table

The big downside of the csv extension, is that no matter what you do your table ends up with every column with a type of text. A trap if you are you are using union/except/intersect clauses. On Fri, Mar 8, 2019 at 11:00 AM Keith Medcalf wrote: > > On Thursday, 7 March, 2019 14:45, Eric Tsau as

Re: [sqlite] Import data into a temporary table

On Thursday, 7 March, 2019 14:45, Eric Tsau asked: >Is it possible to add the option of importing data into a temporary >table? >Currently you have to create a temporary table first before importing >to it, or having to drop the table afterwards. >.import dump.csv temp.table >or >.import dump.

Re: [sqlite] Import data into a temporary table

On 7 Mar 2019, at 9:45pm, Eric Tsau wrote: > Is it possible to add the option of importing data into a temporary table? Can you rephrase your request ? You can import data into a temporary table > .import dump.csv temp.table Does this command not work correctly ? Simon. _

[sqlite] Import data into a temporary table

Hi, Is it possible to add the option of importing data into a temporary table? Currently you have to create a temporary table first before importing to it, or having to drop the table afterwards. .import dump.csv temp.table or .import dump.csv attach.table Regards Eric

Re: [sqlite] Import zipped csv file

On 4/6/18, gwenn wrote: > Hello, > Is it possible to mix zipfile and csv extensions ? > sqlite> SELECT data FROM zipfile('csv_file.zip'); > works. > sqlite> .shell unzip csv_file.zip > sqlite> CREATE VIRTUAL TABLE test USING csv(filename='csv_file.csv'); > works. > How to pass the data extracted b

[sqlite] Import zipped csv file

Hello, Is it possible to mix zipfile and csv extensions ? sqlite> SELECT data FROM zipfile('csv_file.zip'); works. sqlite> .shell unzip csv_file.zip sqlite> CREATE VIRTUAL TABLE test USING csv(filename='csv_file.csv'); works. How to pass the data extracted by zipfile to csv extension ? Thanks.

[sqlite] .import and typeof

The .import command in sqlite3 chokes on CHECK constraints consisting of typeof. Is it intentional? $ sqlite3 -echo -header :memory: < demo/typeof.sql create table T (t int not null); insert into T values (1); .system echo 2 > T .system nl T 1 2 .import 'T' T select * from T; t 1 2 drop

Re: [sqlite] import-prefixed symbols in sqlite3.obj file

On Thu, Nov 10, 2016 at 1:51 PM, Dominique Devienne wrote: > On Thu, Nov 10, 2016 at 11:38 AM, Max Vlasov wrote: > >> __imp_EnterCriticalSection > > > Is this post [1] related to your issue? > Maybe you're not using the 64-bit SDK too? --DD thanks for mentioning. As this topic and many other dis

Re: [sqlite] import-prefixed symbols in sqlite3.obj file

On Thu, Nov 10, 2016 at 11:38 AM, Max Vlasov wrote: > __imp_EnterCriticalSection Is this post [1] related to your issue? Maybe you're not using the 64-bit SDK too? --DD [1] http://stackoverflow.com/questions/17840857/linker-errors-while-migrating-from-x32-to-x64 ___

[sqlite] import-prefixed symbols in sqlite3.obj file

Hi, Trying to link sqlite object file generated by visual c++ command-line compiler(cl.exe sqlite3.c /c) with the Delphi 64 bit executable, I encountered that some functions by windows api used by sqlite contains "___imp_" prefix (total: 7). This happened since recent delphi 64 bit compilers accep

Re: [sqlite] Import 5000 xml files in a sqlite database file

Ruby scripting language, using Nokogiri library to parse the html. On Sun, Oct 23, 2016 at 11:12 AM, Preguntón Cojonero Cabrón < preguntoncojon...@gmail.com> wrote: > Scripting powershell? C#? > > El 23/10/2016 18:04, "Sylvain Pointeau" > escribió: > > > hello, > > > > I am not sure if Oxygen or

Re: [sqlite] Import 5000 xml files in a sqlite database file

Scripting powershell? C#? El 23/10/2016 18:04, "Sylvain Pointeau" escribió: > hello, > > I am not sure if Oxygen or another XML specialized software could do it, > however it would be easy done using C or C++ or Java. Advantage is that it > is then easy to automatize in a batch mode. I can propo

Re: [sqlite] Import 5000 xml files in a sqlite database file

Bob, my name is Kevin Youren, and I did this task about 4 years ago in Python 3, by parsing XML files and creating CSV files. The CSV files were used to load Sqlite tables, MS Xcel spreadsheets and IBM mainframe DB2 tables. The XML data was mildly complex, large, and error prone. If you have a sa

Re: [sqlite] Import 5000 xml files in a sqlite database file

On Sat, 22 Oct 2016 19:26:42 +0200 bob_sql...@web.de wrote: > Hi, > > I have more than 5000 xml files. All files have the same > xml-structure. > > Each file has different values (timestamps, numbers and strings). I > would like to put all these values in a sqlite database tabke, all in > one t

Re: [sqlite] Import 5000 xml files in a sqlite database file

hello, I am not sure if Oxygen or another XML specialized software could do it, however it would be easy done using C or C++ or Java. Advantage is that it is then easy to automatize in a batch mode. I can propose you my services if you are interested. Best regards, Sylvain Le samedi 22 octobre 2

Re: [sqlite] Import 5000 xml files in a sqlite database file

There might be a GUI SQLite client app that can do this; I don’t know (the one I use, SQLiteManager, can only import SQL files.) But it’s really easy to do with a short program in a scripting language like Python/Ruby/PHP. I just searched for [sqlite import xml] and the top hit is the

[sqlite] Import 5000 xml files in a sqlite database file

Hi, I have more than 5000 xml files. All files have the same xml-structure. Each file has different values (timestamps, numbers and strings). I would like to put all these values in a sqlite database tabke, all in one table. => Import the data values into a sqlite database table. Can you plea

Re: [sqlite] trouble with sqlite .import

Hi Richard, Thanks for your answer. I had been looking at the csv exported data in Excel and it looked right. I opened the csv file in a plain text editor and it was as you said, the data was already in date format. Formatting the Column and re-exporting the csv file corrected my issue. M

Re: [sqlite] trouble with sqlite .import

On 2016/08/17 4:54 AM, Aaron Paul wrote: Greetings, I’m importing a csv file into a newly created table with .import. One of the columns contain a numerical tag which sometimes resembles a date (example 08-17). These are not dates, but the .import is converting 08-17 into 17-Aug The odd t

Re: [sqlite] trouble with sqlite .import

On 8/16/16, Aaron Paul wrote: > Greetings, > > I’m importing a csv file into a newly created table with .import. One of > the columns contain a numerical tag which sometimes resembles a date > (example 08-17). > > These are not dates, but the .import is converting 08-17 into 17-Aug No it's not.

[sqlite] trouble with sqlite .import

Greetings, I’m importing a csv file into a newly created table with .import. One of the columns contain a numerical tag which sometimes resembles a date (example 08-17). These are not dates, but the .import is converting 08-17 into 17-Aug The odd thing is it is doing this even when the date

[sqlite] .import command/NULL values

On 2016/03/16 11:53 AM, Bernard McNeill wrote: > Any particular reason why the shell '.import' command cannot interpret || > as a NULL for the field? The reason the import mechanism doesn't cope with NULLs is not because it can't, but because it shouldn't - until the TEXT standard changes in t

[sqlite] .import command/NULL values

Bernard McNeill wrote: > Any particular reason why the shell '.import' command cannot interpret || > as a NULL for the field? > Does || have another use/interpretation? Everything is a string. The data between the two separators is an empty string. Regards, Clemens

[sqlite] .import command/NULL values

Bernard McNeill wrote: > If it is required to import NULL values into an Sqlite table field, can > this be done No; the .import commands imports everything as text. (This can result in numbers in columns that have numeric affinity.) To get NULL values, UPDATE the table afterwards. Regards, Clem

[sqlite] .import command/NULL values

Any particular reason why the shell '.import' command cannot interpret || as a NULL for the field? Does || have another use/interpretation? Just seems a shame that the standard bulk input mechanism cannot cope directly with NULLs. Best regards On Wed, Mar 16, 2016 at 9:13 AM, Simon Slavin wrote

[sqlite] .import command/NULL values

On 16 Mar 2016, at 9:02am, Simon Slavin wrote: > No. Use one of these two methods. I'm sorry. I read your 'import' but I thought you meant using INSERT. If you are using the shell's ".import" command then Clemens is right. You are importing from a text file, and there is no way to put a N

[sqlite] .import command/NULL values

On 16 Mar 2016, at 8:43am, Bernard McNeill wrote: > If it is required to import NULL values into an Sqlite table field, can > this be done simply by arranging that the before and after delimiters for > that field are consecutive? No. Use one of these two methods. CREATE TABLE t(a,b,c) It is

[sqlite] .import command/NULL values

I couldn't find this issue documented anywhere. If it is required to import NULL values into an Sqlite table field, can this be done simply by arranging that the before and after delimiters for that field are consecutive? Example: For a three field table (all three fields text type), importing fr

[sqlite] .import command

Hi, I would like to continue a discussion started here: https://www.sqlite.org/src/tktview/c25aab7e7ea55c861313 First I assume the sqlite3 shell still has the related problem (I'm using Ubuntu, so not sure if the last version has fixed it). I would like to desagree of a comment posted on the ti

Re: [sqlite] .import error: cannot open large file

On 15 Nov 2013, at 9:23am, Luís Simão wrote: > Not sure if it helps, but you could try > >sqlite3 somedb '.import /dev/stdin hugetable' < huge.file > > making OS handle file reading instead of another process (cat). If the source really is a file then the right way to do it is sqlite3 so

Re: [sqlite] .import error: cannot open large file

Not sure if it helps, but you could try sqlite3 somedb '.import /dev/stdin hugetable' < huge.file making OS handle file reading instead of another process (cat). LS 2013/11/14 lpryszcz > Hi, Often I pipe tables (from .gz or multiple files). I found it also work > for large files that othe

Re: [sqlite] .import error: cannot open large file

Hi, Often I pipe tables (from .gz or multiple files). I found it also work for large files that otherwise fail with `Error: cannot open :huge.file"`: cat huge.file | sqlite3 somedb '.import /dev/stdin hugetable' But it could be slower that using `real` file import. Anyone have an idea? L. --

Re: [sqlite] import failing

On Tue, Oct 1, 2013 at 3:51 PM, Hoover, Jeffrey wrote: > I am trying to import a tab delimited file into a table containing 21 > columns. > > It fails on this record > Sapurv10037426m 176 RPS-BLAST(BLASTP) cdd > gnl|CDD|239293 72 159 1 89 33.756.5174

[sqlite] import failing

I am trying to import a tab delimited file into a table containing 21 columns. It fails on this record Sapurv10037426m 176 RPS-BLAST(BLASTP) cdd gnl|CDD|239293 72 159 1 89 33.756.5174 70.792 cd02995, PDI_a_PDI_a'_C, PDIa family, C-t

Re: [sqlite] sqlite .import bug

On 07-09-2013 07:16, dmitry babitsky wrote: *This works fine:* echo 1 | sqlite dbfile ".import '/dev/stdin' foo" But if you have any character (like a space, or newline), or sql statement in front of the '.import', sqlite gives: "Error near "." syntax error *Why it matters:* * * Because what I

[sqlite] sqlite .import bug

ct) This does work, if executed interactively: *$sqlite foo SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create temp table foo (c); sqlite> .import '/dev/stdin' temp.foo 1 sqlit

Re: [sqlite] Import skip first line

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/06/13 07:26, Bart Smissaert wrote: > Is there a way to use the other features, but not this one, say if the > table is already there? The autoimport code is one function so you get all of it, or none of it. The same is true of the import code wh

Re: [sqlite] Import skip first line

Thanks, will have a look at that as well. In my particular situation though I wouldn't want the auto data-type detection as that will slow things down and I will always know what the table fields affinities should be. Is there a way to use the other features, but not this one, say if the table is a

Re: [sqlite] Import skip first line

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 31/05/13 05:34, Bart Smissaert wrote: > Importing a. csv file via the. import command of sqlite3.exe. As the > first line holds the field names I want to skip that. There is no > problem achieving this in code, but could I do this purely via sqlite3

Re: [sqlite] Import skip first line

Bart Smissaert wrote: That modified version sounds useful. Could you mail it? The source as modified is at http://www.filedropper.com/sqlite3modifiedshell . This includes a readline library that works on Win32. The SQLite library source is omitted for space. The shell incorporates the cu

Re: [sqlite] Import skip first line

That modified version sounds useful. Could you mail it? RBS On May 31, 2013 6:14 PM, "Larry Brasfield" wrote: > Bart Smissaert wrote: > >> Importing a. csv file via the. import command of sqlite3.exe. >> As the first line holds the field names I want to skip that. >> There is no problem achievin

Re: [sqlite] Import skip first line

Bart Smissaert wrote: Importing a. csv file via the. import command of sqlite3.exe. As the first line holds the field names I want to skip that. There is no problem achieving this in code, but could I do this purely via sqlite3 commands? You could do it easily with a temporary table having an a

[sqlite] Import skip first line

Importing a. csv file via the. import command of sqlite3.exe. As the first line holds the field names I want to skip that. There is no problem achieving this in code, but could I do this purely via sqlite3 commands? RBS ___ sqlite-users mailing list sqli

Re: [sqlite] import with quotation mark

in it. http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles *From:* Spora *To:* sqlite-users@sqlite.org *Sent:* Wednesday, April 24, 2013 2:11 PM *Subject:* [sqlite] import with quotation mark Last Sqlite is not able to import line as

Re: [sqlite] import with quotation mark

24, 2013 2:11 PM Subject: [sqlite] import with quotation mark Last Sqlite is not able to import line as this: 01010    aa    16020    b'bb"cc, 33        e fff    31/03/13 Error: file.txt line 298: expected 9 columns of data but found 8 With previous version

[sqlite] import with quotation mark

Last Sqlite is not able to import line as this: 01010aa16020b'bb"cc, 33e fff31/03/13 Error: file.txt line 298: expected 9 columns of data but found 8 With previous version i not have this problem. Do it is possible to add a switch to ignore '"'

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

On Wed, Mar 20, 2013 at 8:37 AM, rol...@logikalsolutions.com < rol...@logikalsolutions.com> wrote: > In this case they should be exactly the same. Obviously it is a bug in > 3.7.9 > which got fixed in 3.7.15 > I cannot reproduce the problem in the official SQLite 3.7.9 either. Nor on 3.7.8 or 3

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

> On March 20, 2013 at 7:18 AM Simon Davies > wrote: > > > Makes absolutely no sense. INT and INTEGER should be exactly the same. For > > this limited set of data both should have worked. > > INT and INTEGER are not exactly the same: > http://www.sqlite.org/lang_createtable.html#rowid > > In ve

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

On 19 March 2013 15:08, rol...@logikalsolutions.com wrote: > version 3.7.9 2011-11-01 00:52:41 > KUbuntu 12.04 LTS 32-bit > > ex1.sql > > CREATE TABLE table_ex1( > Groupid INTEGER PRIMARY KEY, > GroupName TEXT > ); > .separator '|' > .import ex1.csv table_ex1 > .quit > > ex1.csv > 1|NONE > 2|TWO >

Re: [sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

rol...@logikalsolutions.com wrote: > sqlite> .read ex1.sql > Error: datatype mismatch Works for me with 3.7.15.2. > INT and INTEGER should be exactly the same. INTEGER PRIMARY KEY is a special case: http://www.sqlite.org/autoinc.html Regards, Clemens ___

[sqlite] IMPORT fails INTEGER column but lets same data sail through as INT

version 3.7.9 2011-11-01 00:52:41 KUbuntu 12.04 LTS 32-bit ex1.sql CREATE TABLE table_ex1( Groupid INTEGER PRIMARY KEY, GroupName TEXT ); .separator '|' .import ex1.csv table_ex1 .quit ex1.csv 1|NONE 2|TWO 3|THREE 4|FOUR 5|FIVE sqlite> .read ex1.sql Error: datatype mismatch sqlite> .quit ex2.s

Re: [sqlite] import TXT file

Thanks Kees. 2013/2/21 Kees Nuyt > On Thu, 21 Feb 2013 18:00:58 +0100, Gert Van Assche > wrote: > > >If I remove the double quotes, the problem is solved indeed. > >I'm working on windows. Do you know if there is an alternative to the SED > >command or tool? > > For just removing double quotes,

Re: [sqlite] import TXT file

On Thu, 21 Feb 2013 18:00:58 +0100, Gert Van Assche wrote: >If I remove the double quotes, the problem is solved indeed. >I'm working on windows. Do you know if there is an alternative to the SED >command or tool? For just removing double quotes, I would recommend the tr (translate) program. Som

Re: [sqlite] import TXT file

t; Any other suggestions? >> >> I think is the "double-quote character" break the line, >> I find in the table each row has mutilines all has a double-quote (") >> in the first line and another in last line , except the last row >> >> I think y

Re: [sqlite] import TXT file

; > I think you can prehandle the txt file change the " to another string > like: > > sed -i.bak 's/"/@@/g' test.txt > > and then in sqlite: > > .import test.txt Source > UPDATE Source SET Segments = replace(Segments, '@@'

Re: [sqlite] import TXT file

Mihael, I'll look for a developer who can compile the code for me. I'm not a developer. If I understand correctly, you wrote a tool to do a CSV import in an SQLite db in a much more controlled way, and if the import fails, we'll know at least why. With the code like it is now, can I use it on pla

Re: [sqlite] import TXT file

Thanks Igor. I will look for that. 2013/2/21 Igor Tandetnik > On 2/21/2013 5:34 AM, Gert Van Assche wrote: > >> I have to import text files into an SQLite table. >> The txt files are plain text in UTF-8. They don't contain tabs and the >> line >> ending is CRLF. >> >> One line in the TXT should

Re: [sqlite] import TXT file

I wrote an import util a while ago which might help you out as it's a bit more picky and gives more intelligent error messages than .import does. http://www.mail-archive.com/sqlite-users@sqlite.org/msg70182.html Show us some example lines and we can help a lot better.

Re: [sqlite] import TXT file

On 2/21/2013 5:34 AM, Gert Van Assche wrote: I have to import text files into an SQLite table. The txt files are plain text in UTF-8. They don't contain tabs and the line ending is CRLF. One line in the TXT should become one record in the table. CREATE TABLE Source (Segments); .import 'test.txt

Re: [sqlite] import TXT file

e the txt file change the " to another string like: sed -i.bak 's/"/@@/g' test.txt and then in sqlite: .import test.txt Source UPDATE Source SET Segments = replace(Segments, '@@', '"'); ___

Re: [sqlite] import TXT file

Hi, Gert, Regarding: - the import is not done line per line Could you elaborate on what you mean by this? FYI, the separator can be a string of characters, if you need something highly unlikely to occur in the data, something like !@# for example. Are the import errors repeatable? I.e. does t

Re: [sqlite] import TXT file

On 21 Feb 2013, at 12:15pm, Gert Van Assche wrote: > thanks for the advice, but I cannot work like this. I don't have the > possibility to rewrite the txt files I receive. > If I use the SQLite Expert, I get an error on "double-quote character" or a > "range error"... > > Any other suggestions?

Re: [sqlite] import TXT file

Roger, thanks for the advice, but I cannot work like this. I don't have the possibility to rewrite the txt files I receive. If I use the SQLite Expert, I get an error on "double-quote character" or a "range error"... Any other suggestions? thanks gert 2013/2/21 Roger Binns > -BEGIN PGP

Re: [sqlite] import TXT file

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 21/02/13 02:34, Gert Van Assche wrote: > Does anyone see something obvious I'm doing wrong? There is no need to bash the SQLite shell into what you are trying to do. You can write your own code to read your file and insert each row into the databa

[sqlite] import TXT file

All, I have to import text files into an SQLite table. The txt files are plain text in UTF-8. They don't contain tabs and the line ending is CRLF. One line in the TXT should become one record in the table. CREATE TABLE Source (Segments); .import 'test.txt ' Sour

Re: [sqlite] Import at first unhappy - then happy after all

for instructions > > Enter SQL statements terminated with a ";" > > sqlite> pragma page_size=4096; > > sqlite> pragma journal_mode=off; > > off > > sqlite> pragma temp_store=memory; > > sqlite> .timer on > > sqlite> .separator ',

  1   2   3   4   >