# Converting XLSX.Worksheet to DataFrame

**URL:** <https://discourse.julialang.org/t/converting-xlsx-worksheet-to-dataframe/110300>\
**Category:** Data\
**Tags:** xlsx, excel\
**Created:** [February 16, 2024, 3:45pm UTC](https://discourse.julialang.org/t/converting-xlsx-worksheet-to-dataframe/110300 "2024-02-16T15:45:18Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![PeX](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pex/32/49986_2.png) [@PeX](https://discourse.julialang.org/u/PeX)\
**Post date:** [February 16, 2024, 3:45pm UTC](https://discourse.julialang.org/t/converting-xlsx-worksheet-to-dataframe/110300/1 "2024-02-16T15:45:19Z")

</div>

Hi all,  
I imported an excel file to XLSX data variable named `xf`, now I’m trying to convert one of the worksheets `xf[2]` into a dataframe.  
The worksheet have two columns, one date in the form of `1993-04-30` and the other column is just numbers.  
When I try  
` df = DataFrame(xf[2])`  
I get the error:  
`ArgumentError: no default `Tables.columns` implementation for type: XLSX.Worksheet`

Any ideas how to convert it properly?

Thanks!

---

<div class="post-metadata">

**Author:** ![George9000](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/george9000/32/23619_2.png) [@George9000](https://discourse.julialang.org/u/George9000)\
**Post date:** [February 16, 2024, 4:16pm UTC](https://discourse.julialang.org/t/converting-xlsx-worksheet-to-dataframe/110300/2 "2024-02-16T16:16:24Z")

</div>

Common operations with XLSX

```julia
xf = XLSX.readxlsx(path)
XLSX.sheetnames(xf)

sh1 = xf["Sheet1"] 
@show sh1["A1:B2"]

df = DataFrame(XLSX.readtable(path, "Sheet1")) # note the need for readtable function

# unfortunately, the columns come over as type Any. So....
 CSV.write("output.csv", df)

# then
CSV.read("output.csv", DataFrame; normalizenames = true, dateformat = "Y-m-d")

```

---

<div class="post-metadata">

**Author:** ![PeX](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pex/32/49986_2.png) [@PeX](https://discourse.julialang.org/u/PeX)\
**Post date:** [February 16, 2024, 5:36pm UTC](https://discourse.julialang.org/t/converting-xlsx-worksheet-to-dataframe/110300/3 "2024-02-16T17:36:34Z")

</div>

Thank you! I wouldn’t think to convert it to CSV without your suggestion!

---

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [February 16, 2024, 6:40pm UTC](https://discourse.julialang.org/t/converting-xlsx-worksheet-to-dataframe/110300/4 "2024-02-16T18:40:04Z")

</div>

> [@George9000](#):
>
> ```julia
> # unfortunately, the columns come over as type Any. So....
> CSV.write("output.csv", df)
> 
> # then
> CSV.read("output.csv", DataFrame; normalizenames = true, dateformat = "Y-m-d")
> 
> ```

That’s not necessary. `XLSX.readtable` has a keyword argument for inferring the element type which defaults to `false`:

```julia
julia> df = DataFrame(XLSX.readtable(path, "Sheet1", infer_eltypes=true))
2×2 DataFrame
 Row │ Date Value 
     │ Date Int64
─────┼───────────────────
   1 │ 1993-04-30 7
   2 │ 1993-05-01 22

```

---

<div class="post-metadata">

**Author:** ![PeX](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pex/32/49986_2.png) [@PeX](https://discourse.julialang.org/u/PeX)\
**Post date:** [February 16, 2024, 7:43pm UTC](https://discourse.julialang.org/t/converting-xlsx-worksheet-to-dataframe/110300/5 "2024-02-16T19:43:41Z")

</div>

Thank you for the added information! Can you please add how can I specify the date format when connecting (e.g. yy-mm-dd)?

---

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [February 16, 2024, 7:51pm UTC](https://discourse.julialang.org/t/converting-xlsx-worksheet-to-dataframe/110300/6 "2024-02-16T19:51:38Z")

</div>

It should autodetect most things. If it doesn’t, you may need to transform the column with [parse](https://stackoverflow.com/questions/61882298/how-to-apply-a-function-columnwise-to-julia-dataframe) and [DateFormat](https://docs.julialang.org/en/v1/stdlib/Dates/#Dates.DateFormat).