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

From Script | Spoken-Tutorial
Revision as of 16:34, 28 September 2018 by Nancyvarkey (Talk | contribs)

Jump to: navigation, search

Title: Conversion of Excel data to Marc 21 format

Contributor: Bella Tony, IIT Bombay

Reviewed by: Nancy Varkey, Spoken Tutorial Project, IIT Bombay

Keywords:


Visual Cue Narration
Title Slide Welcome to the Spoken Tutorial on conversion of Excel data to Marc 21 format.
Learning Objectives In this tutorial, we will learn to-
  • Convert Excel data to Marc 21 format on a 64-bit Windows machine.
Slide Number 3

System Requirement

To record this tutorial, I am using
  1. Windows 10 Pro
  2. Koha version 16.05
  3. Firefox web browser
Slide 4a

Pre-requisites

To follow this tutorial, learners should be familiar with Library Science.
Slide 4b

Pre-requisites

To practice this tutorial, you should have Koha installed on your system.

You should also have Admin access in Koha.

Pre-requisites Slide
  1. Windows 10, 8 or 7
  2. Any web browser


For eg: Internet Explorer, Firefox or Google Chrome

Before moving ahead, please ensure that you have the following on your machine-
  1. Windows 10, 8 or 7
  2. Any web browser

For eg: Internet Explorer, Firefox or Google Chrome

Point to the shortcut icon on the Desktop Earlier in the same series, we had installed MarcEdit 7 on the Desktop.
Right click on MarcEdit 7 Open the same MarcEdit 7 by double-clicking on the icon.
Point to MarcEdit 7.0.250 By Terry Reese A window named MarcEdit 7.0.250 By Terry Reese, opens.
Click on Export Tab Delimited text Locate and click on the tab Export Tab Delimited Text.
Cursor on: Source File field

Cursor on: icon for a folder

Under the Source File field, locate the icon for a folder.

The source file is an Excel file which we are converting into .mrk format.

Click on this icon for Folder and

browse for the excel file

Click on this icon for the Folder and browse for the Excel file in the field for File name.
Click on the drop down adjacent to File name. Click on the drop down adjacent to File name.
Cursor on:

Microsoft Excel 97/2000/XP/2003 (.xls) then choose the format Excel File(*.xls)

If you have Microsoft Excel 97/2000/XP/2003 (.xls) then choose the format Excel File(*.xls).
Cursor on:

Microsoft Excel 2007/2010/2013 XML(.xlsx)

then choose the format Excel File(*.xlsx)

And if you have, Microsoft Excel 2007/2010/2013 XML(.xlsx) then choose the format Excel File(*.xlsx).
Cursor on:

.(dot)xlsx file

Select, Excel XML File(*.xlsx)

As I have the .(dot)xlsx file, I will select Excel XML File(*.xlsx).
Select Downloads Next, go to the left-side folders and select the folder where your Excel file is saved.

I have selected Downloads because that’s where I saved my Excel file.

Select Test-Data.xlsx So, from the Downloads Folder, I have selected TestData.xlsx.
Cursor on:

field for File name

When the file TestData.xlsx is selected, it appears in the File name field.
Click on Open Now, click on the Open button at the bottom of the window.
Cursor on:

Source File as C:\Users\user\Downloads\Test-Data.xlsx

The same window re-opens with the Source File as C:\Users\spoken\Downloads\TestData.xlsx
Click on the folder icon adjacent to Output File Now click on the folder icon adjacent to the Output File:
Cursor on:

Save File window >> File name:

On doing so, Save File window opens prompting us to fill in the File name:
Click on the Downloads folder


Type the File name: as Test-Data

Click on Save

On the same window, I will click on the Downloads folder located at the left hand side.

And type the File name: as TestData

Now click on Save button at the bottom of the page.

Cursor on the entry:

C:\Users\user\Downloads\Test-Data.mrk in the field for Output File:

The same window appears again.

The Output file field shows C:\Users\spoken\Downloads\TestData.mrk

Cursor on-

Excel Sheet Name:

Note that Excel Sheet Name: Sheet1 gets selected automatically by MarcEdit 7.

However, this sheet name is editable.

Cursor on:

UTF-8 Encoded

Under the Section Options

The check-box UTF-8 Encoded is selected by MarcEdit 7, by default.

Click on Next Click on the Next button at the right side of the same window.
Cursor on:

