importing a CSV file into PostgreSQL table (with CREATE TABLE)
lazarus / 16-Databases

----------------------------------------------------------------------
tatamata · 2016-01-16 11:06

Hello,

I need to perform direct importing of any csv file into PostgreSQL
(with both schema and data), remotely.
The code should both create table and import data at once.
Is such code already available?

Alternatively, is there any data access component that can copy
everything from one dataset to another (both schema and data) and
commit it to underlying database (PostgreSQL, Mysql, Firebird etc.)?

Thanks

----------------------------------------------------------------------
goodname · 2016-01-16 14:08

This looks like your asking for backup and restore functions. CSV
files are usually data only so not sure how the schema would work.
http://www.postgresql.org/docs/9.4/static/backup.html

Alternatively PostgreSQL has foreign data wrappers which can read
other database engines directly.
https://wiki.postgresql.org/wiki/Foreign_data_wrappers

----------------------------------------------------------------------
balazsszekely · 2016-01-16 15:34

Quote@tatamata
I need to perform direct importing of any csv file into PostgreSQL
(with both schema and data), remotely.
The code should both create table and import data at once.
Is such code already available?I agree with @goodname, I can't imagine
how you want to store schema in CSV file. Anyway, this is how you can
import both schema and data:
1. Drop a TSQLScript to your form
2. Set Database(TIBConeection) and Transaction(TSQLTransaction)
properties.
3. Set the script property(this is for firebird, but should fully
conforming with SQL standard). For this demo I created a simple table
with two records:
   Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TEST_TABLE (    ID     INTEGER NOT NULL,    FNAME  VARCHAR(25), 
  LNAME  CHAR(25),    AGE    INTEGER);COMMIT WORK; ALTER TABLE
TEST_TABLEADD CONSTRAINT PK_TEST_TABLE PRIMARY KEY (ID);COMMIT
WORK; INSERT INTO TEST_TABLE (ID, FNAME, LNAME, AGE)VALUES (1, 'John',
'Smith                    ', 23);INSERT INTO TEST_TABLE (ID, FNAME,
LNAME, AGE)VALUES (2, 'aaaaa', 'bbbbb                    ', 32);COMMIT
WORK;  4. Set the terminator if necessary(usually ";")

From code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLScript1.DataBase := IBConnection1;  SQLScript1.Transaction :=
SQLTransaction1;  SQLScript1.Script := 'CREATE TABLE
TEST_TABLE(...)see above';  SQLScript1.ExecuteScript;  
Quote@tatamata
Alternatively, is there any data access component that can copy
everything from one dataset to another (both schema and data) and
commit it to underlying database (PostgreSQL, Mysql, Firebird
etc.)?No, not that I'm aware off, but you can query anything from the
system tables. Example for firebird:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select
f.rdb$relation_name, f.rdb$field_namefrom rdb$relation_fields fjoin
rdb$relations r on f.rdb$relation_name = r.rdb$relation_nameand
r.rdb$view_blr is nulland (r.rdb$system_flag is null or
r.rdb$system_flag = 0) I'm 100% sure that something similar exists for
postgre