# Bug in CSV.read?

**URL:** <https://discourse.julialang.org/t/bug-in-csv-read/35562>\
**Category:** General Usage\
**Created:** [March 5, 2020, 9:08am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562 "2020-03-05T09:08:20Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![BLI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bli/32/37206_2.png) [@BLI](https://discourse.julialang.org/u/BLI)\
**Post date:** [March 5, 2020, 9:08am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562/1 "2020-03-05T09:08:20Z")

</div>

I’m trying to represent infinity in Excel, and have tried with `1e18` and `1e19` (since I haven’t found an `Inf` symbol). I then export this to a CSV file, and upon checking the resulting file in Notepad++, the numbers are represented correctly in the ASCI file.

I then import the file into Julia using `CSV.read()`. The results are:

- `1e18` → `1.0e18` (correct)
- `1e19` → `7.766279631452242e17` (incorrect)

Two questions:

1. Is there a good way to represent infinity in Excel (I know, I know – this is not an Excel group),
2. Is the bug in `CSV.read()` similar to interpreting `1e19` in the CSV file as `10^(19)`, which gives overflow? [Note: `7.76...e17` is different from what I get if I do `10^(19)` in Julia…]

---

<div class="post-metadata">

**Author:** ![BLI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bli/32/37206_2.png) [@BLI](https://discourse.julialang.org/u/BLI)\
**Post date:** [March 5, 2020, 9:25am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562/2 "2020-03-05T09:25:44Z")

</div>

Hm… to answer my first question… If I type either of the text strings “Inf” or “inf” in the Excel cell, this appears as `Inf` in the csv-file, and is correctly interpreted as Float64 infinity in Julia.

So this solves my immediate problem. I am still worried about the problem to handle numbers larger than `1e18`, though.

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 5, 2020, 9:29am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562/3 "2020-03-05T09:29:57Z")

</div>

Might be Excel doing something funny? This works:

```julia
using DataFrames, CSV
test = DataFrame(x = [1, 1e18, 1e19, 1e20])
CSV.write("test.csv", test)
CSV.read("test.csv")

```

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [March 5, 2020, 9:33am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562/4 "2020-03-05T09:33:16Z")

</div>

I concur with @nilshg — I cannot replicate this:

```julia
julia> using CSV

julia> tmp = tempname();

julia> write(tmp, "data\n1e19\n");

julia> CSV.read(tmp)
1×1 DataFrame
│ Row │ data │
│ │ Float64 │
├─────┼─────────┤
│ 1 │ 1.0e19 │

```

Please check the contents of the file to see if it is actually written out correctly.

FWIW, using Excel for anything even remotely connected to numbers is the perfect recipe for constantly dealing with issues like this.

---

<div class="post-metadata">

**Author:** ![BLI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bli/32/37206_2.png) [@BLI](https://discourse.julialang.org/u/BLI)\
**Post date:** [March 5, 2020, 9:47am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562/5 "2020-03-05T09:47:06Z")

</div>

No, I don’t think it is Excel that is doing something funny. Here is a screen shot of Excel:  
 ![image](https://global.discourse-cdn.com/julialang/original/3X/4/9/49da64420c88d051cb9c40c4f6824ead4ecc43b5.png)  
In the second row, I have used Scientific number format. In the Fourth row, I have used Number format with 2 decimals.

Here is the resulting content of the CSV file:

```julia
Col1;Col2
1.00E+18;1.00E+19
Inf;inf
1000000000000000000.00;10000000000000000000.00

```

And here is the result of importing the content into Julia using `CSV.read`:  
 ![image](https://global.discourse-cdn.com/julialang/original/3X/d/9/d9b322d74964ae10c02d445d84123eda27cae5f1.png)

As you see, `CSV.read()` appears to handle Scientific number format correctly, but not Number format. [And from here, it seems like long numbers in the Number format are handled somewhat arbitrary… in my other example, 1e18 was handled correctly…

---

<div class="post-metadata">

**Author:** ![BLI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bli/32/37206_2.png) [@BLI](https://discourse.julialang.org/u/BLI)\
**Post date:** [March 5, 2020, 9:48am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562/6 "2020-03-05T09:48:50Z")

</div>

Of course, I can format cells with scientific notation as Scientific number format. But in many cases, my columns contain a mixture of small numbers and infinity (Thermodynamic tables), and it is inconvenient to use Scientific number for small numbers.

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 5, 2020, 9:53am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562/7 "2020-03-05T09:53:44Z")

</div>

> [@BLI](#):
>
> Col1;Col2 1.00E+18;1.00E+19 Inf;inf 1000000000000000000.00;10000000000000000000.00

Okay I can replicate this when creating the same csv file. It seems the decimal points are throwing the read off, when removing them manually from the csv the numbers read in fine.

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [March 5, 2020, 9:53am UTC](https://discourse.julialang.org/t/bug-in-csv-read/35562/8 "2020-03-05T09:53:45Z")

</div>

Yes, that is very likely to be a bug. MWE:

```julia
julia> using CSV

julia> tmp = tempname();

julia> write(tmp, "data\n1000000000000000000.00\n10000000000000000000.00");

julia> CSV.read(tmp)
2×1 DataFrames.DataFrame
│ Row │ data │
│ │ Float64 │
├─────┼────────────┤
│ 1 │ 7.76628e16 │
│ 2 │ 7.76628e17 │

```

Maybe check existing issues for that package and open a new one if this is not known.