Question 209057 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to import/copy different csv files into 1 table in SQL using R?

I have multiple csv files (1 file for each city) having following structure.

An example of two out of many files:

CSV File 1:

City_Name  Pop  PopDen  Lit
MI  45000  280  78
MI  37000  310  89
...
...

CSV File 2:

City_Name  Pop  PopDen  Lit
CH  97000  150  74
CH  67000  220  82
...
...

I want to import these csv files into SQL database as 1 table using R. I am not able to find a function to do it. Could anyone please help.

Desired Output (1 Table in SQL):

City_Name  Pop  PopDen  Lit
MI  45000  280  78
MI  37000  310  89
...
...
CH  97000  150  74
CH  67000  220  82
...
Tags:

2

Answers


  1. 0

    Using read.csv() is not a good option to import multiple large CSV files into R Data Frame. In order to read multiple CSV files or all files from a folder in R, use data.table package. data.table is a third-party library, you need to first install it by using install.packages(‘data.table’).

    # Use data.table package
  library(data.table)
  df <- 
   list.files(path = "/Users/admin/apps/csv-courses/", pattern = "*.csv") %>% 
   map_df(~fread(.))
df
    Login or Signup to reply.
  2. 0

    I am not so familiar with Windows, but since it’s so popular, you shouldn’t have any problem finding examples of looping over *.csv files. First prepare a psql statement that uploads the data from one csv. It will be something like:

    psql -c 'copy your_table(City_Name,Pop, PopDen, Lit) FROM '/path/to/csv/.txt' WITH (FORMAT CSV)'

    Test that it works for one file, then loop over all CSV files (in cmd.exe window):

    FOR %f in (dir /b *.csv) DO 
psql -c 'copy your_table(City_Name,Pop, PopDen, Lit) FROM '%f' WITH (FORMAT CSV)'
DONE

    Something along those lines…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search