Import csv to temp table

On Tue, Jan 2, 2024 at 7:02 AM arun chirappurath <arunsnmimt@gmail.com>
wrote:

Dear All,

Do we have any scripts that create a temp table with column names from the
first row of csv files?

How would you determine the data type?

If you assume TEXT for all of them, then it's relatively simple to write
bash which does it.

arun chirappurath wrote:

Do we have any scripts that create a temp table with column names
from the first row of csv files?

csvkit [1]https://csvkit.readthedocs.io/en/latest/ does that.

[1]: https://csvkit.readthedocs.io/en/latest/

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

On 1/2/24 06:51, Daniel Verite wrote:

arun chirappurath wrote:

Do we have any scripts that create a temp table with column names
from the first row of csv files?

csvkit [1] does that.

[1] https://csvkit.readthedocs.io/en/latest/

In addition to the above which I have used you might want to take a look at:

Polars
https://pola.rs/

and

Duckdb
https://duckdb.org/

They both allow you to query CSV(and other format) files directly. I
have found them very useful for looking at CSV files and you can
transfer data elsewhere later.

Then there is:

Pandas
https://pandas.pydata.org/pandas-docs/stable/index.html

which has
read_csv(https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html)
and
to_sql(https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html#pandas.DataFrame.to_sql)
to pull from a CSV into a table.

Best regards,

--
Adrian Klaver
adrian.klaver@aklaver.com

On 1/2/24 11:47, arun chirappurath wrote:

Reply to list
Ccing list

Hi Adrian,

Love this tool..however it doesn't like supporting RDS.

1) This was Daniel Vérité's suggestion not mine.

2) Define "... doesn't like supporting RDS".

a) You can generate an SQL statement without connecting to the database.
Then use that statement directly in the database.

b) If SQLAlchemy can reach the database then csvkit should be able to.

https://csvkit.readthedocs.io/en/latest/
<https://csvkit.readthedocs.io/en/latest/&gt;

Regards
Arun

--
Adrian Klaver
adrian.klaver@aklaver.com

Il giorno mar 2 gen 2024 alle ore 21:17 Adrian Klaver <
adrian.klaver@aklaver.com> ha scritto:

On 1/2/24 11:47, arun chirappurath wrote:

Reply to list
Ccing list

Hi Adrian,

Love this tool..however it doesn't like supporting RDS.

1) This was Daniel Vérité's suggestion not mine.

2) Define "... doesn't like supporting RDS".

a) You can generate an SQL statement without connecting to the database.
Then use that statement directly in the database.

b) If SQLAlchemy can reach the database then csvkit should be able to.

https://csvkit.readthedocs.io/en/latest/
<https://csvkit.readthedocs.io/en/latest/&gt;

Regards
Arun

--
Adrian Klaver
adrian.klaver@aklaver.com

You can user pgloader (https://pgloader.io/) as well to load data from CSV
to Postgres
Paolo

I use csv2table almost every day: https://github.com/f0rk/csv2table

to just emit a create table statement: csv2table --file your_csv.csv

pipe output to psql to create.

easily used to import data as: csv2table --file your_csv.csv --copy
--backslash -1 | psql your_database

use arguments like --timestamp to automagically detect types.

On Fri, Jan 5, 2024 at 12:45 AM Paolo Saudin <paolosaudin@gmail.com> wrote: