Importing a subset of variables/columns from a .csv dataset

[james xu]

Dear Statalist,

I'm trying to import a very large (over 200 millions rows with over 40 variables) CSV file into the Stata. However I only need to use a couple of the variables in the CSV file. They are not saved by each other so I cannot use the colrange() option.

I noticed there is a extvarlist parameter for the import delimited command and based on my reading of the manual, it seems I can specify the columns that I wish to import. However, I tried but never got it done. I always got error message as below.

import delimited amount using "contribDB_2024.csv", varnames(1) clear rowrange(1:4)
(encoding automatically selected: ISO-8859-1)
could not name variables specified
r(198);

Can someone help with how I should use this function or is there any other way around to import a subset of variables from a huge CSV file?

Thank you!

[Stephen Jenkins]

My reading of -help import delimited- is that the 'extvarlist' option allows you to rename the variables that you are importing ... but the variables (=columns) that you are importing still have to be otherwise specified, presumably using the rowrange and colrange options if you want to select only a subset of variables/columns. If the columns are not continguous, then perhaps the way ahead is to import sets of columns/variables in chunks and then merge them together afterwards

[Evgeny Saburov]

James xy

They are not saved by each other so I cannot use the colrange() option.

It doesn't look like -import delimited- can refer to variable names (or first row's values, in .csv terms) when importing a .csv, but you can solve this by creating such a capability yourself.
Here's my take on what I think Stephen had in mind:

clear all

// Get variable names from the first row of a csv and store them in a separate frame (Stata 16 and above) that will be later used for lookups.
frame create variable_names 
frame variable_names: import delimited "your_data.csv", varnames(1) rowrange(1:1) clear
frame variable_names: describe, replace clear
frame variable_names: list name

// Import each required csv column separately one by one and save it in a temporary file (might be possible to use frames instead for speed improvements, if RAM capacity is not an issue).
foreach var in "c1" "c3" "c4" { // enter needed variables here using their names
    quietly: frame variable_names: levelsof position if name == "`var'" // this will store an `r(levels)' macro containing a single "position" value related to the "name", as "name" can only be unique in this case. 
    import delimited "your_data.csv", varnames(1) colrange(`r(levels)':`r(levels)') clear // importing a single column of a csv
    gen double obs_no = _n // create a key variable for later -merge-
    tempfile t_`var'
    save `t_`var''
}

// Merge all saved tempfiles
use `t_c1', clear 
foreach var in "c3" "c4" { 
    merge 1:1 obs_no using `t_`var'', nogen
}

drop obs_no

[Stephen Jenkins]

Evgeny Saburov You are indeed a mind reader! But also read my mind better than me ...