Difference between revisions of "Koha-Library-Management-System/C3/Convert-Excel-to-MARC/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with " {| border=1 |Time |Narration |- | 00:02 |Welcome to the '''Spoken Tutorial '''on conversion of''' Excel data '''to''' Marc 21 format.''' |- | 00:09 |In this tutorial, we wi...")
 
Line 10: Line 10:
 
|-
 
|-
 
| 00:09
 
| 00:09
−
|In this tutorial, we will learn to- Convert '''Excel data '''to''' Marc 21 format '''on a '''64-bit Windows '''machine.
+
|In this tutorial, we will learn to convert '''Excel data '''to''' Marc 21 format '''on a '''64-bit Windows '''machine.
  
 
|-
 
|-
 
| 00:19
 
| 00:19
−
|To record this tutorial, I am using '''Windows 10 Pro'''
+
|To record this tutorial, I am using:
−
 
+
'''Windows 10 Pro''' and 
−
And  '''Firefox web browser'''
+
'''Firefox web browser.'''
  
 
|-
 
|-
 
| 00:27
 
| 00:27
−
|To follow this tutorial, learners should be familiar with '''Library Science.'''
+
|To follow this tutorial, learners should be familiar with Library Science.
  
 
|-
 
|-
Line 26: Line 26:
 
|Before moving ahead, please ensure that you have the following on your machine-
 
|Before moving ahead, please ensure that you have the following on your machine-
  
−
'''Windows 10, 8 '''or''' 7'''
+
'''Windows 10, 8 '''or''' 7''',
  
 
|-
 
|-
 
| 00:45
 
| 00:45
−
| Any web browser. For eg: '''Internet Explorer, Firefox''' or '''Google Chrome'''
+
| any web browser. For eg: '''Internet Explorer, Firefox''' or '''Google Chrome.'''
  
 
|-
 
|-
Line 42: Line 42:
 
|-
 
|-
 
| 01:07
 
| 01:07
−
| A window named '''MarcEdit 7.0.250 By Terry Reese, '''opens.
+
| A window named '''MarcEdit 7.0.250 By Terry Reese '''opens.
  
 
|-
 
|-
Line 50: Line 50:
 
|-
 
|-
 
| 01:21
 
| 01:21
−
| Under the '''Source File field''', locate the icon for a '''folder.'''
+
| Under the '''Source File''' field, locate the icon for a '''folder.'''
  
 
|-
 
|-
 
| 01:29
 
| 01:29
−
|The '''source file''' is an '''Excel file''' which we are converting into '''.mrk format.'''
+
|The '''source file''' is an '''Excel file''' which we are converting into '''.mrk''' format.
  
 
|-
 
|-
 
| 01:36
 
| 01:36
−
| Click on this icon for the '''Folder''' and browse for the '''Excel file''' in the '''field''' for '''File name'''.
+
| Click on this icon for the '''Folder''' and browse for the '''Excel''' file in the '''field''' for '''File name'''.
  
 
|-
 
|-
 
| 01:45
 
| 01:45
−
| Click on the drop down adjacent to '''File name.'''
+
| Click on the drop-down adjacent to '''File name.'''
  
 
|-
 
|-
Line 74: Line 74:
 
|-
 
|-
 
| 02:23
 
| 02:23
−
| As I have the '''.(dot)xlsx file, '''I will select '''Excel XML File(*.xlsx).'''
+
| As I have the '''.(dot)xlsx ''' file, I will select '''Excel XML File(*.xlsx).'''
  
 
|-
 
|-
Line 86: Line 86:
 
|-
 
|-
 
| 02:47
 
| 02:47
−
| So, from the '''Downloads''' Folder, I have selected '''TestData.xlsx.'''
+
| So, from the '''Downloads''' folder, I have selected '''TestData.xlsx.'''
  
 
|-
 
|-
 
| 02:54
 
| 02:54
−
| When the file '''TestData.xlsx '''is selected, it appears in the '''File name field'''.
+
| When the file '''TestData.xlsx '''is selected, it appears in the '''File name''' field.
  
 
|-
 
|-
Line 98: Line 98:
 
|-
 
|-
 
| 03:09
 
| 03:09
−
| The same window re-opens with the '''Source File '''as '''C:\Users\spoken\Downloads\TestData.xlsx'''
+
| The same window re-opens with the '''Source File '''as '''C:\Users\spoken\Downloads\TestData.xlsx'''.
  
 
|-
 
|-
 
| 03:21
 
| 03:21
−
| Now click on the folder icon adjacent to the '''Output File:'''
+
| Now, click on the folder icon adjacent to the '''Output File.'''
  
 
|-
 
|-
 
| 03:27
 
| 03:27
−
| On doing so, '''Save File '''window opens prompting us to fill in the '''File name:'''
+
| On doing so, '''Save File '''window opens prompting us to fill in the '''File name.'''
  
 
|-
 
|-
Line 112: Line 112:
 
| On the same window, I will click on the '''Downloads '''folder located at the left hand side.
 
| On the same window, I will click on the '''Downloads '''folder located at the left hand side.
  
−
And type the '''File name: '''as '''TestData'''
+
And, type the '''File name: '''as '''TestData'''.
  
 
|-
 
|-
Line 122: Line 122:
 
| The same window appears again.
 
| The same window appears again.
  
−
The '''Output file field''' shows '''C:\Users\spoken\Downloads\TestData.mrk'''
+
The '''Output file''' field shows: '''C:\Users\spoken\Downloads\TestData.mrk'''.
  
 
|-
 
|-
Line 128: Line 128:
 
| Note that '''Excel Sheet Name: Sheet1 '''gets selected automatically by '''MarcEdit 7.'''
 
| Note that '''Excel Sheet Name: Sheet1 '''gets selected automatically by '''MarcEdit 7.'''
  
−
However, this sheet name is editable.
+
However, this '''sheet''' name is editable.
  
 
|-
 
|-
 
| 04:17
 
| 04:17
−
| Under the Section '''Options'''
+
| Under the Section '''Options,''' the check-box''' UTF-8 Encoded''' is selected by '''MarcEdit 7, '''by default.
−
 
+
−
The check-box''' UTF-8 Encoded''' is selected by '''MarcEdit 7, '''by default.
+
  
 
|-
 
|-
Line 142: Line 140:
 
|-
 
|-
 
| 04:36
 
| 04:36
−
| Again a new window, '''MarcEdit Delimited Text Translator, '''opens.
+
| Again a new window, '''MarcEdit Delimited Text Translator '''opens.
  
 
The heading says '''Data Snapshot.'''
 
The heading says '''Data Snapshot.'''
Line 168: Line 166:
 
|-
 
|-
 
| 05:26
 
| 05:26
−
|Go to the tab '''Select '''and from the drop down select '''Field 0.'''
+
|Go to the tab '''Select '''and from the drop-down select '''Field 0.'''
  
 
|-
 
|-
 
| 05:34
 
| 05:34
−
|With this we will be doing the mapping of '''Excel data''' with '''Koha MARC Tags.'''
+
|With this we will be doing the '''mapping''' of '''Excel data''' with '''Koha MARC Tags.'''
  
 
|-
 
|-
 
| 05:41
 
| 05:41
−
| Remember: You can customize '''Map To: '''and '''Indicators.'''
+
| Remember, you can customize '''Map To: '''and '''Indicators.'''
  
 
|-
 
|-
Line 184: Line 182:
 
|-
 
|-
 
| 05:55
 
| 05:55
−
|For more information on '''MARC Tags '''visit the link of official '''Library of Congress''' site.
+
|For more information on '''MARC Tags, '''visit the link of official '''Library of Congress''' site.
  
 
|-
 
|-
 
| 06:05
 
| 06:05
−
|On the browser type this URL and click on search
+
|On the browser, type this URL and click on '''search'''.
  
 
|-
 
|-
 
| 06:12
 
| 06:12
−
| Recall that- '''Map To: '''values entered in the '''field,''' are referred from an earlier tutorial in this series.
+
| 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'''.
+
So, I will enter '''020$a '''in the '''Map To:''' field.
  
 
|-
 
|-
Line 204: Line 202:
 
| I will leave '''Indicators: '''and '''Term. Punctuation: '''as they are.
 
| I will leave '''Indicators: '''and '''Term. Punctuation: '''as they are.
  
−
However, you may fill these''' fields''' as directed by the '''Koha MARC Tags.'''
+
However, you may fill these fields as directed by the '''Koha MARC Tags.'''
  
 
|-
 
|-
 
| 06:41
 
| 06:41
−
| Next is the check box for '''Constant Data.'''
+
| Next is the check-box for '''Constant Data.'''
  
 
|-
 
|-
 
| 06:44
 
| 06:44
−
|Click this if you wish- to map the same information  into the datafield for each entry in the delimited text document
+
|Click this if you wish to '''map''' the same information  into the data field for each entry, in the delimited text document.
  
 
|-
 
|-

Revision as of 21:53, 20 October 2018

Time Narration
00:02 Welcome to the Spoken Tutorial on conversion of Excel data to Marc 21 format.
00:09 In this tutorial, we will learn to convert Excel data to Marc 21 format on a 64-bit Windows machine.
00:19 To record this tutorial, I am using:

Windows 10 Pro and Firefox web browser.

00:27 To follow this tutorial, learners should be familiar with Library Science.
00:34 Before moving ahead, please ensure that you have the following on your machine-

Windows 10, 8 or 7,

00:45 any web browser. For eg: Internet Explorer, Firefox or Google Chrome.
00:54 Earlier in the same series, we had installed MarcEdit 7 on the Desktop.
01:01 Open the same MarcEdit 7 by double-clicking on the icon.
01:07 A window named MarcEdit 7.0.250 By Terry Reese opens.
01:16 Locate and click on the tab Export Tab Delimited Text.
01:21 Under the Source File field, locate the icon for a folder.
01:29 The source file is an Excel file which we are converting into .mrk format.
01:36 Click on this icon for the Folder and browse for the Excel file in the field for File name.
01:45 Click on the drop-down adjacent to File name.
01:49 If you have Microsoft Excel 97/2000/XP/2003 (.xls) then choose the format Excel File(*.xls).
02:05 And if you have, Microsoft Excel 2007/2010/2013 XML(.xlsx) then choose the format Excel File(*.xlsx).
02:23 As I have the .(dot)xlsx file, I will select Excel XML File(*.xlsx).
02:33 Next, go to the left-side folders and select the folder where your Excel file is saved.
02:41 I have selected Downloads because that’s where I saved my Excel file.
02:47 So, from the Downloads folder, I have selected TestData.xlsx.
02:54 When the file TestData.xlsx is selected, it appears in the File name field.
03:04 Now, click on the Open button at the bottom of the window.
03:09 The same window re-opens with the Source File as C:\Users\spoken\Downloads\TestData.xlsx.
03:21 Now, click on the folder icon adjacent to the Output File.
03:27 On doing so, Save File window opens prompting us to fill in the File name.
03:34 On the same window, I will click on the Downloads folder located at the left hand side.

And, type the File name: as TestData.

03:45 Now click on Save button at the bottom of the page.
03:50 The same window appears again.

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

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

However, this sheet name is editable.

04:17 Under the Section Options, the check-box UTF-8 Encoded is selected by MarcEdit 7, by default.
04:31 Click on the Next button at the right side of the same window.
04:36 Again a new window, MarcEdit Delimited Text Translator opens.

The heading says Data Snapshot.

04:47 This window will have all the field details as per entries made in the Excel file.
04:53 We will see the Fields ranging from 0 to 8 and above with their corresponding values.
05:02 For example, Field 0 has a value of 978-3-319-47238-6 (ISBN) on my machine.
05:15 You may see a different value as per your Excel sheet.
05:21 Under the section DataSnapshot, locate the section Settings.
05:26 Go to the tab Select and from the drop-down select Field 0.
05:34 With this we will be doing the mapping of Excel data with Koha MARC Tags.
05:41 Remember, you can customize Map To: and Indicators.
05:46 However, it is important that the Fields and Subfield Codes are as per the Koha MARC Tag.
05:55 For more information on MARC Tags, visit the link of official Library of Congress site.
06:05 On the browser, type this URL and click on search.
06:12 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.

06:26 This sequence will change as per your Excel data.
06:30 I will leave Indicators: and Term. Punctuation: as they are.

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

06:41 Next is the check-box for Constant Data.
06:44 Click this if you wish to map the same information into the data field for each entry, in the delimited text document.
06:55 Click Repeatable subfield if you want to repeat the same subfield.
07:01 Next click on the button Add Argument.

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

07:15 Similarly let’s map all the other fields.
07:19 Under the section Settings, Go to Select, from the drop down select Field 1.
07:27 In the field for Map To: type 080$a.
07:32 Now, click on the button Add Argument.

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

07:47 Under the tab Select, from the drop down select Field 2.
07:53 In the field for Map To: type 100$a
07:59 In the field for Indicators: type 1.

Note that: 1 is the first indicator of tag 100 and it represents the Surname for the subfield ‘a’.

08:14 Likewise, complete the mapping of all fields upto Field 13 as being shown in the drop down under Select.
08:24 Notice the up and down arrows adjacent to each field.
08:28 You may use these to change the sequence of the values that appear.
08:34 Under the section Arguments, the Tags that are common with different subfields need to be joined.
08:41 For that do the following- Select Common Tags for example 245$a and 245$c.
08:52 Then right-click on Common Tags and from drop down select Join Items
09:00 This will create a grouping of the fields that are with the same type.
09:05 Note that the * (asterisk symbol) will appear before the selected Tags.
09:10 The * asterisk symbol indicates that the common tags are now joined.
09:16 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
09:34 However I have done mapping manually, so I will not click Auto Generate option.
09:41 Next we see four options.
09:44 First one is Save Template
09:47 Use this if you want to save the same mapping for future use.
09:52 The saved template will be used if in case you face any problem with data conversion.
10:00 If we choose the option Save Template, then, we will be prompted to give it a name and specify the directory for saving it
10:10 It will get saved as .mrd file
10:15 Access this template in the future by clicking “Load Template” at the right side of the window.
10:22 Second option is Sort Fields.
10:26 Third option is Calculate common nonfiling data.
10:31 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.

10:41 Among these- Sort Fields and Calculate common nonfiling data are auto-selected by MarcEdit 7.

I will leave them as they are.

10:53 Now, I will check the check-box for Save Template and Ignore Header Row.
11:00 Next, locate and click on the tab Finish on the top right corner of the page.
11:07 On doing so, Save File window opens, prompting us to fill in the File name:
11:14 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

11:27 Now click on Save button at the bottom of the page.
11:32 A pop-up window with the message: Process has been finished. Records saved to:

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

11:45 Click on the Ok button at bottom of this dialog box.
11:50 With this .mrk file has been successfully saved in the directed location which is the Downloads folder.
11:59 A new page MarcEdit 7.0.250 By Terry Reese, opens.

Locate and click on the icon MarcEditor.

12:11 A new page MarcEditor opens.

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

12:24 A window, Open File opens showing TestData.mrk file.
12:30 Click and select the TestData.mrk file.

It will show in the field for File name.

12:39 Now click on Open at the bottom of the window.
12:44 Another window MarcEditor: TestData.mrk opens with all the details.
12:51 On the same window, from the main menu, click on the File.
12:56 Now, from the drop down select Compile File into MARC.
13:02 Another new window Save File, opens.

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

13:11 I will type TestData.
13:16 Koha by default, selects MARC Files (*.mrc) in the field Save as type:
13:25 Now click on the Save button located at the bottom of the window.
13:30 Upon doing so, on the same window at the bottom, you will see 5 records processed in 0.166228 seconds.
13:42 This is because I imported only 5 records. You will see a different number of records and processed time as per your data.
13:52 With this we have converted Excel data of our library into Marc 21 format successfully.
14:00 Marc 21 format is the standard format used in Koha for cataloging and importing data into Koha.
14:09 Now close this window. To do so, go to the top right corner and click on the Close button.
14:19 Let us summarize. In this tutorial, we have learnt to- Convert Excel data to Marc 21 format on a 64-bit Windows machine.
14:30 Assignment

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

14:41 The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

14:47 The Spoken Tutorial Project Team conducts workshops and gives certificates.

For more details, please write to us.

14:55 Please post your timed queries in this forum.
14:59 Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

15:09 This is Bella Tony from IIT Bombay signing off.

Thank you for joining.

Contributors and Content Editors

Nancyvarkey, PoojaMoolya, Pratik kamble, Sandhya.np14

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