Обсуждение: Importing 120 csv files bulk multiple

Поиск
Список
Период
Сортировка

Importing 120 csv files bulk multiple

От
Andrew Taylor
Дата:
Hi,

As per title I need to import a load of csv files. So I wrote a bash script to generate the statements I needed (attached). However, this is failing on my ubuntu laptop - it seems to occasionally miss  the semicolon to execute. What I did was copy the text to clipboard and paste it in to bash. Is there maybe a way to open this file and evaluate it within psql? Output shown below:

COPY 11340
postgres=# copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ub.csv' using delimiters ','
postgres-# copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wa.csv' using delimiters ',';
ERROR:  syntax error at or near "copy"
LINE 2: copy postcode_input from '/home/andyt/projects/django-string...
        ^
postgres=# 
postgres=# copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/w.csv' using delimiters ',';
COPY 19787
postgres=# 
postgres=# copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wf.csv' using delimiters ','
postgres-# copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wn.csv' using delimiters ',';
ERROR:  syntax error at or near "copy"
LINE 2: copy postcode_input from '/home/andyt/projects/django-string...
        ^
postgres=# copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wr.csv' using delimiters ',';
COPY 8381
postgres=# copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ws.csv' using delimiters ',';
COPY 9884
postgres=# copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wv.csv' using delimiters ',';
COPY 9440


Thanks,


Andy

Вложения

Re: Importing 120 csv files bulk multiple

От
Ryan Kelly
Дата:
You can use \i /path/to/file.txt

-Ryan

On Tue, Feb 02/12/13, 2013 at 03:14:03PM +0000, Andrew Taylor wrote:
> Hi,
>
> As per title I need to import a load of csv files. So I wrote a bash script
> to generate the statements I needed (attached). However, this is failing on
> my ubuntu laptop - it seems to occasionally miss  the semicolon to execute.
> What I did was copy the text to clipboard and paste it in to bash. Is there
> maybe a way to open this file and evaluate it within psql? Output shown
> below:
>
> COPY 11340
> postgres=# copy postcode_input from
> '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ub.csv' using
> delimiters ','
> postgres-# copy postcode_input from
> '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wa.csv' using
> delimiters ',';
> ERROR:  syntax error at or near "copy"
> LINE 2: copy postcode_input from '/home/andyt/projects/django-string...
>         ^
> postgres=#
> postgres=# copy postcode_input from
> '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/w.csv' using
> delimiters ',';
> COPY 19787
> postgres=#
> postgres=# copy postcode_input from
> '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wf.csv' using
> delimiters ','
> postgres-# copy postcode_input from
> '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wn.csv' using
> delimiters ',';
> ERROR:  syntax error at or near "copy"
> LINE 2: copy postcode_input from '/home/andyt/projects/django-string...
>         ^
> postgres=# copy postcode_input from
> '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wr.csv' using
> delimiters ',';
> COPY 8381
> postgres=# copy postcode_input from
> '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ws.csv' using
> delimiters ',';
> COPY 9884
> postgres=# copy postcode_input from
> '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wv.csv' using
> delimiters ',';
> COPY 9440
>
>
> Thanks,
>
>
> Andy

> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ab.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/al.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ba.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/bb.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/b.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/bd.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/bh.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/bl.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/bn.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/br.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/bs.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ca.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/cb.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/cf.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ch.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/cm.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/co.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/cr.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ct.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/cv.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/cw.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/da.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/dd.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/de.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/dg.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/dh.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/dl.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/dn.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/dt.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/dy.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ec.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/e.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/eh.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/en.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ex.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/fk.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/fy.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/g.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/gl.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/gu.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ha.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/hd.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/hg.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/hp.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/hr.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/hs.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/hu.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/hx.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ig.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ip.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/iv.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ka.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/kt.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/kw.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ky.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/la.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/l.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ld.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/le.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ll.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ln.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ls.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/lu.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/m.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/me.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/mk.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ml.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/n.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ne.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ng.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/nn.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/np.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/nr.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/nw.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ol.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ox.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/pa.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/pe.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ph.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/pl.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/po.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/pr.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/rg.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/rh.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/rm.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sa.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/s.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/se.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sg.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sk.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sl.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sm.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sn.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/so.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sp.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sr.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ss.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/st.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sw.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/sy.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ta.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/td.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/tf.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/tn.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/tq.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/tr.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ts.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/tw.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ub.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wa.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wc.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/w.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wd.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wf.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wn.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wr.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ws.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/wv.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/yo.csv' using delimiters ',';
> copy postcode_input from '/home/andyt/projects/django-stringer/Other/codepo_gb/Data/ze.csv' using delimiters ',';

>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Importing 120 csv files bulk multiple

От
Adrian Klaver
Дата:
On 02/12/2013 07:14 AM, Andrew Taylor wrote:
> Hi,
>
> As per title I need to import a load of csv files. So I wrote a bash
> script to generate the statements I needed (attached). However, this is
> failing on my ubuntu laptop - it seems to occasionally miss  the
> semicolon to execute. What I did was copy the text to clipboard and
> paste it in to bash. Is there maybe a way to open this file and evaluate
> it within psql? Output shown below:
>
>

If you want to look at by opening an editor:

test=> \e poscodeInput.txt

If you want to run it:

test=> \i poscodeInput.txt

Substitute path as needed.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Importing 120 csv files bulk multiple

От
Andrew Taylor
Дата:
Awesome, thanks!

On Tue, Feb 12, 2013 at 3:35 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

> On 02/12/2013 07:14 AM, Andrew Taylor wrote:
>
>> Hi,
>>
>> As per title I need to import a load of csv files. So I wrote a bash
>> script to generate the statements I needed (attached). However, this is
>> failing on my ubuntu laptop - it seems to occasionally miss  the
>> semicolon to execute. What I did was copy the text to clipboard and
>> paste it in to bash. Is there maybe a way to open this file and evaluate
>> it within psql? Output shown below:
>>
>>
>>
> If you want to look at by opening an editor:
>
> test=> \e poscodeInput.txt
>
> If you want to run it:
>
> test=> \i poscodeInput.txt
>
> Substitute path as needed.
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>