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

From Script | Spoken-Tutorial
Jump to: navigation, search
Line 1: Line 1:
  
{| border=1
+
{| border=1  
|Time
+
|Time  
|Narration
+
|Narration  
  
|-
+
|-  
| 00:02
+
| 00:01
|Welcome to the '''Spoken Tutorial '''on conversion of''' Excel data '''to''' Marc 21 format.'''
+
|Welcome to the '''Spoken Tutorial '''on conversion of''' Excel data '''to''' Marc 21 format.'''  
  
|-
+
|-  
| 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:
+
|To record this tutorial, I am using: '''Windows 10 Pro''' and   
'''Windows 10 Pro''' and   
+
'''Firefox web browser.'''
+
  
|-
+
'''Firefox web browser.'''
| 00:27
+
|To follow this tutorial, learners should be familiar with Library Science.
+
  
|-
+
|-  
|00:34
+
| 00:29
|Before moving ahead, please ensure that you have the following on your machine-
+
|To follow this tutorial, learners should be familiar with Library Science.
  
'''Windows 10, 8 '''or''' 7''',
+
|-
 +
|00:35
 +
|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
+
| 00:45
| Earlier in the same series, we had installed '''MarcEdit 7 '''on the '''Desktop.'''
+
| Any web browser. For eg: '''Internet Explorer, Firefox''' or '''Google Chrome.'''  
  
|-
+
|-  
| 01:01
+
| 00:53
| Open the same '''MarcEdit 7 '''by double-clicking on the icon.
+
| Earlier in the same series, we had installed '''MarcEdit 7 '''on the '''Desktop.'''
  
|-
+
|-  
| 01:07
+
| 01:00
| A window named '''MarcEdit 7.0.250 By Terry Reese '''opens.
+
| Open the same '''MarcEdit 7 '''by double-clicking on the icon.  
  
|-
+
|-  
| 01:16
+
| 01:07
| Locate and click on the tab '''Export Tab Delimited Text.'''
+
| A window named '''MarcEdit 7.0.250 By Terry Reese '''opens.
  
|-
+
|-  
| 01:21
+
| 01:15
| Under the '''Source File''' field, locate the icon for a '''folder.'''
+
| Locate and click on the tab '''Export Tab Delimited Text.'''  
  
|-
+
|-  
| 01:29
+
| 01:21
|The '''source file''' is an '''Excel file''' which we are converting into '''.mrk''' format.
+
| Under the '''Source File''' field, locate the icon for a '''folder.'''  
  
|-
+
|-  
| 01:36
+
| 01:27
| Click on this icon for the '''Folder''' and browse for the '''Excel''' file in the '''field''' for '''File name'''.
+
|The '''source file''' is an '''Excel file''' which we are converting into '''.mrk''' format.  
  
|-
+
|-  
| 01:45
+
| 01:34
| Click on the drop-down adjacent to '''File name.'''
+
| Click on this icon for the '''Folder''' and browse for the '''Excel''' file in the '''field''' for '''File name'''.
  
|-
+
|-  
| 01:49
+
| 01:42
| If you have''' Microsoft Excel 97/2000/XP/2003 (.xls) '''then choose the format '''Excel File(*.xls).'''
+
| Click on the drop-down adjacent to '''File name.'''  
  
|-
+
|-  
|02:05
+
| 01:46
| And if you have, '''Microsoft Excel 2007/2010/2013 XML(.xlsx) '''then choose the format '''Excel File(*.xlsx).'''
+
| If you have''' Microsoft Excel 97/2000/XP/2003 (.xls) '''then choose the format '''Excel File(*.xls).'''  
  
|-
+
|-  
| 02:23
+
|02:03
| As I have the '''.(dot)xlsx ''' file, I will select '''Excel XML File(*.xlsx).'''
+
| And if you have, '''Microsoft Excel 2007/2010/2013 XML(.xlsx) '''then choose the format '''Excel File(*.xlsx).'''  
  
|-
+
|-  
| 02:33
+
| 02:21
| Next, go to the left-side folders and select the folder where your '''Excel file '''is saved.
+
| As I have the '''.(dot)xlsx ''' file, I will select '''Excel XML File(*.xlsx).'''
  
|-
+
|-  
| 02:41
+
| 02:32
|I have selected '''Downloads '''because that’s where I saved my '''Excel file'''.
+
| Next, go to the left-side folders and select the folder where your '''Excel file '''is saved.  
  
|-
+
|-  
| 02:47
+
| 02:40
| So, from the '''Downloads''' folder, I have selected '''TestData.xlsx.'''
+
|I have selected '''Downloads '''because that’s where I saved my '''Excel file'''.
  
|-
+
|-  
| 02:54
+
| 02:47
| When the file '''TestData.xlsx '''is selected, it appears in the '''File name''' field.
+
| So, from the '''Downloads''' folder, I have selected '''TestData.xlsx.'''  
  
|-
+
|-  
| 03:04
+
| 02:55
| Now, click on the '''Open '''button at the bottom of the window.
+
| When the file '''TestData.xlsx '''is selected, it appears in the '''File name''' field.  
  
|-
+
|-  
| 03:09
+
| 03:04
| The same window re-opens with the '''Source File '''as '''C:\Users\spoken\Downloads\TestData.xlsx'''.
+
| Now, click on the '''Open '''button at the bottom of the window.  
  
|-
+
|-  
| 03:21
+
| 03:09
| Now, click on the folder icon adjacent to the '''Output File.'''
+
| The same window re-opens with the '''Source File '''as  '''C:\Users\spoken\Downloads\TestData.xlsx'''.
  
|-
+
|-  
| 03:27
+
| 03:21
| On doing so, '''Save File '''window opens prompting us to fill in the '''File name.'''
+
| Now, click on the folder icon adjacent to the '''Output File.'''  
  
|-
+
|-  
| 03:34
+
| 03:27
| On the same window, I will click on the '''Downloads '''folder located at the left hand side.
+
| On doing so, '''Save File '''window opens prompting us to fill in the '''File name.'''
  
And, type the '''File name: '''as '''TestData'''.
+
|-
 +
| 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
+
| 03:46
| The same window appears again.
+
|Now click on '''Save '''button at the bottom of the page.  
  
The '''Output file''' field shows: '''C:\Users\spoken\Downloads\TestData.mrk'''.
+
|-
 +
| 03:51
 +
| 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:06
 +
| 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
+
| 04:20
| Click on the '''Next '''button at the right side of the same window.
+
| Under the Section '''Options,''' the check-box''' UTF-8 Encoded''' is selected by '''MarcEdit 7, '''by default.  
  
|-
+
|-  
| 04:36
+
| 04:32
| Again a new window, '''MarcEdit Delimited Text Translator '''opens.
+
| Click on the '''Next '''button at the right side of the same window.  
  
The heading says '''Data Snapshot.'''
+
|-
 +
| 04:37
 +
| 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
+
| 04:48
| We will see the '''Fields''' ranging from '''0 '''to''' 8''' and above with their corresponding values.
+
| This window will have all the '''field''' details as per entries made in the '''Excel file.'''  
  
|-
+
|-
| 05:02
+
| 04:55
| For example, '''Field 0 '''has a value of '''978-3-319-47238-6 (ISBN)''' on my machine.
+
| We will see the '''Fields''' ranging from '''0 '''to''' 8''' and above with their corresponding values.
 +
 +
|-  
 +
| 05:03
 +
| For example, '''Field 0 '''has a value of '''978-3-319-47238-6 (ISBN)''' on my machine.  
  
|-
+
|-  
| 05:15
+
| 05:17
| You may see a different value as per your '''Excel sheet.'''
+
| You may see a different value as per your '''Excel sheet.'''  
  
|-
+
|-  
| 05:21
+
| 05:22
| Under the section''' DataSnapshot, '''locate the section '''Settings.'''
+
| Under the section''' DataSnapshot, '''locate the section '''Settings.'''  
  
|-
+
|-  
| 05:26
+
| 05:28
|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:35
|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:43
| Remember, you can customize '''Map To: '''and '''Indicators.'''
+
| Remember, you can customize '''Map To: '''and '''Indicators.'''  
  
|-
+
|-  
| 05:46
+
| 05:49
|However, it is important that the '''Fields '''and '''Subfield Codes '''are as per the '''Koha MARC Tag.'''
+
|However, it is important that the '''Fields '''and '''Subfield Codes '''are as per the '''Koha MARC Tag.'''  
  
|-
+
|-  
| 05:55
+
| 05:58
|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:07
|On the browser, type this URL and click on '''search'''.
+
|On the browser, type this URL and click on '''search'''.  
  
|-
+
|-  
| 06:12
+
| 06:15
| 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.  
  
|-
+
|-  
| 06:26
+
| 06:31
|This sequence will change as per your '''Excel data'''.
+
|This sequence will change as per your '''Excel data'''.  
  
|-
+
|-  
|06:30
+
|06:36
| 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.'''
+
|-
 +
| 06:42
 +
| However, you may fill these fields as directed by the '''Koha MARC Tags.'''  
  
|-
+
|-  
| 06:41
+
| 06:49
| Next is the check-box for '''Constant Data.'''
+
| Next is the check-box for '''Constant Data.'''  
  
|-
+
|-  
| 06:44
+
| 06:54
|Click this if you wish to '''map''' the same information  into the data field 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.  
  
|-
+
|-  
| 06:55
+
| 07:04
| Click '''Repeatable subfield '''if you want to repeat the same '''subfield'''.
+
| Click '''Repeatable subfield '''if you want to repeat the same '''subfield'''.  
  
|-
+
|-  
| 07:01
+
| 07:10
| 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.'''  
  
|-
+
|-  
| 07:15
+
| 07:25
 
| Similarly, let’s map all the other '''fields'''.  
 
| Similarly, let’s map all the other '''fields'''.  
  
|-
+
|-  
| 07:19
+
| 07:31
|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:39
| In the '''field''' for '''Map To, '''type:  '''080$a.'''
+
| In the '''field''' for '''Map To, '''type:  '''080$a.'''  
  
|-
+
|-  
| 07:32
+
| 07:46
| Now, click on the button''' Add Argument.'''
+
| Now, click on the button''' Add Argument.'''  
  
On doing so, the value '''1 080$a 0 '''appears in the field under the section '''Arguments.'''
+
On doing so, the value '''1 080$a 0 '''appears in the field under the section '''Arguments.'''  
  
|-
+
|-  
| 07:47
+
| 08:01
| Under the tab '''Select, '''from the drop-down, select '''Field 2.'''
+
| Under the tab '''Select, '''from the drop-down, select '''Field 2.'''  
  
|-
+
|-  
| 07:53
+
| 08:07
| In the field for '''Map To, '''type: '''100$a. '''
+
| In the field for '''Map To, '''type: '''100$a. '''  
  
|-
+
|-  
| 07:59
+
| 08:13
|In the field for '''Indicators, '''type '''1.'''
+
|In the field for '''Indicators, '''type '''1.'''
 +
 +
|-
 +
| 08:17
 +
| 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:28
 +
| Likewise, complete the mapping of all the fields up to '''Field 13 '''as being shown in the drop-down under '''Select'''.  
  
|-
+
|-  
| 08:14
+
| 08:39
| Likewise, complete the mapping of all the fields up to '''Field 13 '''as being shown in the drop-down under '''Select'''.
+
| Notice the up and down arrows adjacent to each field.  
  
|-
+
|-  
| 08:24
+
| 08:44
| Notice the up and down arrows adjacent to each field.
+
|You may use these to change the sequence of the values that appear.  
 
+
|-
+
| 08:28
+
|You may use these to change the sequence of the values that appear.
+
  
|-
+
|-  
| 08:34
+
| 08:50
| Under the section '''Arguments, '''the''' Tags''' that are common with different '''sub-fields''' need to be joined.
+
| Under the section '''Arguments, '''the''' Tags''' that are common with different '''sub-fields''' need to be joined.  
  
|-
+
|-  
| 08:41
+
| 08:58
 
|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
+
| 09:09
 
| 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:17
| 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.  
  
|-
+
|-  
| 09:05
+
| 09:23
| Note that the '''<nowiki>* </nowiki>'''(asterisk symbol) will appear before the selected '''Tags.'''
+
| Note that the '''<nowiki>* </nowiki>'''(asterisk symbol) will appear before the selected '''Tags.'''  
  
|-
+
|-  
| 09:10
+
| 09:29
|The '''<nowiki>* </nowiki>'''asterisk symbol indicates that the common '''tags''' are now joined.
+
|The '''<nowiki>* </nowiki>'''asterisk symbol indicates that the common '''tags''' are now joined.  
  
|-
+
|-  
| 09:16
+
| 09:35
| 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.'''
+
| 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:52
|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.
 +
 +
|-
 +
| 09:59
 +
| Next we see four options.  
  
|-
+
|-  
| 09:41
+
| 10:02
| Next we see four options.
+
| First one is '''Save Template.'''
  
|-
+
|-  
| 09:44
+
| 10:06
| First one is '''Save Template.'''
+
 
+
|-
+
| 09:47
+
 
|  Use this if you want to save the same '''mapping''' for future use.  
 
|  Use this if you want to save the same '''mapping''' for future use.  
  
|-
+
|-  
| 09:52
+
| 10:12
| The '''saved template''' will be used if in case you face any problem with data conversion.
+
| The '''saved template''' will be used if in case you face any problem with data conversion.  
  
|-
+
|-  
|10:00
+
|10:20
| 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.  
  
|-
+
|-  
| 10:10
+
| 10:31
|It will get saved as '''.mrd file'''
+
|It will get saved as '''.mrd file'''  
  
|-
+
|-  
| 10:15
+
| 10:36
| Access this '''template '''in the future by clicking “'''Load Template” '''at the right side of the window.
+
| Access this '''template '''in the future by clicking “'''Load Template” '''at the right side of the window.  
  
|-
+
|-  
| 10:22
+
| 10:44
| Second option is '''Sort Fields.'''
+
| Second option is '''Sort Fields.'''  
  
|-
+
|-  
| 10:26
+
| 10:48
| Third option is''' Calculate common nonfiling data'''.
+
| Third option is''' Calculate common nonfiling data'''.  
  
|-
+
|-  
| 10:31
+
| 10:54
| Fourth option is''' 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.
+
Click here, if you have a header in the '''Excel sheet '''and if you need to ignore the headings.  
  
|-
+
|-  
| 10:41
+
| 11:05
| 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.  
  
|-
+
|-  
| 10:53
+
| 11:19
| Now, I will check the check-box for '''Save Template '''and''' Ignore Header Row.'''
+
| Now, I will check the check-box for '''Save Template '''and''' Ignore Header Row.'''  
  
|-
+
|-  
| 11:00
+
| 11:26
| Next, 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.  
  
|-
+
|-  
| 11:07
+
| 11:34
| 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:41
| 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:54
| Now, click on '''Save '''button at the bottom of the page.
+
| Now, click on '''Save '''button at the bottom of the page.  
  
|-
+
|-  
|11:32
+
|11:59
| 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
+
| 12:14
| Click on the '''Ok '''button at the bottom of this dialog-box.
+
| Click on the '''Ok '''button at the bottom of this dialog-box.  
  
|-
+
|-  
| 11:50
+
| 12:19
| 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.  
  
|-
+
|-  
| 11:59
+
| 12:29
| A new page''' MarcEdit 7.0.250 By Terry Reese, '''opens.
+
| A new page''' MarcEdit 7.0.250 By Terry Reese, '''opens.  
  
Locate and click on the icon '''MarcEditor'''.
+
Locate and click on the icon '''MarcEditor'''.  
  
|-
+
|-  
| 12:11
+
| 12:42
| 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:55
| A window '''Open File''' opens showing''' TestData.mrk '''file.
+
| A window '''Open File''' opens showing''' TestData.mrk '''file.
 +
 +
|-
 +
| 13:02
 +
| Click and select the '''TestData.mrk '''file.  
  
|-
+
It will show in the field for''' File name.'''  
| 12:30
+
| Click and select the '''TestData.mrk '''file.
+
  
It will show in the field for''' File name.'''
+
|-
 +
| 13:12
 +
| Now click on '''Open''' at the bottom of the window.
  
|-
+
|-  
| 12:39
+
| 13:16
| Now click on '''Open''' at the bottom of the window.
+
| Another window '''MarcEditor: TestData.mrk''' opens with all the details.  
  
|-
+
|-  
| 12:44
+
| 13:24
| Another window '''MarcEditor: TestData.mrk''' opens with all the details.
+
| On the same window, from the main menu, click on the '''File'''.  
  
|-
+
|-  
| 12:51
+
| 13:29
| On the same window, from the main menu, click on the '''File'''.
+
|Now, from the drop-down select '''Compile File into MARC.'''  
  
|-
+
|-  
| 12:56
+
| 13:35
|Now, from the drop-down select '''Compile File into MARC.'''
+
| Another new window '''Save File''' opens.
  
|-
+
Here, locate '''File Name: '''and type the appropriate name in the '''field'''.  
| 13:02
+
| Another new window '''Save File''' opens.
+
  
Here, locate '''File Name: '''and type the appropriate name in the '''field'''.
+
|-
 +
| 13:46
 +
|I will type '''TestData.'''  
  
|-
+
|-  
| 13:11
+
| 13:50
|I will type '''TestData.'''
+
| '''Koha, '''by default, selects '''MARC Files (*.mrc) '''in the field '''Save as type:'''.
  
|-
+
|-  
| 13:16
+
| 14:00
| '''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.  
 
| Now, click on the '''Save '''button located at the bottom of the window.  
  
|-
+
|-  
| 13:30
+
| 14:06
| Upon doing so, on the same window at the bottom, you will see '''5 records processed in 0.166228 seconds'''.
+
| Upon doing so, on the same window at the bottom, you will see '''5 records processed in 0.166228 seconds'''.  
  
|-
+
|-  
| 13:42
+
| 14:19
|This is because I imported only '''5 records'''. You will see a different number of '''records''' and processed time as per your data.
+
|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
+
| 14:29
| 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:37
|'''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:46
| 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:55
| 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
+
| 15:08
| 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.'''  
  
|-
+
|-  
| 14:41
+
| 15:20
| The video at the following link summarizes the '''Spoken Tutorial project.'''
+
| The video at the following link summarizes the '''Spoken Tutorial project.'''  
  
Please download and watch it.
+
Please download and watch it.  
  
|-
+
|-  
14:47
+
15:27
|  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.  
  
|-
+
|-  
||14:55
+
||15:35
|| Please post your timed queries in this forum.
+
|| Please post your timed queries in this forum.  
  
|-
+
|-  
| 14:59
+
| 15:39
| '''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.  
  
|-
+
|-  
| 15:09
+
| 15:50
| 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 18:03, 11 February 2019

Time Narration
00:01 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:29 To follow this tutorial, learners should be familiar with Library Science.
00:35 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:53 Earlier in the same series, we had installed MarcEdit 7 on the Desktop.
01:00 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:15 Locate and click on the tab Export Tab Delimited Text.
01:21 Under the Source File field, locate the icon for a folder.
01:27 The source file is an Excel file which we are converting into .mrk format.
01:34 Click on this icon for the Folder and browse for the Excel file in the field for File name.
01:42 Click on the drop-down adjacent to File name.
01:46 If you have Microsoft Excel 97/2000/XP/2003 (.xls) then choose the format Excel File(*.xls).
02:03 And if you have, Microsoft Excel 2007/2010/2013 XML(.xlsx) then choose the format Excel File(*.xlsx).
02:21 As I have the .(dot)xlsx file, I will select Excel XML File(*.xlsx).
02:32 Next, go to the left-side folders and select the folder where your Excel file is saved.
02:40 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:55 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:46 Now click on Save button at the bottom of the page.
03:51 The same window appears again.

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

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

However, this sheet name is editable.

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

The heading says Data Snapshot.

04:48 This window will have all the field details as per entries made in the Excel file.
04:55 We will see the Fields ranging from 0 to 8 and above with their corresponding values.
05:03 For example, Field 0 has a value of 978-3-319-47238-6 (ISBN) on my machine.
05:17 You may see a different value as per your Excel sheet.
05:22 Under the section DataSnapshot, locate the section Settings.
05:28 Go to the tab Select and from the drop-down select Field 0.
05:35 With this we will be doing the mapping of Excel data with Koha MARC Tags.
05:43 Remember, you can customize Map To: and Indicators.
05:49 However, it is important that the Fields and Subfield Codes are as per the Koha MARC Tag.
05:58 For more information on MARC Tags, visit the link of official Library of Congress site.
06:07 On the browser, type this URL and click on search.
06:15 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:31 This sequence will change as per your Excel data.
06:36 I will leave Indicators: and Term. Punctuation: as they are.
06:42 However, you may fill these fields as directed by the Koha MARC Tags.
06:49 Next is the check-box for Constant Data.
06:54 Click this if you wish to map the same information into the data field for each entry, in the delimited text document.
07:04 Click Repeatable subfield if you want to repeat the same subfield.
07:10 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:25 Similarly, let’s map all the other fields.
07:31 Under the section Settings, go to Select. From the drop- down, select Field 1.
07:39 In the field for Map To, type: 080$a.
07:46 Now, click on the button Add Argument.

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

08:01 Under the tab Select, from the drop-down, select Field 2.
08:07 In the field for Map To, type: 100$a.
08:13 In the field for Indicators, type 1.
08:17 Note that 1 is the first indicator of tag 100 and it represents the Surname for the subfield ‘a’.
08:28 Likewise, complete the mapping of all the fields up to Field 13 as being shown in the drop-down under Select.
08:39 Notice the up and down arrows adjacent to each field.
08:44 You may use these to change the sequence of the values that appear.
08:50 Under the section Arguments, the Tags that are common with different sub-fields need to be joined.
08:58 For that, do the following- Select common tags for example 245$a and 245$c.
09:09 Then right-click on common tags and from drop-down select Join Items.
09:17 This will create a grouping of the fields that are with the same type.
09:23 Note that the * (asterisk symbol) will appear before the selected Tags.
09:29 The * asterisk symbol indicates that the common tags are now joined.
09:35 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:52 However, I have done mapping manually. So, I will not click Auto Generate option.
09:59 Next we see four options.
10:02 First one is Save Template.
10:06 Use this if you want to save the same mapping for future use.
10:12 The saved template will be used if in case you face any problem with data conversion.
10:20 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:31 It will get saved as .mrd file
10:36 Access this template in the future by clicking “Load Template” at the right side of the window.
10:44 Second option is Sort Fields.
10:48 Third option is Calculate common nonfiling data.
10:54 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.

11:05 Among these, Sort Fields and Calculate common nonfiling data are auto-selected by MarcEdit 7.

I will leave them as they are.

11:19 Now, I will check the check-box for Save Template and Ignore Header Row.
11:26 Next, locate and click on the tab Finish on the top right corner of the page.
11:34 On doing so, Save File window opens, prompting us to fill in the File name.
11:41 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:54 Now, click on Save button at the bottom of the page.
11:59 A pop-up window with the message: Process has been finished. Records saved to:

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

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

Locate and click on the icon MarcEditor.

12:42 A new page MarcEditor opens.

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

12:55 A window Open File opens showing TestData.mrk file.
13:02 Click and select the TestData.mrk file.

It will show in the field for File name.

13:12 Now click on Open at the bottom of the window.
13:16 Another window MarcEditor: TestData.mrk opens with all the details.
13:24 On the same window, from the main menu, click on the File.
13:29 Now, from the drop-down select Compile File into MARC.
13:35 Another new window Save File opens.

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

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

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

15:20 The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

15:27 The Spoken Tutorial Project team conducts workshops and gives certificates.

For more details, please write to us.

15:35 Please post your timed queries in this forum.
15:39 Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

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

Thank you for joining.

Contributors and Content Editors

Nancyvarkey, PoojaMoolya, Pratik kamble, Sandhya.np14