Обсуждение: Importing 120 csv files bulk multiple
Hi,
Andy
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
Вложения
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
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
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 >