# How to specify thousand separator with CSV.read data

**URL:** <https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890>\
**Category:** General Usage\
**Tags:** question, csv\
**Created:** [September 7, 2022, 1:37pm UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890 "2022-09-07T13:37:54Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nikx](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nikx/32/36723_2.png) [@Nikx](https://discourse.julialang.org/u/Nikx)\
**Post date:** [September 7, 2022, 1:37pm UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890/1 "2022-09-07T13:37:54Z")

</div>

My Excel spread uses apostrophes to separate thousands and even worse it uses" ’ " instead of straight apostroph e.g. 1’000. Is there a way to specify this in CSV.read()? Because when I import data it interprets 1’000 as 1\x92000.

Excel Data comes in the form of:  
Amount;Date;Time  
1’000;10.01.2022;13:22:17  
6’000;10.01.2022;13:20:12  
3’000;10.01.2022;13:23:08

then I use  
df=CSV.read(“filepath.csv”, delim =“;”, dateformat= “dd.mm.yyyy”, normalizenames= true, DataFrame);

println(df) shows the Amount as String7 which prohibits me from doing any calculation with the data.

![image](https://global.discourse-cdn.com/julialang/original/3X/d/3/d39072dab42d36d1d6cd709ff056be6cad5db297.png)

---

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [September 7, 2022, 1:41pm UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890/2 "2022-09-07T13:41:14Z")

</div>

Welcome to the community! Can you post an example of the data that you’re trying to read so that others can easily copy/paste it as they attempt to help? Thanks!

---

<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:** [September 7, 2022, 3:31pm UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890/3 "2022-09-07T15:31:20Z")

</div>

> [@Nikx](#):
>
> Is there a way to specify this in CSV.read()?

There is not:

> <https://github.com/JuliaData/CSV.jl/issues/626>
>
> This feature was listed in https://github.com/JuliaData/CSV.jl/issues/3 , but no…t implemented.  
> 
> https://github.com/JuliaData/CSV.jl/issues/207 has a complicated workaround (but keyword transforms does not exists anymore?), and says "there isn't a "thousands" separator option, but we could potentially add on".
> 
> Practical example, data is two first rows from table 2 on page 10 in https://www.istat.it/it/files//2020/05/Rapporto\_Istat\_ISS.pdf
> \`\`\`
> a=CSV.File(IOBuffer("""
> Alessandria 95,7 98,2 -12,8 91,0 1.199 693 222 18,5
> Ancona 76,6 84,3 -10,7 49,4 704 528 86 12,2
> """), delim=" ", header = false, decimal = ',')
> \`\`\`
> Row 1, column 6 would ideally be Int 1199. Here comma is a decimal separator and period is a thousands separator.

---

<div class="post-metadata">

**Author:** ![stillyslalom](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stillyslalom/32/45687_2.png) [@stillyslalom](https://discourse.julialang.org/u/stillyslalom)\
**Post date:** [September 7, 2022, 3:47pm UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890/4 "2022-09-07T15:47:36Z")

</div>

As a workaround, you can just remove the separator and parse the resulting string as a number. In the DataFrames.jl mini-language:

```julia
julia> transform!(df, :Amount => ByRow(str -> parse(Int, replace(str, '’' => ""))) => :Amount)
3×3 DataFrame
 Row │ Amount Date Time
     │ Int64 Date String15
─────┼──────────────────────────────
   1 │ 1000 2022-01-10 13:22:17
   2 │ 6000 2022-01-10 13:20:12
   3 │ 3000 2022-01-10 13:23:08

```

or more simply

```julia
df.Amount = parse.(Int, replace.(df.Amount, '’' => ""))

```

---

<div class="post-metadata">

**Author:** ![stevengj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stevengj/32/71_2.png) [@stevengj](https://discourse.julialang.org/u/stevengj)\
**Post date:** [September 7, 2022, 4:15pm UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890/5 "2022-09-07T16:15:20Z")

</div>

> [@Nikx](#):
>
> CSV.read(“filepath.csv”, delim =“;”)

You should also be able to use `CSV.read`: read the file into a string, delete the delimiter, and do `CSV.read` on an in-memory buffer (`IOBuffer`):

```julia
s = filter(!=('’'), read("filepath.csv", String))
CSV.read(CSV.File(IOBuffer(s)), delim=';', ....)

```

---

<div class="post-metadata">

**Author:** ![Nikx](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nikx/32/36723_2.png) [@Nikx](https://discourse.julialang.org/u/Nikx)\
**Post date:** [September 7, 2022, 4:33pm UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890/6 "2022-09-07T16:33:46Z")

</div>

Thanks a lot for the suggestions.

---

<div class="post-metadata">

**Author:** ![quinnj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/quinnj/32/11_2.png) [@quinnj](https://discourse.julialang.org/u/quinnj)\
**Post date:** [September 8, 2022, 3:49pm UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890/7 "2022-09-08T15:49:08Z")

</div>

@drvi [just added support](https://github.com/JuliaData/Parsers.jl/pull/128) for this in the Parsers.jl package, so now we just need to plumb support for this in the CSV.jl package if someone wanted a pretty easy first issue to try out.

---

<div class="post-metadata">

**Author:** ![Yuan-Ru-Lin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/yuan-ru-lin/32/46068_2.png) [@Yuan-Ru-Lin](https://discourse.julialang.org/u/Yuan-Ru-Lin)\
**Post date:** [June 16, 2024, 1:10am UTC](https://discourse.julialang.org/t/how-to-specify-thousand-separator-with-csv-read-data/86890/8 "2024-06-16T01:10:58Z")

</div>

Just so whoever stumbles across this problem knows. The issue is fixed by [Support groupmark by LilithHafner · Pull Request #1093 · JuliaData/CSV.jl · GitHub](https://github.com/JuliaData/CSV.jl/pull/1093) and according to [this example](https://csv.juliadata.org/stable/examples.html#thousands_example), you can now do this.

```julia
using CSV

# In many places in the world, digits to the left of the decimal place are broken into
# groups by a thousands separator. We can ignore those separators by passing the `groupmark`
# keyword argument.
data = """
x y
1 2
2 1,729
3 87,539,319
"""

file = CSV.File(IOBuffer(data); groupmark=',')

```