|00:01||Welcome to the Spoken Tutorial on conversion of Excel data to Marc 21 format.|
|00:09||In this tutorial, we will learn to convert Excel data to Marc 21 format on a 64-bit Windows machine.|
|00:19||To record this tutorial, I am using: Windows 10 Pro and
Firefox web browser.
|00:29||To follow this tutorial, learners should be familiar with Library Science.|
|00:35||Before moving ahead, please ensure that you have the following on your machine-
Windows 10, 8 or 7,
|00:45||Any web browser. For eg: Internet Explorer, Firefox or Google Chrome.|
|00:53||Earlier in the same series, we had installed MarcEdit 7 on the Desktop.|
|01:00||Open the same MarcEdit 7 by double-clicking on the icon.|
|01:07||A window named MarcEdit 7.0.250 By Terry Reese opens.|
|01:15||Locate and click on the tab Export Tab Delimited Text.|
|01:21||Under the Source File field, locate the icon for a folder.|
|01:27||The source file is an Excel file which we are converting into .mrk format.|
|01:34||Click on this icon for the Folder and browse for the Excel file in the field for File name.|
|01:42||Click on the drop-down adjacent to File name.|
|01:46||If you have Microsoft Excel 97/2000/XP/2003 (.xls) then choose the format Excel File(*.xls).|
|02:03||And if you have, Microsoft Excel 2007/2010/2013 XML(.xlsx) then choose the format Excel File(*.xlsx).|
|02:21||As I have the .(dot)xlsx file, I will select Excel XML File(*.xlsx).|
|02:32||Next, go to the left-side folders and select the folder where your Excel file is saved.|
|02:40||I have selected Downloads because that’s where I saved my Excel file.|
|02:47||So, from the Downloads folder, I have selected TestData.xlsx.|
|02:55||When the file TestData.xlsx is selected, it appears in the File name field.|
|03:04||Now, click on the Open button at the bottom of the window.|
|03:09||The same window re-opens with the Source File as C:\Users\spoken\Downloads\TestData.xlsx.|
|03:21||Now, click on the folder icon adjacent to the Output File.|
|03:27||On doing so, Save File window opens prompting us to fill in the File name.|
|03:34|| On the same window, I will click on the Downloads folder located at the left hand side.
And, type the File name: as TestData.
|03:46||Now click on Save button at the bottom of the page.|
|03:51|| The same window appears again.
The Output file field shows: C:\Users\spoken\Downloads\TestData.mrk.
|04:06|| Note that Excel Sheet Name: Sheet1 gets selected automatically by MarcEdit 7.
However, this sheet name is editable.
|04:20||Under the Section Options, the check-box UTF-8 Encoded is selected by MarcEdit 7, by default.|
|04:32||Click on the Next button at the right side of the same window.|
|04:37|| Again a new window, MarcEdit Delimited Text Translator opens.
The heading says Data Snapshot.
|04:48||This window will have all the field details as per entries made in the Excel file.|
|04:55||We will see the Fields ranging from 0 to 8 and above with their corresponding values.|
|05:03||For example, Field 0 has a value of 978-3-319-47238-6 (ISBN) on my machine.|
|05:17||You may see a different value as per your Excel sheet.|
|05:22||Under the section DataSnapshot, locate the section Settings.|
|05:28||Go to the tab Select and from the drop-down select Field 0.|
|05:35||With this we will be doing the mapping of Excel data with Koha MARC Tags.|
|05:43||Remember, you can customize Map To: and Indicators.|
|05:49||However, it is important that the Fields and Subfield Codes are as per the Koha MARC Tag.|
|05:58||For more information on MARC Tags, visit the link of official Library of Congress site.|
|06:07||On the browser, type this URL and click on search.|
|06:15||Recall that Map To: values entered in the field are referred from an earlier tutorial in this series.|
|06:24||I will enter 020$a in the Map To: field.|
|06:31||This sequence will change as per your Excel data.|
|06:36||I will leave Indicators: and Term. Punctuation: as they are.|
|06:42||However, you may fill these fields as directed by the Koha MARC Tags.|
|06:49||Next is the check-box for Constant Data.|
|06:54||Click this if you wish to map the same information into the data field for each entry, in the delimited text document.|
|07:04||Click Repeatable subfield if you want to repeat the same subfield.|
|07:10||Next, click on the button Add Argument.|
|07:15||On doing so, the value 0 020$a 0 appears in the field under the section Arguments.|
|07:25||Similarly, let’s map all the other fields.|
|07:30||Under the section Settings, go to Select. From the drop- down, select Field 1.|
|07:39||In the field for Map To, type: 080$a.|
|07:46||Now, click on the button Add Argument.|
|07:50||On doing so, the value 1 080$a 0 appears in the field under the section Arguments.|
|08:01||Under the tab Select, from the drop-down, select Field 2.|
|08:07||In the field for Map To, type: 100$a.|
|08:13||In the field for Indicators, type 1.|
|08:17||Note that 1 is the first indicator of tag 100 and it represents the Surname for the subfield ‘a’.|
|08:28||Likewise, complete the mapping of all the fields up to Field 13 as being shown in the drop-down under Select.|
|08:39||Notice the up and down arrows adjacent to each field.|
|08:44||You may use these to change the sequence of the values that appear.|
|08:50||Under the section Arguments, the Tags that are common with different sub-fields need to be joined.|
|08:58||For that, do the following- Select common tags for example 245$a and 245$c.|
|09:09||Then right-click on common tags and from drop-down select Join Items.|
|09:17||This will create a grouping of the fields that are with the same type.|
|09:23||Note that the * (asterisk symbol) will appear before the selected Tags.|
|09:29||The * asterisk symbol indicates that the common tags are now joined.|
|09:35||Alternately, you can do the mapping of fields by clicking on the tab Auto Generate to import the values of respective fields from 0 to 13, in the fields provided for Arguments.|
|09:52||However, I have done mapping manually. So, I will not click Auto Generate option.|
|09:59||Next we see four options.|
|10:02||First one is Save Template.|
|10:06||Use this if you want to save the same mapping for future use.|
|10:12||The saved template will be used if in case you face any problem with data conversion.|
|10:20||If we choose the option Save Template, then we will be prompted to give it a name and specify the directory for saving it.|
|10:31||It will get saved as .mrd file|
|10:36||Access this template in the future by clicking “Load Template” at the right side of the window.|
|10:44||Second option is Sort Fields.|
|10:48||Third option is Calculate common nonfiling data.|
|10:54||Fourth option is Ignore Header Row.|
|10:58||Click here, if you have a header in the Excel sheet and if you need to ignore the headings.|
|11:05||Among these, Sort Fields and Calculate common nonfiling data are auto-selected by MarcEdit 7.|
|11:15||I will leave them as they are.|
|11:18||Now, I will check the check-box for Save Template and Ignore Header Row.|
|11:26||Next, locate and click on the tab Finish on the top right corner of the page.|
|11:34||On doing so, Save File window opens, prompting us to fill in the File name.|
|11:41||On the same window, I will click on the Downloads folder, located at the left hand side.|
|11:48||And, in the field for File name, I will type TestData.|
|11:54||Now, click on Save button at the bottom of the page.|
|11:59|| A pop-up window with the message: Process has been finished. Records saved to:
|12:14||Click on the Ok button at the bottom of this dialog-box.|
|12:19||With this, .mrk file has been successfully saved in the directed location which is the Downloads folder.|
|12:29|| A new page MarcEdit 7.0.250 By Terry Reese, opens.
Locate and click on the icon MarcEditor.
|12:42|| A new page MarcEditor opens.
On the main Menu, click on File and from the drop- down select Open.
|12:55||A window Open File opens showing TestData.mrk file.|
|13:02||Click and select the TestData.mrk file.|
|13:07||It will show in the field for File name.|
|13:11||Now click on Open at the bottom of the window.|
|13:16||Another window MarcEditor: TestData.mrk opens with all the details.|
|13:24||On the same window, from the main menu, click on the File.|
|13:29||Now, from the drop-down select Compile File into MARC.|
|13:35||Another new window Save File opens.|
|13:39||Here, locate File Name: and type the appropriate name in the field.|
|13:46||I will type TestData.|
|13:50||Koha, by default, selects MARC Files (*.mrc) in the field Save as type:.|
|14:00||Now, click on the Save button located at the bottom of the window.|
|14:06||Upon doing so, on the same window at the bottom, you will see 5 records processed in 0.166228 seconds.|
|14:19||This is because I imported only 5 records. You will see a different number of records and processed time as per your data.|
|14:29||With this, we have converted Excel data of our library into Marc 21 format successfully.|
|14:37||Marc 21 format is the standard format used in Koha for cataloging and importing data into Koha.|
|14:46||Now, close this window. To do so, go to the top right corner and click on the Close button.|
|14:55||Let us summarize. In this tutorial, we have learnt to- convert Excel data to Marc 21 format on a 64-bit Windows machine.|
Prepare a list of 10 records in Excel and convert those records into MARC by using MarcEdit 7.
|15:20|| The video at the following link summarizes the Spoken Tutorial project.
Please download and watch it.
|15:27|| The Spoken Tutorial Project team conducts workshops and gives certificates.
For more details, please write to us.
|15:35||Please post your timed queries in this forum.|
|15:39||Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.|
|15:45||More information on this mission is available at this link.|
|15:50|| This is Bella Tony from IIT Bombay, signing off.
Thank you for joining.