Re: [sqlite] How do you load a ".csv" and skip the first line?

[John Gillespie]

I use "SQLite Manager " extension for Firefox.
Don't know if it is available for IE,  didn't find it for Safari.

John G


On 4 September 2012 21:23, Peter Haworth  wrote:

> Take a look at my SQLiteAdmin tool.  It will import csv files with or
> without headers, export them that way too, plus many features to help
> create and maintain your schema and browse/edit your data.  Avaialble at
> www.lcsql.com.
>
> Pete
> lcSQL Software 
>
>
>
> On Sun, Sep 2, 2012 at 9:00 AM,  wrote:
>
> > Message: 3
> > Date: Sat, 01 Sep 2012 12:19:09 -0700
> > From: "joe.fis...@tanguaylab.com" 
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] How do you load a ".csv" and skip the first line?
> > Message-ID: <50425fad.4020...@tanguaylab.com>
> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> >
> > Is there some way to load a ".csv" file into a SQLite database table
> > when the first row (record) of the file contains headers?
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Peter Haworth]

Take a look at my SQLiteAdmin tool.  It will import csv files with or
without headers, export them that way too, plus many features to help
create and maintain your schema and browse/edit your data.  Avaialble at
www.lcsql.com.

Pete
lcSQL Software 



On Sun, Sep 2, 2012 at 9:00 AM,  wrote:

> Message: 3
> Date: Sat, 01 Sep 2012 12:19:09 -0700
> From: "joe.fis...@tanguaylab.com" 
> To: sqlite-users@sqlite.org
> Subject: [sqlite] How do you load a ".csv" and skip the first line?
> Message-ID: <50425fad.4020...@tanguaylab.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Is there some way to load a ".csv" file into a SQLite database table
> when the first row (record) of the file contains headers?
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do you load a ".csv" and skip the first line?

[danap]

> Is there some way to load a ".csv" file into a SQLite database table
> when the first row (record) of the file contains headers?
>
> This does not work?
> --
> .separator ,
> .import test.csv ld_assay
>
> Here's my database / table:
> CREATE TABLE [ld_assay] (
>[id] INTEGER PRIMARY KEY,
>[barcode_id] NCHAR(9),
>[light] BOOLEAN,
>[bpm] REAL);
>
> Here's my "test.csv":
> id,barcode_id,light,bpm
> 1,02455,1,180.2
> 2,02455,1,168.3
> 3,02455,1,189.4

I'm pretty sure MyJSQLView will do this for you. Been a while
since I tested it it, but it should use the first line to
compose a SQL insert statement that only adds the columns
specified. Left out columns should take default values.

Since MyJSQLView is a Java based GUI the Zentus's SQLite
JDBC must be used as the commnuncation interface to the
SQLite database file. Add it to your java/lib/ext directory
to have it loaded automatically.

danap.

MyJSQLView
http://myjsqlview.org
SQLite JDBC
http://www.xerial.org/trac/Xerial
/wiki/SQLiteJDBC#WhatisdifferentfromZentussSQLiteJDBC

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

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Larry Brasfield]

joe.fis...@tanguaylab.com wrote:


Is there some way to load a ".csv" file into a SQLite database table
when the first row (record) of the file contains headers?


Awhile ago, I published a modification of the SQLite shell which I
wrote specifically to solve this problem.  When headers are turned
on, (only for output in the original version), the behavior of an
.import command is changed so that the first input line names the
columns, which must exist.

If you send email to l *dot* brasfield *at* computer *dot* org , I
can send the modified source for SQLite version 3.7.13 shell, or
a Windows executable, or both.  It has a few other changes that do
not interfere with conventional use of the shell which show up in
the .help response.

Cheers,
--
Larry Brasfield

(Examples of problem cut.)


This does not work?
--
.separator ,
.import test.csv ld_assay

Here's my database / table:
CREATE TABLE [ld_assay] (
   [id] INTEGER PRIMARY KEY,
   [barcode_id] NCHAR(9),
   [light] BOOLEAN,
   [bpm] REAL);

Here's my "test.csv":
id,barcode_id,light,bpm
1,02455,1,180.2
2,02455,1,168.3
3,02455,1,189.4
--

This works (example from different program):
--
When R loads a ".csv" file it uses the [header = TRUE] flag to skip the
first row of the file:
data.frame_ld_assay <- read.table("test.csv", header = TRUE, sep = ",",
colClasses = c("integer", "character", "integer", "numeric"))

Most likely utilizing the "skip, integer: the number of lines of the
data file to skip before beginning to read data." option.
--




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

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Dominique Pellé]

joe.fis...@tanguaylab.com  wrote:

