[R] duckdb table from multiple csv files

[Jeff Newmiller]

Invest the time to save as parquet. CSV is slow even though Arrow is faster at reading it than read.csv.

If you can process a bunch of cases at once with vectorized calculations that will also speed things up... but not all algorithms are vectorizable.

On May 24, 2026 5:19:36 PM PDT, Naresh Gurbuxani <naresh_gurbuxani using hotmail.com> wrote:
>Files are on a local network drive.
>
>I ended up creating a duckdb database and writing all the data into a couple of tables.  Database is approximately 200 GB.
>
>Initially I was directly reading these files one at a time, doing the analysis, keeping analysis results, then moving to next file.  Going through all the files took a few hours.  Then, if I wanted to tweak the analysis, I needed to start over.
>
>I am looking for tools to get faster access to data files, preferably without resaving data.  Some analysis requires a small subset of data.  If all the data were in a few files, then in memory duckdb would work.  There would be no need to resave data.  But with so many files, writing data into duckdb database was needed.
>
>My analysis is mostly complete.  For next time, I want to see if arrow + duckdb will help avoid resaving data in another format.
>
>Sent from my iPhone
>
>On May 24, 2026, at 5:41 PM, John Kane <jrkrideau using gmail.com> wrote:
>
>
>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 using hotmail.com<mailto:naresh_gurbuxani using 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 using r-project.org<mailto:R-help using 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]]
>
>______________________________________________
>R-help using 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.

--
Sent from my phone. Please excuse my brevity.
	[[alternative HTML version deleted]]