Thread: 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 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 > > 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...
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
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
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