> Is there some way to load a ".csv" file into a SQLite database table when
> the first row (record) of the file contains headers?
>
> This does not work?
> --
> .separator ,
> .import test.csv ld_assay
>
> Here's my database / table:
> CREATE TABLE [ld_assay] (
>   [id] INTEGER PRIMARY KEY,
>   [barcode_id] NCHAR(9),
>   [light] BOOLEAN,
>   [bpm] REAL);
>
> Here's my "test.csv":
> id,barcode_id,light,bpm
> 1,02455,1,180.2
> 2,02455,1,168.3
> 3,02455,1,189.4
> --
>
> This works (example from different program):
> --
> When R loads a ".csv" file it uses the [header = TRUE] flag to skip the
> first row of the file:
> data.frame_ld_assay <- read.table("test.csv", header = TRUE, sep = ",",
> colClasses = c("integer", "character", "integer", "numeric"))
>
> Most likely utilizing the "skip, integer: the number of lines of the data
> file to skip before beginning to read data." option.
> --


Spatialite extension of SQLite has VirtualText() which
creates a virtual table from a CSV files.  It uses the
first lines to name the columns in the virtual table.

Example:

$ cat test.csv
id,barcode_id,light,bpm
1,02455,1,180.2
2,02455,1,168.3
3,02455,1,189.4


spatialite> CREATE VIRTUAL TABLE virt_test
   ...> USING VirtualText(test.csv, UTF8, 1, COMMA, DOUBLEQUOTE, ',');

spatialite> SELECT * FROM virt_test;
0|1|2455|1|180.2
1|2|2455|1|168.3
2|3|2455|1|189.4

spatialite> CREATE TABLE test AS SELECT * FROM virt_test;

spatialite> SELECT * FROM test;
0|1|2455|1|180.2
1|2|2455|1|168.3
2|3|2455|1|189.4

spatialite> .schema test
CREATE TABLE TEST(
  ROWNO INT,
  id INT,
  barcode_id INT,
  light INT,
  bpm TEXT
);

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

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Ted Rolle, Jr.]

Text editor to remove the offending line(s)?
This way you can see what you've removed.
The other solutions (tail, less, more) would work better for LARGE files.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Udi Karni]

If all else fails - after the import issue  DELETE FROM XYZ WHERE ID = 'id'
;

But - yes - adding the "skip=y" and  "load=x" parameters  (skip the first y
rows in the .csv file, and stop after loading x rows) - to the .import
utility would be most desirable.

On Sat, Sep 1, 2012 at 12:31 PM, Petite Abeille wrote:

>
> On Sep 1, 2012, at 9:19 PM, joe.fis...@tanguaylab.com <
> joe.fis...@tanguaylab.com> wrote:
>
> > Is there some way to load a ".csv" file into a SQLite database table
> when the first row (record) of the file contains headers?
>
> On *nix, any of tail +2, more +2, etc would do.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/09/12 12:19, joe.fis...@tanguaylab.com wrote:
> Is there some way to load a ".csv" file into a SQLite database table
> when the first row (record) of the file contains headers?

You may want to consider using the APSW shell.  It has a .autoimport
command that automatically figures out separators, what fields are
text/date/numeric etc.  You can then use the resulting database with
whatever you use for SQLite.

   http://apidoc.apsw.googlecode.com/hg/shell.html

Example usage (a few .dump lines trimmed) against your test csv:

  $ python -c "import apsw;apsw.main()" test.db
  SQLite version 3.7.13 (APSW 3.7.13-r1)
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> .autoimport test.csv
  Detected Format excel  Columns 4  Rows 4
  Auto-import into table "test" complete
  sqlite> .dump test
  CREATE TABLE test(id, barcode_id, light, bpm);
  INSERT INTO test VALUES(1,'02455',1,180.2);
  INSERT INTO test VALUES(2,'02455',1,168.3);
  INSERT INTO test VALUES(3,'02455',1,189.4);

You'll note that it figured out barcode field was a string despite
consisting of only digits.  This is the .help for autoimport:

.autoimport FILENAME ?TABLE?  Imports filename creating a table and
  automatically working out separators and data
  types (alternative to .import command)

The import command requires that you precisely pre-setup the table and
schema, and set the data separators (eg commas or tabs).  In many cases
this information can be automatically deduced from the file contents which
is what this command does.  There must be at least two columns and two rows.

If the table is not specified then the basename of the file will be used.

Additionally the type of the contents of each column is also deduced - for
example if it is a number or date.  Empty values are turned into nulls.
Dates are normalized into -MM-DD format and DateTime are normalized
into ISO8601 format to allow easy sorting and searching.  4 digit years
must be used to detect dates.  US (swapped day and month) versus rest of
the world is also detected providing there is at least one value that
resolves the ambiguity.

Care is taken to ensure that columns looking like numbers are only treated
as numbers if they do not have unnecessary leading zeroes or plus signs.
This is to avoid treating phone numbers and similar number like strings as
integers.

This command can take quite some time on large files as they are effectively
imported twice.  The first time is to determine the format and the types for
each column while the second pass actually imports the data.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlBCclIACgkQmOOfHg372QQV2QCg3s6VpgCbvGG9xQqvQ1fSvBpj
OjkAn1afh25GTDpjPUYuHoJASmHdQ/3Z
=2S51
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Petite Abeille]

On Sep 1, 2012, at 9:19 PM, joe.fis...@tanguaylab.com 
 wrote:

> Is there some way to load a ".csv" file into a SQLite database table when the 
> first row (record) of the file contains headers?

On *nix, any of tail +2, more +2, etc would do.

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