Koha-Library-Management-System/C3/Convert-Excel-to-MARC/English-timed

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

Contributors and Content Editors

Nancyvarkey, PoojaMoolya, Pratik kamble, Sandhya.np14