Koha-Library-Software/C4/Convert-Excel-Data-to-MARC-Format/English

Title of the Script: Convert excel data to .mrk format

Author: Maya Hans and Vaibhavi Satardekar

Keywords: Koha, Koha 24.05, MARC21, MARC Records, MarcEdit 7.7, Excel to MARC Conversion, Delimited Text Translator, Library Automation, Metadata Mapping, TestData File, UTF-8 Encoding, Subfield Mapping, .mrk File, Video tutorial.

Prerequisite: Extract Excel to Marc Codefile

Slide Number 1 Title Slide	Welcome to this Spoken Tutorial on Convert excel data to .mrk format.
Slide Number 2 Learning Objectives Convert excel data to .mrk format	In this tutorial, we will learn how to: Convert excel data to .mrk format.
Slide Number 3 System Requirement Windows 11 Pro Operating System MARC Edit 7.7 Working internet connection	To record this tutorial, I am using Windows 11 Pro Operating System MARC Edit 7.7 A working internet connection.
Slide Number 4 Prerequisites You should have knowledge of Library Science	To practice this tutorial, you should have knowledge of Library Science.
Point to the shortcut icon on Desktop Double click on MarcEdit 7.7	We will use the TestData file which we have extracted in an earlier tutorial. Let us open MarcEdit 7.7 to convert this Excel data file into .mrk format. Recall that we had installed MarcEdit 7.7 in an earlier tutorial.
Point to MarcEdit 7.7.35 By Terry Reese	A window MarcEdit 7.7.35 By Terry Reese opens.
Click on Export Tab Delimited text	Click on the Export Tab Delimited Text.
Point to MarcEdit Delimited Text Translator Highlight the Configure Data and Define Translation tabs	MarcEdit Delimited Text Translator window opens. Here we can see two tabs Configure Data and Define Translation. To upload a file, go to the Source File field and click on its folder icon.
Point to Open File window	Open File window opens.
Click on Text File drop down Select Excel XML File(*.xlsx) From the navigation pane on the left, click on Desktop	At the bottom of the window click on Text File drop down and select Excel XML File format. From the navigation pane on the left, click on Desktop.
Click on TestData file Point to File name field	Now select the file TestData. Note that it appears in the File name field.
Click on Open	At the bottom of the window click on the Open button.
Point to C:\Users\spoken-window\TestData.xlsx	The selected file appears in the Source File field.
Click on Output File	To save the .mrk file click on the folder icon adjacent to the Output File.
Point to Save File	The Save File window opens and prompts us to fill in the File name. From the navigation pane on the left, click on Desktop.
Point to File name field Type TestData Click on the Save	Type the file name as TestData and click on the Save button at the bottom.
Point to C:\Users\spoken-window\Desktop\TestData.mrk	Observe that the selected file appears in the Output File field.
Point to Excel Sheet Name: Sheet1	Also note that the Excel Sheet Name as Sheet1 gets selected automatically. This sheet name is editable. For now we will keep the name as it is.
Scroll down to Options section Point to check-box UTF-8 Encoded	Now go to the Options section. Ensure that the check-box UTF-8 Encoded is selected by default.
Click on Import File	At the top right side of the window click on the Import File button.
Point to MarcEdit Delimited Text Translator Point to Data Snapshot	Note that the tab changes to Define Translation.
Scroll the table	Under Data Snapshot we can see the field details as entered in the Excel file.
Point to Data Snapshot table	We can see the Fields ranging from0 to 13 fields are their with corresponding value0 to 13 with their corresponding values.
Point to Settings Go to Select tab select Field 0	Locate the Settings section. Here will do the mapping of Excel data with Koha MARC Tags. Click on the Select drop down and choose Field 0.
Point to Map To: and Indicators:	Please note you can also customize the Map To and Indicators. It is important that the Fields and Subfield Codes are as per the Koha MARC Tag.
Point to Map To: field type 020$a	In the Map To field I will enter the following value.
Point to Indicators: and Term. Punctuation:	I will leave the Indicators and Term Punctuation as they are.
Point to check box for Constant Data Point to check box for Repeatable subfield	If you want to map the same data into the datafield click on Constant Data. To repeat the same subfield click on the Repeatable subfield check box. For now we will leave them as they are.
Click on Add Argument Point to Arguments 0 020$a 0	Now click on the Add Argument button. On doing so, the field value appears in the Arguments textbox.
Point to Settings Choose Field 1	Now under the Settings section go to the Select drop down and choose Field 1.
In Map To field type 082$a	In the Map To field type following value and click on the Add Argument button.
Click on Add Argument Point to Arguments 1 082$a 0	The field value appears in the Arguments textbox.
Point to Settings Choose Field 2	Again from the Select drop down choose Field 2.
In Map To: field type 100$a In Indicators: type 1 Narration only	In the Map To field type following value. In the Indicators field type 1. Note that: 1 is the first indicator of tag 100. It represents the Surname for the subfield ‘a’.
Field 3 - Map To:245$a - Indicators: 1 Field 4 - Map To:245$c - Indicators: 1 Field 5 - Map To:250$a - Indicators: \\ Field 6 - Map To:260$a - Indicators: 1 Field 7 - Map To:260$b - Indicators: 1 Field 8 - Map To:260$c - Indicators: 1 Field 9 - Map To:300$a - Indicators: \\ Field 10 - Map To:650$a - Indicators: 1 Field 11 - Map To:942$c - Indicators: \\ Field 12 - Map To:952$k - Indicators: \\ Field 13 - Map To:952$e - Indicators: \\	Now pause the video, and complete the mapping of remaining fields.
Point to 245$a and 245$c right click select Join Items	After mapping is complete combine the common subfields. To do so, select common tags for example 245$a and 245$c. Right-click and select Join Items, likewise join all the common tags.
Point to symbol	Note that the asterisk symbol will appear before the selected Tags. The asterisk symbol indicates that the common tags are now joined.
Point to Auto Generate	Alternatively, you can also click on the Auto Generate link to map the fields.
Point to Save Template Sort Fields Calculate common nonfiling data Ignore Header Row Click on checkbox for Save Template and Ignore Header Row	We can also see the following four options. Among these Sort Fields and Calculate common nonfiling data are auto-selected. We will leave them as they are. Next check the check-box for Save Template and Ignore Header Row.
Click on Finish	Scroll up, at the top right corner of the window click on the Finish button.
Point to File name:	On doing so, the Save File window opens. Prompting us to fill in the File name.
In the File name: field type TestData Click on Save	In the File name field, I will type TestData and click on Save button.
Point to MarcEdit Delimited Text Translator Process has been finished. Records saved to: C:\Users\Spoken\Download\TestData.mrk	A pop-up dialogue box appears with the message Process has been finished.
Click on Ok	At the bottom of this dialog box, click on the Ok button.
Narration only .mrk	With this we have successfully converted the excel data file into .mrk format.
Narration only	This brings us to the end of this tutorial.
Slide Number 5 Summary In this tutorial we learned to: Convert Excel data to .mrk format	Let’s quickly recap.
Slide Number 6 Assignment Use the codefile extracted in the previous tutorial assignment Convert it into .mrk format	Assignment As an assignment, Use the codefile extracted in the previous tutorial assignment and Convert it into .mrk format
Slide Number 7 Thank you	Thank you for joining.

Contributors and Content Editors

Mayahans