shell csv import

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

shell csv import

D Burgess
I have a script that loads csv into an existing table.

I get this message on stderr for each row imported:

"... expected 7 columns but found 6 - filling the rest with NULL"


We have the means to send stdout to /dev/null using the .once or .output

Is there a way to send suppress stderr messages for a dot command?

If not, can we have one?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] shell csv import

Hick Gunter
When running a script from the shell, you can redirect stderr tot he null device using 2>/dev/null or to the same destination as stdout using 2>&1. The latter is also very useful in crontab entries, as neglecting to handle stderr will result in an email tot he user that contains anything written there

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von D Burgess
Gesendet: Dienstag, 18. September 2018 08:15
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] shell csv import

I have a script that loads csv into an existing table.

I get this message on stderr for each row imported:

"... expected 7 columns but found 6 - filling the rest with NULL"


We have the means to send stdout to /dev/null using the .once or .output

Is there a way to send suppress stderr messages for a dot command?

If not, can we have one?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] shell csv import

D Burgess
Thanks hick. But that's not the problem.
The import will always write to stderr, it's not an error
Lots of other stuff in the script and I want to be able to catch any errors
in the other parts of the script.

On Tue, Sep 18, 2018 at 4:20 PM, Hick Gunter <[hidden email]> wrote:

> When running a script from the shell, you can redirect stderr tot he null
> device using 2>/dev/null or to the same destination as stdout using 2>&1.
> The latter is also very useful in crontab entries, as neglecting to handle
> stderr will result in an email tot he user that contains anything written
> there
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von D Burgess
> Gesendet: Dienstag, 18. September 2018 08:15
> An: [hidden email]
> Betreff: [EXTERNAL] [sqlite] shell csv import
>
> I have a script that loads csv into an existing table.
>
> I get this message on stderr for each row imported:
>
> "... expected 7 columns but found 6 - filling the rest with NULL"
>
>
> We have the means to send stdout to /dev/null using the .once or .output
>
> Is there a way to send suppress stderr messages for a dot command?
>
> If not, can we have one?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] shell csv import

Rowan Worth-2
You can also filter out specific messages at the shell level:

sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the
rest with NULL' >&2)

But note that the >() syntax is not a POSIX sh feature, and will not work
in a script using a shebang of #!/bin/sh. You need to change it to
#!/bin/bash or whatever shell you have on hand. For more info see the
"Process Substition" section of the bash man page.

If you have the ability to modify the generated SQL, presumably you could
avoid the error by generating a NULL yourself for the missing column?
-Rowan

On 18 September 2018 at 14:28, D Burgess <[hidden email]> wrote:

> Thanks hick. But that's not the problem.
> The import will always write to stderr, it's not an error
> Lots of other stuff in the script and I want to be able to catch any errors
> in the other parts of the script.
>
> On Tue, Sep 18, 2018 at 4:20 PM, Hick Gunter <[hidden email]> wrote:
>
> > When running a script from the shell, you can redirect stderr tot he null
> > device using 2>/dev/null or to the same destination as stdout using 2>&1.
> > The latter is also very useful in crontab entries, as neglecting to
> handle
> > stderr will result in an email tot he user that contains anything written
> > there
> >
> > -----Ursprüngliche Nachricht-----
> > Von: sqlite-users [mailto:[hidden email]]
> > Im Auftrag von D Burgess
> > Gesendet: Dienstag, 18. September 2018 08:15
> > An: [hidden email]
> > Betreff: [EXTERNAL] [sqlite] shell csv import
> >
> > I have a script that loads csv into an existing table.
> >
> > I get this message on stderr for each row imported:
> >
> > "... expected 7 columns but found 6 - filling the rest with NULL"
> >
> >
> > We have the means to send stdout to /dev/null using the .once or .output
> >
> > Is there a way to send suppress stderr messages for a dot command?
> >
> > If not, can we have one?
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___________________________________________
> >  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 |
> (O)
> > +43 1 80100 - 0
> >
> > May be privileged. May be confidential. Please delete if not the
> addressee.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] shell csv import

E.Pasma
In reply to this post by D Burgess
> I have a script that loads csv into an existing table.
>
> I get this message on stderr for each row imported:
>
> "... expected 7 columns but found 6 - filling the rest with NULL"
>
>
> We have the means to send stdout to /dev/null using the .once or .output
>
> Is there a way to send suppress stderr messages for a dot command?

The example below works without warnings (though this diverts from exact question).

create table t (a,b,c,d,e,f)
;
/* Procedure to insert a line of input */
create view procinput(a,b,c) as values(null,null,null)
;
create trigger ins_procinput instead of insert on procinput
begin
  insert into t(a,b,c) values(new.a,new.b,new.c);
end
;

.import input.dat procinput



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] shell csv import

E.Pasma
In reply to this post by Rowan Worth-2

> Rowan Worth wrote:
>
> You can also filter out specific messages at the shell level:
>
> sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the
> rest with NULL' >&2)
>
> But note that the >() syntax is not a POSIX sh feature, and will not work
> in a script using a shebang of #!/bin/sh. You need to change it to
> #!/bin/bash or whatever shell you have on hand. For more info see the
> "Process Substition" section of the bash man page.
>
> If you have the ability to modify the generated SQL, presumably you could
> avoid the error by generating a NULL yourself for the missing column?
> -Rowan
I replied just before reading this. This solution may be preferred.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] shell csv import

D Burgess
Thank you E.Pasma, most elegant. Solves my problem.


Thank you Rowan, I was trying to achieve it with /bin/sh (dash)

On Tue, Sep 18, 2018 at 7:12 PM, E.Pasma <[hidden email]> wrote:

>
> > Rowan Worth wrote:
> >
> > You can also filter out specific messages at the shell level:
> >
> > sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the
> > rest with NULL' >&2)
> >
> > But note that the >() syntax is not a POSIX sh feature, and will not work
> > in a script using a shebang of #!/bin/sh. You need to change it to
> > #!/bin/bash or whatever shell you have on hand. For more info see the
> > "Process Substition" section of the bash man page.
> >
> > If you have the ability to modify the generated SQL, presumably you could
> > avoid the error by generating a NULL yourself for the missing column?
> > -Rowan
> I replied just before reading this. This solution may be preferred.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users