Converting XLSX.Worksheet to DataFrame

Specific Domains Data
,
1

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!

2

Common operations with XLSX

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")
2 Likes
3

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

4

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

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
2 Likes
5

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

6

It should autodetect most things. If it doesn’t, you may need to transform the column with parse and DateFormat.

1 Like