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

Madhurig at 17:32, 18 January 2026

2026-01-18T17:32:10Z

Madhurig at 17:22, 18 January 2026

2026-01-18T17:22:33Z

Mayahans: 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..."

2025-12-15T10:20:10Z

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..."

New page

'''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

{|border=1
|-
|| '''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 value'''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 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.
|-
|}

@@ 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.
-'''Prerequisite:''' Extract Excel to Marc Codefile
+'''Prerequisite:''' Extract Excel to Marc Codefile'''
 {|border=1
@@ Line 152: / Line 153: @@
 || 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'''
@@ Line 232: / Line 233: @@
 |-
 || Point to '''245$a''' and '''245$c '''right click select '''Join Items'''
-|| After the mapping is complete combine the common subfields.
+|| After the mapping is completed combine the common subfields.
 For that, select the common tags for example '''245$a''' and '''245$c'''.
@@ Line 256: / Line 257: @@
 || 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.
@@ Line 273: / Line 274: @@
 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'''

@@ Line 32: / Line 32: @@
 || To record this tutorial, I am using
 * '''Windows 11 Pro Operating System'''
-* '''MARC Edit''' '''7.7 '''
+* '''MARC Edit 7.7 ''' and
 * A working internet connection.
@@ Line 75: / Line 75: @@
 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'''.
@@ Line 105: / Line 105: @@
 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'''
-|| Observe that the selected file appears in the '''Output File '''field.
+||'''MarcEdit Delimited Text Translator''' window opens.
 |-
 || 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.
@@ Line 136: / Line 138: @@
 |-
 || 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'''
@@ Line 153: / Line 155: @@
 |-
 || 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:'''
@@ Line 161: / Line 163: @@
 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.
@@ Line 170: / Line 172: @@
 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.
@@ Line 182: / Line 182: @@
 |-
 || 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'''
-Point to '''Arguments 1 082$a 0'''
+Point to '''Arguments 1 082$a  '''
 || The field''' '''value appears in the '''Arguments '''textbox.
 |-
@@ Line 199: / Line 199: @@
 Narration only
-|| In the '''Map To '''field type following value.
+|| In the '''Map To '''field type '''100$a'''
 In the '''Indicators''' field type '''1'''.
 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’'''.
@@ Line 229: / Line 229: @@
 '''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'''
-|| After mapping is complete combine the common subfields.
+|| After the mapping is complete 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 '''
 * '''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.
@@ Line 260: / Line 260: @@
 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'''
-|| 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:'''
-|| On doing so, the '''Save File '''window opens. Prompting us to fill in the '''File name'''.
+|| On doing so, the '''Save File '''window opens.
 |-
 || In the '''File name: '''field type '''TestData'''
 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'''
@@ Line 282: / Line 284: @@
 |-
 || 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'''
@@ Line 302: / Line 304: @@
 '''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'''
 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'''
 |-