Thread: Importing from CSV, auto creating table?

Importing from CSV, auto creating table?

From
stan
Date:
I have a situation where we need to import data, as an interim measure,
from spreadsheets. 

I have read up on \copy and COPY, but I do not see that either of these can
use the header from a CSV file to define a new table. Am I missing
something?

Also downloaded something called pgfutter, which I thought would do this,
but have not had any success with this. After I (thought) I had figured out
the arguments, it just seams to hag forever.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin



Re: Importing from CSV, auto creating table?

From
"David G. Johnston"
Date:
On Wed, Aug 21, 2019 at 2:15 PM stan <stanb@panix.com> wrote:
I have a situation where we need to import data, as an interim measure,
from spreadsheets.

I have read up on \copy and COPY, but I do not see that either of these can
use the header from a CSV file to define a new table. Am I missing
something?


Nope.  You need to create the table separately - or find a tool that will do that creation for you.

David J.

Re: Importing from CSV, auto creating table?

From
DiasCosta
Date:
Hi Stan,

I uploaded to the database (PostgreSQL 9.6), monthly and for several years, over 50 000 000 csv records
using a version of pgtfutter that I compiled (with some changes if I remember correctly) and the tables
were created in
loading process from the column titles.

Dias Costa




On 21-08-2019 22:15, stan wrote:
I have a situation where we need to import data, as an interim measure,
from spreadsheets. 

I have read up on \copy and COPY, but I do not see that either of these can
use the header from a CSV file to define a new table. Am I missing
something?

Also downloaded something called pgfutter, which I thought would do this,
but have not had any success with this. After I (thought) I had figured out
the arguments, it just seams to hag forever.




-- 
J. M. Dias Costa
Telef. 214026948    

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o 
malfadado acordo ortográfico.

Re: Importing from CSV, auto creating table?

From
Ron
Date:
On 8/21/19 4:15 PM, stan wrote:
> I have a situation where we need to import data, as an interim measure,
> from spreadsheets.
>
> I have read up on \copy and COPY, but I do not see that either of these can
> use the header from a CSV file to define a new table. Am I missing
> something?

Data types.  Sure, you've got column names from the header line, but what 
types do you assign them?

>
> Also downloaded something called pgfutter, which I thought would do this,
> but have not had any success with this. After I (thought) I had figured out
> the arguments, it just seams to hag forever.
>
>

-- 
Angular momentum makes the world go 'round.



Re: Importing from CSV, auto creating table?

From
"David G. Johnston"
Date:
On Wednesday, August 21, 2019, Ron <ronljohnsonjr@gmail.com> wrote:
On 8/21/19 4:15 PM, stan wrote:
I have a situation where we need to import data, as an interim measure,
from spreadsheets.

I have read up on \copy and COPY, but I do not see that either of these can
use the header from a CSV file to define a new table. Am I missing
something?

Data types.  Sure, you've got column names from the header line, but what types do you assign them?

Text.  I’m gonna post-process anyway, casting to better types isn’t a problem.

David J. 

Re: Importing from CSV, auto creating table?

From
Reid Thompson
Date:
On Wed, 2019-08-21 at 17:15 -0400, stan wrote:
> [EXTERNAL SOURCE]
> 
> 
> 
> I have a situation where we need to import data, as an interim measure,
> from spreadsheets.
> 
> I have read up on \copy and COPY, but I do not see that either of these can
> use the header from a CSV file to define a new table. Am I missing
> something?
> 
> Also downloaded something called pgfutter, which I thought would do this,
> but have not had any success with this. After I (thought) I had figured out
> the arguments, it just seams to hag forever.
> 
> 
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>                                                 -- Benjamin Franklin
> 
> 


https://csvkit.readthedocs.io/en/latest/tutorial/3_power_tools.html#csvsql-and-sql2csv-ultimate-power

