Difference between revisions of "Koha-Library-Software/C4/Convert-Excel-Data-to-MARC-Format/English"
From Script | Spoken-Tutorial
(Created page with "'''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...") |
|||
| Line 32: | Line 32: | ||
|| To record this tutorial, I am using | || To record this tutorial, I am using | ||
* '''Windows 11 Pro Operating System''' | * '''Windows 11 Pro Operating System''' | ||
| − | * '''MARC Edit | + | * '''MARC Edit 7.7 ''' and |
* A working internet connection. | * A working internet connection. | ||
| Line 75: | Line 75: | ||
From the navigation pane on the left, click on '''Desktop''' | From the navigation pane on the left, click on '''Desktop''' | ||
| − | || At the bottom of the window click on '''Text File''' drop down | + | || At the bottom of the window click on '''Text File''' drop down select '''Excel XML File''' format. |
From the navigation pane on the left, click on '''Desktop'''. | From the navigation pane on the left, click on '''Desktop'''. | ||
| Line 105: | Line 105: | ||
Click on the '''Save''' | Click on the '''Save''' | ||
| − | || Type the file name as '''TestData '''and click on the '''Save '''button at the bottom. | + | || Type the file name as '''TestData ''' and click on the '''Save '''button at the bottom. |
|- | |- | ||
|| Point to '''C:\Users\spoken-window\Desktop\TestData.mrk''' | || Point to '''C:\Users\spoken-window\Desktop\TestData.mrk''' | ||
| − | || Observe that the selected file appears in the '''Output File '''field. | + | ||'''MarcEdit Delimited Text Translator''' window opens. |
| + | |||
| + | Observe that the selected file appears in the '''Output File '''field. | ||
|- | |- | ||
|| Point to '''Excel Sheet Name: Sheet1''' | || Point to '''Excel Sheet Name: Sheet1''' | ||
| − | || | + | || From the '''Excel Sheet Name ''' dropdown '''Sheet1 '''gets selected automatically. |
This sheet name is editable. | This sheet name is editable. | ||
| Line 136: | Line 138: | ||
|- | |- | ||
|| Point to '''Data Snapshot '''table | || Point to '''Data Snapshot '''table | ||
| − | || We can see the '''Fields''' ranging | + | || We can see the '''Fields''' ranging from 0 to 13 with their corresponding values. |
|- | |- | ||
|| Point to '''Settings''' | || Point to '''Settings''' | ||
| Line 153: | Line 155: | ||
|- | |- | ||
|| Point to '''Map To: '''field type '''020$a''' | || Point to '''Map To: '''field type '''020$a''' | ||
| − | || In the '''Map To field '''I will enter | + | || In the '''Map To field '''I will enter '''020$a''' |
|- | |- | ||
|| Point to '''Indicators: '''and '''Term. Punctuation:''' | || Point to '''Indicators: '''and '''Term. Punctuation:''' | ||
| Line 161: | Line 163: | ||
Point to check box for '''Repeatable subfield''' | Point to check box for '''Repeatable subfield''' | ||
| − | || If you want to map the same data into the datafield click on '''Constant Data'''. | + | || 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. | To repeat the same subfield click on the '''Repeatable subfield '''check box. | ||
| Line 170: | Line 172: | ||
Point to '''Arguments 0 020$a 0''' | Point to '''Arguments 0 020$a 0''' | ||
| − | || | + | || Now click on the '''Add Argument ''' button. |
| − | + | ||
| − | Now click on the '''Add Argument '''button. | + | |
On doing so, the field '''value''' appears in the '''Arguments '''textbox. | On doing so, the field '''value''' appears in the '''Arguments '''textbox. | ||
| Line 182: | Line 182: | ||
|- | |- | ||
|| In '''Map To '''field type '''082$a''' | || In '''Map To '''field type '''082$a''' | ||
| − | || In the '''Map To '''field type | + | || In the '''Map To '''field type '''082$a''' and click the '''Add Argument '''button. |
|- | |- | ||
|| Click on '''Add Argument''' | || Click on '''Add Argument''' | ||
| − | Point to '''Arguments 1 082$a | + | Point to '''Arguments 1 082$a ''' |
|| The field''' '''value appears in the '''Arguments '''textbox. | || The field''' '''value appears in the '''Arguments '''textbox. | ||
|- | |- | ||
| Line 199: | Line 199: | ||
Narration only | Narration only | ||
| − | || In the '''Map To '''field type | + | || In the '''Map To '''field type '''100$a''' |
In the '''Indicators''' field type '''1'''. | In the '''Indicators''' field type '''1'''. | ||
Note that: | Note that: | ||
| − | * '''1 '''is the''' first indicator''' of tag '''100'''. | + | * '''1 ''' is the''' first indicator''' of tag '''100'''. |
* It represents the '''Surname '''for the '''subfield ‘a’'''. | * It represents the '''Surname '''for the '''subfield ‘a’'''. | ||
| Line 229: | Line 229: | ||
'''Field 13 - Map To:952$e '''- '''Indicators: \\''' | '''Field 13 - Map To:952$e '''- '''Indicators: \\''' | ||
| − | || Now pause the video, and complete the mapping of remaining fields. | + | || Now pause the video, and complete the mapping of the remaining fields. |
|- | |- | ||
|| Point to '''245$a''' and '''245$c '''right click select '''Join Items''' | || Point to '''245$a''' and '''245$c '''right click select '''Join Items''' | ||
| − | || After mapping is complete combine the common subfields. | + | || After the mapping is complete combine the common subfields. |
| − | + | For that, select the common tags for example '''245$a''' and '''245$c'''. | |
| − | Right-click and select '''Join Items''', | + | Right-click and select '''Join Items''', similarly join all the common tags. |
|- | |- | ||
|| Point to ''' | || Point to ''' | ||
* '''symbol | * '''symbol | ||
| − | || Note that the asterisk | + | || Note that the asterisk symbols appears before the selected '''Tags'''. |
The asterisk symbol indicates that the common '''tags''' are now joined. | The asterisk symbol indicates that the common '''tags''' are now joined. | ||
| Line 260: | Line 260: | ||
We will leave them as they are. | We will leave them as they are. | ||
| − | Next check the check- | + | Next check the check-boxes for '''Save Template '''and''' Ignore Header Row'''. |
|- | |- | ||
|| Click on '''Finish''' | || Click on '''Finish''' | ||
| − | || Scroll up, at the top right corner of the window click on the '''Finish''' button. | + | || Scroll up, at the top right corner of the window, click on the '''Finish''' button. |
|- | |- | ||
|| Point to '''File name:''' | || Point to '''File name:''' | ||
| − | || On doing so, the '''Save File '''window opens. Prompting us to fill in the '''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''' | || In the '''File name: '''field type '''TestData''' | ||
Click on '''Save ''' | Click on '''Save ''' | ||
| − | || In the '''File name '''field, I will type '''TestData '''and click on '''Save '''button. | + | || In the '''File name '''field, I will type '''TestData ''' and click on '''Save '''button. |
|- | |- | ||
|| Point to '''MarcEdit Delimited Text Translator''' | || Point to '''MarcEdit Delimited Text Translator''' | ||
| Line 282: | Line 284: | ||
|- | |- | ||
|| Click on '''Ok ''' | || Click on '''Ok ''' | ||
| − | || At the bottom of this dialog box, click on the ''' | + | || At the bottom of this dialog box, click on the '''OK '''button. |
|- | |- | ||
|| Narration only '''.mrk''' | || Narration only '''.mrk''' | ||
| Line 302: | Line 304: | ||
'''Assignment''' | '''Assignment''' | ||
| − | *Use the codefile extracted in the previous tutorial assignment | + | * Use the codefile extracted in the previous tutorial assignment |
| − | *'''Convert it into .mrk format''' | + | * '''Convert it into .mrk format''' |
|| '''Assignment''' | || '''Assignment''' | ||
As an assignment, | As an assignment, | ||
| − | *Use the codefile extracted in the previous tutorial assignment and | + | * Use the codefile extracted in the previous tutorial assignment and |
| − | *'''Convert it into .mrk format''' | + | * '''Convert it into .mrk format''' |
|- | |- | ||
Revision as of 22:52, 18 January 2026
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
|
In this tutorial, we will learn how to:
|
| Slide Number 3
System Requirement
|
To record this tutorial, I am using
|
| Slide Number 4
Prerequisites
|
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 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 | MarcEdit Delimited Text Translator window opens.
Observe that the selected file appears in the Output File field. |
| Point to Excel Sheet Name: Sheet1 | From the Excel Sheet Name dropdown 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 from 0 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 020$a |
| 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 082$a and click the Add Argument button. |
| Click on Add Argument
Point to Arguments 1 082$a |
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 100$a
In the Indicators field type 1. Note that:
|
| 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 the remaining fields. |
| Point to 245$a and 245$c right click select Join Items | After the mapping is complete combine the common subfields.
For that, select the common tags for example 245$a and 245$c. Right-click and select Join Items, similarly join all the common tags. |
Point to
|
Note that the asterisk symbols appears 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
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-boxes 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:
|
Let’s quickly recap. |
| Slide Number 6
Assignment
|
Assignment
As an assignment,
|
| Slide Number 7
Thank you |
Thank you for joining. |
