Importing from CSV, auto creating table?
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
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.
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.
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.
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.
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
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 using with the -i flag:
...snip...
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 Franklincsvsql 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 using with the -i flag:
...snip...
Example
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 Franklincsvsql 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 using with the -i flag:
...snip...Example
a more detailed example
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