csvsql and sql2csv: ultimate power
...snip...
By default, csvsql will generate a create table statement for your data. You can specify what sort of database you are
usingwith the -i flag:
 
...snip...


Re: Importing from CSV, auto creating table?

From
Reid Thompson
Date:
On Thu, 2019-08-22 at 08:25 -0400, Reid Thompson wrote:
> On Wed, 2019-08-21 at 17:15 -0400, stan wrote:
> > [EXTERNAL SOURCE]
> > 
> > 
> > 
> > I have a situation where we need to import data, as an interim measure,
> > from spreadsheets.
> > 
> > I have read up on \copy and COPY, but I do not see that either of these can
> > use the header from a CSV file to define a new table. Am I missing
> > something?
> > 
> > Also downloaded something called pgfutter, which I thought would do this,
> > but have not had any success with this. After I (thought) I had figured out
> > the arguments, it just seams to hag forever.
> > 
> > 
> > --
> > "They that would give up essential liberty for temporary safety deserve
> > neither liberty nor safety."
> >                                                 -- Benjamin Franklin
> > 
> > 
> 
> https://csvkit.readthedocs.io/en/latest/tutorial/3_power_tools.html#csvsql-and-sql2csv-ultimate-power
> 
> csvsql and sql2csv: ultimate power
> ...snip...
> By default, csvsql will generate a create table statement for your data. You can specify what sort of database you
areusing with the -i flag:
 
> ...snip...
> 

Example

https://stackoverflow.com/questions/35243432/how-to-generate-a-schema-from-a-csv-for-a-postgresql-copy


Re: Importing from CSV, auto creating table?

From
Reid Thompson
Date:
On Thu, 2019-08-22 at 09:01 -0400, Reid Thompson wrote:
> On Thu, 2019-08-22 at 08:25 -0400, Reid Thompson wrote:
> > On Wed, 2019-08-21 at 17:15 -0400, stan wrote:
> > > [EXTERNAL SOURCE]
> > > 
> > > 
> > > 
> > > I have a situation where we need to import data, as an interim measure,
> > > from spreadsheets.
> > > 
> > > I have read up on \copy and COPY, but I do not see that either of these can
> > > use the header from a CSV file to define a new table. Am I missing
> > > something?
> > > 
> > > Also downloaded something called pgfutter, which I thought would do this,
> > > but have not had any success with this. After I (thought) I had figured out
> > > the arguments, it just seams to hag forever.
> > > 
> > > 
> > > --
> > > "They that would give up essential liberty for temporary safety deserve
> > > neither liberty nor safety."
> > >                                                 -- Benjamin Franklin
> > > 
> > > 
> > 
> > https://csvkit.readthedocs.io/en/latest/tutorial/3_power_tools.html#csvsql-and-sql2csv-ultimate-power
> > 
> > csvsql and sql2csv: ultimate power
> > ...snip...
> > By default, csvsql will generate a create table statement for your data. You can specify what sort of database you
areusing with the -i flag:
 
> > ...snip...
> > 
> 
> Example
> 
> https://stackoverflow.com/questions/35243432/how-to-generate-a-schema-from-a-csv-for-a-postgresql-copy
> 

a more detailed example

https://archive.is/pFpJi


Re: Importing from CSV, auto creating table?

From
Steve Atkins
Date:
On 21/08/2019 22:15, stan wrote:
> I have a situation where we need to import data, as an interim measure,
> from spreadsheets.
>
> I have read up on \copy and COPY, but I do not see that either of these can
> use the header from a CSV file to define a new table. Am I missing
> something?
>
> Also downloaded something called pgfutter, which I thought would do this,
> but have not had any success with this. After I (thought) I had figured out
> the arguments, it just seams to hag forever.
>
You might find https://github.com/wttw/csvpg useful. It creates tables 
with column names based on the CSV header, and data types intuited from 
the data.

(The only pre-built binary there is for Mac right now; I should at least 
add one for Windows).

Cheers,
   Steve