From Script | Spoken-Tutorial
Jump to: navigation, search
Time Narration
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:

C:\Users\Spoken\Download\TestData.mrk opens.

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.
15:08 Assignment:

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.

Contributors and Content Editors

Nancyvarkey, PoojaMoolya, Pratik kamble, Sandhya.np14