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

Question

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

Answer 1

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

Answer 2

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)'

Note: you'll have to enter the full path on Windows to the psql utility.

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