Converting XLSX.Worksheet to DataFrame

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?


Common operations with XLSX

xf = XLSX.readxlsx(path)

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"output.csv", DataFrame; normalizenames = true, dateformat = "Y-m-d")
1 Like

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

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

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

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

1 Like