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

From Script | Spoken-Tutorial
Jump to: navigation, search
Line 218: Line 218:
 
|-
 
|-
 
| 07:01
 
| 07:01
| Next click on the button '''Add Argument.'''
+
| Next, click on the button '''Add Argument.'''
  
 
On doing so, the value '''0 020$a 0 '''appears in the field under the section '''Arguments.'''
 
On doing so, the value '''0 020$a 0 '''appears in the field under the section '''Arguments.'''
Line 224: Line 224:
 
|-
 
|-
 
| 07:15
 
| 07:15
| Similarly let’s map all the other '''fields'''.  
+
| Similarly, let’s map all the other '''fields'''.  
  
 
|-
 
|-
 
| 07:19
 
| 07:19
|Under the section '''Settings''', Go to '''Select, '''from the drop down select '''Field 1. '''
+
|Under the section '''Settings''', go to '''Select. '''From the drop- down, select '''Field 1. '''
  
 
|-
 
|-
 
| 07:27
 
| 07:27
| In the '''field''' for '''Map To: '''type '''080$a.'''
+
| In the '''field''' for '''Map To, '''type'''080$a.'''
  
 
|-
 
|-
Line 242: Line 242:
 
|-
 
|-
 
| 07:47
 
| 07:47
| Under the tab '''Select, '''from the drop down select '''Field 2.'''
+
| Under the tab '''Select, '''from the drop-down, select '''Field 2.'''
  
 
|-
 
|-
 
| 07:53
 
| 07:53
| In the '''field '''for '''Map To: '''type '''100$a '''
+
| In the field for '''Map To, '''type: '''100$a. '''
  
 
|-
 
|-
 
| 07:59
 
| 07:59
|In the field for '''Indicators: '''type '''1.'''
+
|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’.'''
+
Note that '''1 '''is the first indicator of '''tag 100''' and it represents the '''Surname '''for the '''subfield ‘a’.'''
  
 
|-
 
|-
 
| 08:14
 
| 08:14
| Likewise, complete the mapping of all fields upto '''Field 13 '''as being shown in the drop down under '''Select'''.
+
| Likewise, complete the mapping of all the fields up to '''Field 13 '''as being shown in the drop-down under '''Select'''.
  
 
|-
 
|-
Line 268: Line 268:
 
|-
 
|-
 
| 08:34
 
| 08:34
| Under the section '''Arguments, '''the''' Tags''' that are common with different '''subfields''' need to be joined.
+
| Under the section '''Arguments, '''the''' Tags''' that are common with different '''sub-fields''' need to be joined.
  
 
|-
 
|-
 
| 08:41
 
| 08:41
|For that do the following- Select '''Common Tags''' for example '''245$a''' and '''245$c'''.  
+
|For that, do the following- Select common tags for example '''245$a''' and '''245$c'''.  
  
 
|-
 
|-
 
| 08:52
 
| 08:52
| Then right-click on '''Common Tags''' and from drop down select '''Join Items'''  
+
| Then right-click on common tags and from drop-down select '''Join Items.'''  
  
 
|-
 
|-
 
| 09:00
 
| 09:00
| This will create a grouping of the '''fields''' that are with the same type.
+
| This will create a grouping of the fields that are with the same type.
  
 
|-
 
|-
Line 292: Line 292:
 
|-
 
|-
 
| 09:16
 
| 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'''
+
| 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 fields provided for '''Arguments.'''
  
 
|-
 
|-
 
| 09:34
 
| 09:34
|However I have done mapping manually, so I will not click '''Auto Generate''' option.
+
|However, I have done mapping manually. So, I will not click '''Auto Generate''' option.
  
 
|-
 
|-
Line 304: Line 304:
 
|-
 
|-
 
| 09:44
 
| 09:44
| First one is '''Save Template'''
+
| First one is '''Save Template.'''
  
 
|-
 
|-
Line 316: Line 316:
 
|-
 
|-
 
|10:00
 
|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
+
| If we choose the option '''Save Template,''' then we will be prompted to give it a name and  specify the directory for saving it.
  
 
|-
 
|-
Line 342: Line 342:
 
|-
 
|-
 
| 10:41
 
| 10:41
| Among these- '''Sort Fields''' and '''Calculate common nonfiling data''' are auto-selected by '''MarcEdit 7.'''
+
| Among these, '''Sort Fields''' and '''Calculate common nonfiling data''' are auto-selected by '''MarcEdit 7.'''
  
 
I will leave them as they are.
 
I will leave them as they are.
Line 356: Line 356:
 
|-
 
|-
 
| 11:07
 
