# Partition a large CSV file into smaller files without loading into memory

**URL:** <https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673>\
**Category:** General Usage\
**Tags:** question, csv, io\
**Created:** [March 9, 2019, 12:12pm UTC](https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673 "2019-03-09T12:12:57Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![greg\_plowman](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/greg_plowman/32/8100_2.png) [@greg\_plowman](https://discourse.julialang.org/u/greg_plowman)\
**Post date:** [March 9, 2019, 12:12pm UTC](https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673/1 "2019-03-09T12:12:57Z")

</div>

I want to split (partition) a large `csv` file into separate smaller `csv` files based on the value of a particular column.

I also want to do this row by row, without loading the entire file into memory.

My current solution is using `CSV` and iterating over the rows, selecting the io stream based on the value of column `col1`. But I don’t know the best way to efficiently write that row. Currently I’m using `Tables.eachcolumn` as below:

```julia
getIO(col1) -> returns appropriate io stream (an open `csv` file)

csvfile = CSV.File(filename)
sch = Tables.schema(csvfile)

for row in csvfile
    #write(getIO(row.col1), row) # I want to do something like this

    io = getIO(row.col1)
    Tables.eachcolumn(sch, row) do val, col, name
        print(io, val)
        col < numcols && print(io, ", ")
    end
    println(io)
end

```

Is there a better way?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 9, 2019, 12:32pm UTC](https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673/2 "2019-03-09T12:32:17Z")

</div>

I just use julia’s `run` to call `sprint`.

Works on Windows too if u install git and set path to include all the bins from git.

See [https://www.google.com/url?sa=t&source=web&rct=j&url=https://stackoverflow.com/questions/54956361/julia-how-to-execute-a-system-command-from-julia-code&ved=2ahUKEwj24eCaiPXgAhUTf30KHan4D2gQFjAAegQIBRAB&usg=AOvVaw3xxTqGVTL2DejClxfiZtfM](https://www.google.com/url?sa=t&source=web&rct=j&url=https://stackoverflow.com/questions/54956361/julia-how-to-execute-a-system-command-from-julia-code&ved=2ahUKEwj24eCaiPXgAhUTf30KHan4D2gQFjAAegQIBRAB&usg=AOvVaw3xxTqGVTL2DejClxfiZtfM)

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [March 9, 2019, 12:55pm UTC](https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673/3 "2019-03-09T12:55:17Z")

</div>

If the first column is easy to parse (no escaping, etc.), then I’d skip using CSV so that you don’t need to parse the other columns:

```julia
julia> open("/tmp/t1.csv") do f1
           open("/tmp/t2.csv", "w") do f2
               for l in eachline(f1)
                   c1 = parse(Int64, l[1:findfirst(==(','), l) - 1])
                   if c1 == 2
                       write(f2, l, "\n")
                   end
               end
           end
       end

```

---

<div class="post-metadata">

**Author:** ![greg\_plowman](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/greg_plowman/32/8100_2.png) [@greg\_plowman](https://discourse.julialang.org/u/greg_plowman)\
**Post date:** [March 10, 2019, 8:09am UTC](https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673/4 "2019-03-10T08:09:01Z")

</div>

> [@xiaodai](#):
>
> I just use julia’s `run` to call `sprint` .
> 
> Works on Windows too if u install git and set path to include all the bins from git.

Perhaps you meant `split`?  
I would prefer a pure Julia solution though.

---

<div class="post-metadata">

**Author:** ![greg\_plowman](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/greg_plowman/32/8100_2.png) [@greg\_plowman](https://discourse.julialang.org/u/greg_plowman)\
**Post date:** [March 10, 2019, 8:14am UTC](https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673/5 "2019-03-10T08:14:33Z")

</div>

Thanks Rob! This is much simpler and about 3x faster than my original method using CSV.

As an aside, I tried using `SubString` rather then indexing into the line because I thought it would save allocating a new string on each iteration. But there was no improvement (in fact slightly slower).

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 10, 2019, 8:36am UTC](https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673/6 "2019-03-10T08:36:22Z")

</div>

```julia
run(`split large_file.csv`)

```

is as Julia as it comes

---

<div class="post-metadata">

**Author:** ![bennedich](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bennedich/32/4894_2.png) [@bennedich](https://discourse.julialang.org/u/bennedich)\
**Post date:** [March 10, 2019, 12:26pm UTC](https://discourse.julialang.org/t/partition-a-large-csv-file-into-smaller-files-without-loading-into-memory/21673/7 "2019-03-10T12:26:14Z")

</div>

> [@xiaodai](#):
>
> as Julia as it comes

I would certainly not call it that. I’d call it a bash solution. It also doesn’t do what OP asks, which is to partition a file based on a column value. No doubt that can be achieved in bash with `awk` for example, but I too would prefer an implementation in Julia, for the following reasons:

- The logic to parse and select file based on column can more easily be reused if written as a Julia function (like `getIO` in OP’s example) than embedded in an `awk` command
- With a pure Julia solution, we can cleanly unit test this functionality with mock data, using in-memory IO streams
- Guaranteed to work (and work the same) on all platforms

I’d second Rob’s solution.