3
$\begingroup$

https://www.ocr.org.uk/Images/400305-large-data-set-lds_1-h630-2018-h640-2018-2019-pre-release-data-mei.xlsx

What's the best way to import such dataset?

I have tried to use

dat2 = Import[   "https://www.ocr.org.uk/Images/400305-large-data-set-lds_1-h630-\ 2018-h640-2018-2019-pre-release-data-mei.xlsx", {"Sheets", "Data"}]

which looks ok.

But is there a better way to import this as some kind of DataSet? So I can then call it like

dat2["Swaziland"]

or using the ID

dat2["50"]

ect.

Also how can I make full use of the Headings? Deal with the missing data? At the moment, there are simply shown as , , , ,

What is it with the $Failed?

enter image description here

$\endgroup$
3
$\begingroup$
Import[
    "https://www.ocr.org.uk/Images/400305-large-data-set-lds_1-h630-2018-h640-2018-2019-pre-release-data-mei.xlsx", 
    {"Dataset", "Data"}, "HeaderLines" -> 1
]

Though there's a bug in getting headers in 11.3 that is fixed in the next version. To get around this bug, please use this workaround:

Import["https://www.ocr.org.uk/Images/400305-large-data-set-lds_1-h630-2018-h640-2018-2019-pre-release-data-mei.xlsx", 
    {"Dataset", "Data"}, "HeaderLines" -> 1
] /. System`Convert`ExcelDump`null -> ""

The "" can be replaced with any other value you would like to use in place of the null symbol that is appearing. You could also add another replacement rule for $Failed as well. I will look into why these cells are imported as $Failed. The $Failed appears to be from =#N/A, which is the correct output.

$\endgroup$
  • $\begingroup$ I got quite a lot of StringLength::string: String expected at position 1 in StringLength[SystemConvertExcelDumpnull].` errors .. $\endgroup$ – CasperYC Feb 11 at 22:20
  • 1
    $\begingroup$ Sorry, this is a bug in 11.3 that is fixed in the next version that has yet to be released. In the meantime, I edited my answer with a workaround. $\endgroup$ – GenericAccountName Feb 11 at 22:35

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.