英文:
How to import/copy different csv files into 1 table in SQL using R?
问题
我想将这些CSV文件导入SQL数据库并创建一个表，使用R编程。我找不到一个可以完成这个任务的函数。请问有谁可以帮忙？
期望的输出（一个SQL表）：
City_Name Pop PopDen Lit
MI 45000 280 78
MI 37000 310 89
...
...
CH 97000 150 74
CH 67000 220 82
...
英文:
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
...
答案1
得分: 3
我对Windows不太熟悉，但由于它非常流行，你应该可以轻松找到循环遍历*.csv文件的示例。首先准备一个psql语句，用于从一个csv文件上传数据。它会类似于：
psql -c '\copy your_table(City_Name,Pop, PopDen, Lit) FROM '/path/to/csv/.txt' WITH (FORMAT CSV)'
注意：你需要在Windows上输入psql实用程序的完整路径。
测试它是否适用于一个文件，然后在所有CSV文件上进行循环遍历（在cmd.exe窗口中）：
FOR %f in (dir /b *.csv) DO
psql -c '\copy your_table(City_Name,Pop, PopDen, Lit) FROM '%f' WITH (FORMAT CSV)'
DONE
大致类似于这样...
英文:
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...
答案2
得分: 1
使用read.csv()不是将多个大型CSV文件导入R数据框的好选择。为了在R中读取多个CSV文件或从文件夹中读取所有文件，请使用data.table包。data.table是一个第三方库，您需要首先使用install.packages('data.table')来安装它。
# 使用data.table包
library(data.table)
df <-
list.files(path = "/Users/admin/apps/csv-courses/", pattern = "*.csv") %>%
map_df(~fread(.))
df
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论