MarcEdit Delimited Text Translator, opens with the heading-

Data Snapshot

Again a new window, MarcEdit Delimited Text Translator, opens.

The heading says Data Snapshot.

Show screenshot of the Excel file This window will have all the field details as per entries made in the Excel file.
Cursor on:
  • Fields ranging from 0 to 8
We will see the Fields ranging from 0 to 8 and above with their corresponding values.
  • For example, Field 0 has a value of 978-3-319-47238-6 (ISBN) on my machine.
  • You may see a different value as per your Excel sheet.
Cursor on Settings

Cursor on Select

Select Field 0

Under the section DataSnapshot, locate the section Settings.

Go to the tab Select and from the drop down select Field 0.

With this we will be doing the mapping of Excel data with Koha MARC Tags.

SLIDE

Remember:

You can customize Map To: and Indicators.

However, it is important that the Fields and Subfield Codes are as per the Koha MARC Tag.

For more information on MARC Tags visit the link of official Library of Congress site.

http://www.loc.gov/marc/bibliographic/

Remember:

You can customize Map To: and Indicators.

However, it is important that the Fields and Subfield Codes are as per the Koha MARC Tag.

For more information on MARC Tags visit the link of official Library of Congress site.

Screen shot:

Add MARC record without any entry in the fields


(005)

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.

This sequence will change as per your Excel data.

Cursor on:

Indicators: and Term. Punctuation:

I will leave Indicators: and Term. Punctuation: as they are.

However, you may fill these fields as directed by the Koha MARC Tags.

Click in the check box for:

Constant Data

Next is the check box for Constant Data.

Click this if you wish-

  • to map the same information
  • into the datafield for each entry
  • in the delimited text document
Click in the check box for:

Repeatable Subfield

Click Repeatable subfield if you want to repeat the same subfield.
Click on Add Argument

Cursor on:

the value 0 020$a

Cursor on:

the field under the section Arguments

Next click on the button Add Argument.

On doing so, the value 0 020$a 0 appears in the field under the section Arguments.

Cursor on: Select from the drop down select Field 1,


Similarly let’s map all the other fields.

Under the section Settings

Go to Select, from the drop down select Field 1.

Cursor on: Field Map To:

type 080$a

In the field for Map To: type 080$a.
Under the section Arguments

>> Click on Add Argument

Cursor on:

the value 0 080$a

Now, click on the button Add Argument.

On doing so, the value 1 080$a 0 appears in the field under the section Arguments.

Cursor on: Select >> select Field 2 Under the tab Select, from the drop down select Field 2.
Cursor on: field for Map To:

type 100$a

Cursor on: 1

In the field for Map To: type 100$a

In the field for Indicators: type 1.

Note that:

  1. 1 is the first indicator of tag 100
  2. and it represents the Surname for the subfield ‘a’.
Likewise, complete the mapping of all fields upto Field 13 as being shown in the drop down under Select.
Cursor on:


up-down arrows adjacent to the field-

Notice the up and down arrows adjacent to each field.

You may use these to change the sequence of the values that appear.

Select Tags 245$a and 245$c.
  • Then right click on Common Tags and
  • from drop down select Join Items
Under the section Arguments, the Tags that are common with different subfields need to be joined.

For that do the following-

  • Select Common Tags for example 245$a and 245$c.
  • Then right-click on Common Tags and from drop down select Join Items
  • This will create a grouping of the fields that are with same type.
Cursor on:

the * (asterisk)

Note that the * (asterisk symbol) will appear before the selected Tags.

The * asterisk symbol indicates that the common tags are now joined.

Narration only 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

However I have done mapping manually, so I will not click Auto Generate option.

Highlight all 4 options Next we see four options.
Cursor on Save Template- First one is Save Template
Slide
  • Use this if you want to save the same mapping for future use.
  • The saved template will be used if in case you face any problem with data conversion.
Slide If we choose the option Save Template,
  • then, we will be prompted to give it a name and
  • specify the directory for saving it


It will get saved as .mrd file

Cursor on:

Load Template

Access this template in the future by clicking “Load Template” at the right side of the window.
Cursor on Sort Fields Second option is Sort Fields.
Cursor on:

Calculate common nonfiling data

Third option is Calculate common nonfiling data.
Cursor on:

Ignore Header Row

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.

