R help - May 2026 - duckdb table from multiple csv files

I have approximately ten thousand csv files with identical columns and
formats.  I want to run some SQL queries on a virtual database, where all
of these files are treated as one table.  While it is possible to run
SQL query, dbListTables() does not show this table.  Is it possible to
list all tables including those created from arrow FileSystem?

Is it possible to achieve this result without arrow package?

# Create example data
library(data.table)
data("flights", package = "nycflights13")
fwrite(flights[(origin == "EWR")],
"data/flights/ewr_flights.csv")
fwrite(flights[(origin == "JFK")],
"data/flights/jfk_flights.csv")
fwrite(flights[(origin == "LGA")],
"data/flights/lga_flights.csv")

data("airports", package = "nycflights13")
fwrite(airports, "data/airports.csv")

# Verify data saved as intended
dir("data")
[1] "airports.csv" "flights"
dir("data/flights/")
[1] "ewr_flights.csv" "jfk_flights.csv"
"lga_flights.csv"

# Create virtual database with two tables
library(arrow)
library(duckdb)

# csv file successfully registed as a table
con <- dbConnect(duckdb())
duckdb_read_csv(con, "airports", "data/airports.csv")
dbListTables(con)
[1] "airports"

# flights_arrow does not show up as a table
flights_arrow <- open_csv_dataset("data/flights")
duckdb_register_arrow(con, "flights", flights_arrow)
dbListTables(con)
[1] "airports"
dbGetQuery(con, "SELECT table_name FROM information_schema.tables;")
  table_name
1   airports

# SQL queries can be run on flights table
dbGetQuery(con, "SELECT * FROM flights LIMIT 2;")
  year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
arr_delay carrier
1 2013     1   1      517            515         2      830            819      
11      UA
2 2013     1   1      554            558        -4      740            728      
12      UA
  flight tailnum origin dest air_time distance hour minute           time_hour
1   1545  N14228    EWR  IAH      227     1400    5     15 2013-01-01 10:00:00
2   1696  N39463    EWR  ORD      150      719    5     58 2013-01-01 10:00:00

I am not really sure what you are doing here.

Where are the files stored? Are they in one place?
What size are they?

On Sun, 24 May 2026 at 09:35, Naresh Gurbuxani <naresh_gurbuxani at
hotmail.com>
wrote:
>
> I have approximately ten thousand csv files with identical columns and
> formats.  I want to run some SQL queries on a virtual database, where all
> of these files are treated as one table.  While it is possible to run
> SQL query, dbListTables() does not show this table.  Is it possible to
> list all tables including those created from arrow FileSystem?
>
> Is it possible to achieve this result without arrow package?
>
> # Create example data
> library(data.table)
> data("flights", package = "nycflights13")
> fwrite(flights[(origin == "EWR")],
"data/flights/ewr_flights.csv")
> fwrite(flights[(origin == "JFK")],
"data/flights/jfk_flights.csv")
> fwrite(flights[(origin == "LGA")],
"data/flights/lga_flights.csv")
>
> data("airports", package = "nycflights13")
> fwrite(airports, "data/airports.csv")
>
> # Verify data saved as intended
> dir("data")
> [1] "airports.csv" "flights"
> dir("data/flights/")
> [1] "ewr_flights.csv" "jfk_flights.csv"
"lga_flights.csv"
>
> # Create virtual database with two tables
> library(arrow)
> library(duckdb)
>
> # csv file successfully registed as a table
> con <- dbConnect(duckdb())
> duckdb_read_csv(con, "airports", "data/airports.csv")
> dbListTables(con)
> [1] "airports"
>
> # flights_arrow does not show up as a table
> flights_arrow <- open_csv_dataset("data/flights")
> duckdb_register_arrow(con, "flights", flights_arrow)
> dbListTables(con)
> [1] "airports"
> dbGetQuery(con, "SELECT table_name FROM
information_schema.tables;")
>   table_name
> 1   airports
>
> # SQL queries can be run on flights table
> dbGetQuery(con, "SELECT * FROM flights LIMIT 2;")
>   year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
> arr_delay carrier
> 1 2013     1   1      517            515         2      830
> 819        11      UA
> 2 2013     1   1      554            558        -4      740
> 728        12      UA
>   flight tailnum origin dest air_time distance hour minute
>  time_hour
> 1   1545  N14228    EWR  IAH      227     1400    5     15 2013-01-01
> 10:00:00
> 2   1696  N39463    EWR  ORD      150      719    5     58 2013-01-01
> 10:00:00
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> https://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

-- 
John Kane
Kingston ON Canada

	[[alternative HTML version deleted]]

Have a look at

	https://github.com/jqnatividad/qsv

and its cat command, which accepts wildcards. It can write to sqlite3
(so you can get it into duckdb), but also has an sqlp command.

el


On 2026/05/24 15:35, Naresh Gurbuxani wrote:
> 
> I have approximately ten thousand csv files with identical columns 
> and formats.  I want to run some SQL queries on a virtual database, 
> where all of these files are treated as one table.
[...]