 OpenRefine is a useful open source software which can be used to work with different spreadsheets. It is also used as a tool for uploading data from spreadsheets directly onto Wikidata. Today I'm going to run a tutorial showing how to reconcile data and add it onto Wikidata. You can download OpenRefine online for free following the link shown here. You can then open OpenRefine using Google Chrome or Internet Explorer. Once OpenRefine is opened, a spreadsheet can then be uploaded. This can be done either by uploading directly from your computer, using a web address, a clipboard feature, uploading a database or using a link from Google Data. I've decided for this example to work with data from Google Sheets and the example that I'm using is from the Scottish Witchcraft database where I will be looking at the ethnicity of different witches recorded in the database. So I will copy the link and then paste it directly into the text box. From there I am then given a preview of the OpenRefine data and I'm able to then give the project a name. Then on OpenRefine you're then able to see all of the columns that you have used on your original database. And for this I then have the most important information that I'm going to look at and that is the name of the accused witch and the ethnic origin. I also then have the reference which is added at the end which will be a reference for each point that I then upload onto Wikidata. If you follow the reference it will then take you onto a page on the Survey of Scottish Witchcraft Database where you will be able to see more information about each witch, not only their ethnicity. Firstly the spreadsheet on OpenRefine can be neaten up so that there are not columns featured in the spreadsheet which are not needed. This can be done by clicking on the arrow at the top of the column and then going on Edit column and then clicking on Remove this column. This will then remove this column from the spreadsheet. This can be done for any columns that are not needed within the spreadsheet. Once this is done you are then ready to reconcile the data. To reconcile your data you must click on the arrow at the header of the column and scroll down to reconcile and click on Start Reconciling. From there you want to pick the Wikidata service and be ready to start reconciling. OpenRefine gives you suggestions of different items that you can reconcile your data against to have a more accurate result. You can either pick from this list or you are able to search your own. So for ethnicity, ethnic group seems like a better queue number to reconcile against. From there OpenRefine can then reconcile all of the data that you need. Once OpenRefine has finished reconciling your data it will then have a green bar at the top of the header column which then indicates the amount of your data which has then been correctly reconciled. As you can see from the 17 rows within this spreadsheet there are 15 matched and 2 not matched and OpenRefine splits these into two separate groups to make it easier to look at them. So we then know that for Irish we are wanting to go with the Irish people rather than Irish Americans and OpenRefine is doing this by giving us different Wikidata suggestions. If I click on the single tick it will match this one item to this cell. However if I click on the double tick it will then match all of the cells with the same information to the same Wikidata point. Once all of your points have been matched we then have a full dataset for ethnic origin and as a result there is a 100% match rate with a full green bar. Next we can then reconcile other items within the spreadsheet which will be used along with this already reconciled column to upload data onto Wikidata. We can then reconcile other parts of the dataset which would be useful to use in connection with the ethnic origin in order to upload this data onto Wikidata. And so the next step is to reconcile the name of the witches in a similar way to what we've done for the ethnic data. For the name of the witches we can then relate this to Q5 which is for humans and this time Wikidata and OpenRefine have given us the correct list. As names are more difficult to be able to find a connection with we can then also reconcile it against another column within the spreadsheet and for this example a Q's reference would be the most useful as each witch has a Q's reference which is also uploaded onto Wikidata. A Q's witch ID. And then we're ready to also start reconciling for this dataset. You can then reconcile as many different columns as you wish. However in this example two columns is enough. The next step in order to upload data from OpenRefine onto Wikidata is to build a schema which can then be uploaded onto Wikidata. This is used using the Wikidata extension and clicking on edit Wikidata schema. From there you're then able to build a schema which is made up of different items and properties. By clicking on add item we are then able to drag a different reconciled column into this box and as I want to add data about each ethnic origin directly onto the name of each witch I then want to have my main item as the witch which is then called full name in this instance. I then want to add a statement about the witch and by doing this I will click on add statement. I can find the property that I want to add about each Q's witch by searching in this search bar here where I can type to get suggestions about the different properties. I can then drag another reconciled item about the witch and so I'm going to drag in ethnic origin. I'm then going to drag in telling us the ethnic group for each Q's witch that is mentioned in this spreadsheet. The next step is then to add a reference and this can be done by clicking on the add reference box here. There can either be one reference for every single item which is going to be uploaded onto WikiData or each can have a singular reference. This can be done by typing in URL reference and either you can put in one URL directly into this box if every single statement has the same reference or else in this instance I'm going to put in one of the columns taken from the spreadsheet directly into this box by dragging it and this is because each one of these points that I want to add to WikiData has a different reference. Once I have a reference I'm then able to go into the next column to see if there are any issues with my edits. OpenRefine tends to flag up any issues that it sees in your edits so that they are as accurate as possible as you can see in this example there are no issues and so the next tab then shows you the preview of how it will look for each one. For example we can see that John Neal is part of the ethnic group of English people and we have a reference below which will then take us on to more information about each witch issues. Once you're happy with each one of the points and are confident that the data that you're going to upload into WikiData is correct, you're then able to export the data. This can be done either using upload edits to WikiData which will upload your edits directly onto WikiData and is the best approach for smaller data sets under a thousand points or else if you have a very large data set you can then also export to quick statements and this then makes your data into the correct format to be uploaded directly into quick statements which is another program which can be used to upload data directly into WikiData. In this example I'm going to upload the data directly into WikiData and it already tells me that there are no issues with my edits and I'm ready to upload. I then type in an edit summary and can click on upload edits and through open refine I'm then able to upload my edits directly onto WikiData. Once this is complete I'm then able to go on the name of one of the Q's witches which will then take me directly to their WikiData page and if I go down the page I'm then able to see that there are