Difference between revisions of "Koha-Library-Software/C4/Convert-Excel-Data-to-MARC-Format/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
(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...")
 
 
(One intermediate revision by the same user not shown)
Line 5: Line 5:
 
'''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.
 
'''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
+
'''Prerequisite:''' Extract Excel to Marc Codefile'''
 +
 
  
 
{|border=1
 
{|border=1
Line 32: Line 33:
 
|| 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''' '''7.7 '''
+
* '''MARC Edit 7.7 ''' and
 
* A working internet connection.
 
* A working internet connection.
  
Line 75: Line 76:
  
 
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 and select '''Excel XML File''' format.  
+
|| 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 106:
  
 
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'''
−
|| Also note that the '''Excel Sheet Name '''as '''Sheet1 '''gets selected automatically.
+
|| From the '''Excel Sheet Name ''' dropdown '''Sheet1 '''gets selected automatically.
  
 
This sheet name is editable.
 
This sheet name is editable.
Line 136: Line 139:
 
|-
 
|-
 
|| Point to '''Data Snapshot '''table
 
|| Point to '''Data Snapshot '''table
−
|| We can see the '''Fields''' ranging from0 to 13 fields are their with corresponding value'''0 '''to''' 13''' with their corresponding values.
+
|| We can see the '''Fields''' ranging from 0 to 13 with their corresponding values.
 
|-
 
|-
 
|| Point to '''Settings'''
 
|| Point to '''Settings'''
Line 150: Line 153:
 
|| Please note you can also customize the '''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'''.
+
It is important that the '''Fields '''and the '''Subfield Codes '''are as per the '''Koha MARC Tag'''.
 
|-
 
|-
 
|| Point to '''Map To: '''field type '''020$a'''
 
|| Point to '''Map To: '''field type '''020$a'''
−
|| In the '''Map To field '''I will enter the following value.
+
|| 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 164:
  
 
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 173:
  
 
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 183:
 
|-
 
|-
 
|| In '''Map To '''field type '''082$a'''
 
|| In '''Map To '''field type '''082$a'''
−
|| In the '''Map To '''field type following value and click on the '''Add Argument '''button.
+
|| 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 0'''
+
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 200:
  
 
Narration only
 
Narration only
−
|| In the '''Map To '''field type following value.
+
|| 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 230:
  
 
'''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 completed combine the common subfields.
  
−
To do so, select common tags for example '''245$a''' and '''245$c'''.
+
For that, select the common tags for example '''245$a''' and '''245$c'''.
  
−
Right-click and select '''Join Items''', likewise join all the common tags.
+
Right-click and select '''Join Items''', similarly join all the common tags.
 
|-
 
|-
 
|| Point to '''
 
|| Point to '''
 
* '''symbol
 
* '''symbol
−
|| Note that the asterisk symbol will appear before the selected '''Tags'''.
+
|| 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 256: Line 257:
 
|| We can also see the following four options.
 
|| We can also see the following four options.
  
−
Among these '''Sort Fields '''and '''Calculate common nonfiling data '''are auto-selected.
+
Among these '''Sort Fields ''' and '''Calculate common nonfiling data ''' are auto-selected.
  
 
We will leave them as they are.
 
We will leave them as they are.
  
−
Next check the check-box for '''Save Template '''and''' Ignore Header Row'''.
+
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 the '''Save '''button.
 
|-
 
|-
 
|| Point to '''MarcEdit Delimited Text Translator'''
 
|| Point to '''MarcEdit Delimited Text Translator'''
Line 282: Line 285:
 
|-
 
|-
 
|| Click on '''Ok '''
 
|| Click on '''Ok '''
−
|| At the bottom of this dialog box, click on the '''Ok '''button.
+
|| At the bottom of this dialog box, click on the '''OK '''button.
 
|-
 
|-
 
|| Narration only '''.mrk'''
 
|| Narration only '''.mrk'''
Line 302: Line 305:
  
 
'''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'''
  
 
|-
 
|-

Latest revision as of 23:02, 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

  • 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 and
  • 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 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 the 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:

  • 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 the remaining fields.
Point to 245$a and 245$c right click select Join Items After the mapping is completed 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
  • symbol
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
  • 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-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 the 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

Madhurig, Mayahans

Retrieved from "https://script.spoken-tutorial.org/index.php?title=Koha-Library-Software/C4/Convert-Excel-Data-to-MARC-Format/English&oldid=57377"