Cursor on:
  • Sort Fields
  • Calculate common nonfiling data
Among these-
  • Sort Fields
  • Calculate common nonfiling data

are auto-selected by MarcEdit 7.


I will leave them as they are.

Click the check-box for Save Template Now, I will check the check-box for Save Template and Ignore Header Row.
Locate and click on the tab Finish on the top right corner of the page. Next, locate and click on the tab Finish on the top right corner of the page.
Cursor on:

Save File window >> File name:

On doing so, Save File window opens, prompting us to fill in the File name:
Click on the Downloads folder

Type the File name: as Test-Data

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

Click on Save Now click on Save button at the bottom of the page.
Cursor on

Process has been finished. Records saved to:

C:\Users\Spoken\Download\TestData.mrk

A pop-up window with the message:

Process has been finished. Records saved to:

C:\Users\Spoken\Download\TestData.mrk opens

Click on the Ok button Click on the Ok button at bottom of this dialog box.
Narration only With this .mrk file has been successfully saved in the directed location which is the Downloads folder.
Cursor on MarcEdit 7.0.250 By Terry Reese

Locate and click on the icon MarcEditor

A new page MarcEdit 7.0.250 By Terry Reese, opens.

Locate and click on the icon MarcEditor.

Cursor on MarcEditor

On the main Menu click on File and from the drop down click on Open.

A new page MarcEditor opens.

On the main Menu click on File and from the drop down select Open.

Cursor on Open File window.


Cursor on TestData.mrk file

A window, Open File opens showing TestData.mrk file.
Click and select the TestData.mrk file Click and select the TestData.mrk file.

It will show in the field for File name.

Cursor on field for File name Now click on Open at the bottom of the window.
Cursor on MarcEditor: TestData.mrk Another window MarcEditor: TestData.mrk opens with all the details.
Click on the file >> Select Compile File into MARC On the same window, from the main menu, click on the File.

Now, from the drop down select Compile File into MARC.

Cursor on Save File

Locate File Name >> type TestData

Another new window Save File, opens.

Here locate File Name: and type the appropriate name in the field.

I will type TestData.

Cursor on MARC Files (*.mrc) in the field Save as type: Koha by default, selects MARC Files (*.mrc) in the field Save as type:
Click on the Save Now click on the Save button located at the bottom of the window.
Cursor on:

5 records processed in 0.008517 seconds.

Upon doing so, on the same window at the bottom, you will see 5 records processed in 0.166228 seconds.


This is because I imported only 5 records.

You will see a different number of records and processed time as per your data.

Narration only

Show screenshot of saved file

With this we have converted Excel data of our library into Marc 21 format successfully.


Marc 21 format is the standard format used in Koha for cataloging and importing data into Koha.

Close the window

To do so, go to top right corner and click on the close button

Now close this window.

To do so, go to the top right corner and click on the Close button.

Summary:

In this tutorial, we have learnt to-

  • Convert Excel data to Marc 21 format
Let us summarize.

In this tutorial, we have learnt to-

  • Convert Excel data to Marc 21 format on a 64-bit Windows machine.
Assignment

Prepare a list of 10 records in excel and convert those records into MARC by using MarcEdit 7

Assignment

Prepare a list of 10 records in Excel and convert those records into MARC by using MarcEdit 7.

Slide:

About Spoken Tutorial project


The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

Slide:

Spoken Tutorial workshops

The Spoken Tutorial Project Team

  • Conducts workshops using spoken tutorials
  • Gives certificates for those who pass an online test
  • For more details, please write to [about:blank http://contact@spoken-tutorial.org]


The Spoken Tutorial Project Team conducts workshops and gives certificates.

For more details, please write to us.

Slide:

Forum for specific questions

Please post your timed queries in this forum.
Slide:

Acknowledgement

  • Spoken Tutorial Project is a part of the Talk to a Teacher project
  • It is supported by the National Mission on Education through ICT, MHRD, Government of India
  • More information on this Mission is available at
  • http://spoken-tutorial.org/NMEICT-Intro
Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

This is Bella Tony from IIT Bombay signing off.

Thank you for joining.

Contributors and Content Editors

Nancyvarkey, PoojaMoolya

Retrieved from "https://script.spoken-tutorial.org/index.php?title=Koha-Library-Management-System/C3/Convert-Excel-to-MARC/English&oldid=44614"