| 11:07
| 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.'''
  
 
|-
 
|-
 
| 11:14
 
| 11:14
| 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 in the '''field''' for '''File name: '''I will type '''TestData'''
+
And, in the '''field''' for '''File name, '''I will type '''TestData.'''
  
 
|-
 
|-
 
| 11:27
 
| 11:27
| Now click on '''Save '''button at the bottom of the page.
+
| Now, click on '''Save '''button at the bottom of the page.
  
 
|-
 
|-
Line 372: Line 372:
 
| A pop-up window with the message: '''Process has been finished. Records saved to:'''
 
| A pop-up window with the message: '''Process has been finished. Records saved to:'''
  
'''C:\Users\Spoken\Download\TestData.mrk '''opens
+
'''C:\Users\Spoken\Download\TestData.mrk '''opens.
  
 
|-
 
|-
 
| 11:45
 
| 11:45
| Click on the '''Ok '''button at bottom of this dialog box.
+
| Click on the '''Ok '''button at the bottom of this dialog-box.
  
 
|-
 
|-
 
| 11:50
 
| 11:50
| With this''' .mrk file '''has been successfully saved in the directed location which is the '''Downloads''' folder.
+
| With this,''' .mrk file '''has been successfully saved in the directed location which is the '''Downloads''' folder.
  
 
|-
 
|-
Line 392: Line 392:
 
| A new page '''MarcEditor''' opens.
 
| A new page '''MarcEditor''' opens.
  
On the main '''Menu''' click on '''File''' and from the drop down select '''Open'''.
+
On the main '''Menu,''' click on '''File''' and from the drop- down select '''Open'''.
  
 
|-
 
|-
 
| 12:24
 
| 12:24
| A window, '''Open File''' opens showing''' TestData.mrk '''file.
+
| A window '''Open File''' opens showing''' TestData.mrk '''file.
  
 
|-
 
|-
Line 418: Line 418:
 
|-
 
|-
 
| 12:56
 
| 12:56
|Now, from the drop down select '''Compile File into MARC.'''
+
|Now, from the drop-down select '''Compile File into MARC.'''
  
 
|-
 
|-
 
| 13:02
 
| 13:02
| Another new window '''Save File''', opens.
+
| Another new window '''Save File''' opens.
  
Here locate '''File Name: '''and type the appropriate name in the '''field'''.
+
Here, locate '''File Name: '''and type the appropriate name in the '''field'''.
  
 
|-
 
|-
Line 432: Line 432:
 
|-
 
|-
 
| 13:16
 
| 13:16
| '''Koha '''by default, selects '''MARC Files (*.mrc) '''in the '''field Save as type:'''
+
| '''Koha, '''by default, selects '''MARC Files (*.mrc) '''in the field '''Save as type:'''.
  
 
|-
 
|-
 
| 13:25
 
| 13:25
| Now click on the '''Save '''button located at the bottom of the window.  
+
| Now, click on the '''Save '''button located at the bottom of the window.  
  
 
|-
 
|-
Line 448: Line 448:
 
|-
 
|-
 
| 13:52
 
| 13:52
| With this we have converted '''Excel data '''of our library into''' Marc 21 format '''successfully.
+
| With this, we have converted '''Excel data '''of our library into''' Marc 21 format '''successfully.
  
 
|-
 
|-
 
| 14:00
 
| 14:00
|'''Marc 21 format '''is the standard '''format''' used in '''Koha''' for '''cataloging''' and importing '''data''' into '''Koha'''.
+
|'''Marc 21 format '''is the standard format used in '''Koha''' for cataloging and importing data into '''Koha'''.
  
 
|-
 
|-
 
| 14:09
 
| 14:09
| Now close this window. To do so, go to the 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.
  
 
|-
 
|-
 
| 14:19
 
| 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.
+
| 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
 
| 14:30
| '''Assignment'''
+
| Assignment:
  
 
Prepare a list of 10 '''records''' in '''Excel''' and convert those records into '''MARC '''by using '''MarcEdit 7.'''
 
Prepare a list of 10 '''records''' in '''Excel''' and convert those records into '''MARC '''by using '''MarcEdit 7.'''
Line 476: Line 476:
 
|-
 
|-
 
|  14:47
 
|  14:47
|  The''' Spoken Tutorial Project''' Team conducts workshops and gives certificates.
+
|  The''' Spoken Tutorial Project''' team conducts workshops and gives certificates.
  
 
For more details, please write to us.
 
For more details, please write to us.
Line 486: Line 486:
 
|-
 
|-
 
| 14:59
 
| 14:59
| '''Spoken Tutorial project''' is funded by '''NMEICT, MHRD, Government of India.'''
+
| '''Spoken Tutorial project''' is funded by '''NMEICT, MHRD''', Government of India.
  
 
More information on this mission is available at this link.
 
More information on this mission is available at this link.
Line 492: Line 492:
 
|-
 
|-
 
| 15:09
 
| 15:09
| This is Bella Tony from '''IIT Bombay '''signing off.
+
| This is Bella Tony from '''IIT Bombay, '''signing off.
  
 
Thank you for joining.
 
Thank you for joining.
  
 
|}
 
|}

Revision as of 22:32, 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 the fields up to 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 sub-fields 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 fields 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 the 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