Difference between revisions of "Koha-Library-Management-System/C3/Convert-Excel-to-MARC/English-timed"
Line 1: | Line 1: | ||
− | {| border=1 | + | {| border=1 |
− | |Time | + | |Time |
− | |Narration | + | |Narration |
− | |- | + | |- |
− | | 00: | + | | 00:02 |
− | |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: '''Windows 10 Pro''' and | + | |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: | + | |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: | + | | 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: | + | | 01:07 |
− | | | + | | A window named '''MarcEdit 7.0.250 By Terry Reese '''opens. |
− | |- | + | |- |
− | | 01: | + | | 01:16 |
− | | | + | | Locate and click on the tab '''Export Tab Delimited Text.''' |
− | |- | + | |- |
− | | 01: | + | | 01:21 |
− | | | + | | Under the '''Source File''' field, locate the icon for a '''folder.''' |
− | |- | + | |- |
− | | 01: | + | | 01:29 |
− | | | + | |The '''source file''' is an '''Excel file''' which we are converting into '''.mrk''' format. |
− | |- | + | |- |
− | | 01: | + | | 01:36 |
− | | | + | | Click on this icon for the '''Folder''' and browse for the '''Excel''' file in the '''field''' for '''File name'''. |
− | |- | + | |- |
− | | 01: | + | | 01:45 |
− | | Click on | + | | Click on the drop-down adjacent to '''File name.''' |
− | |- | + | |- |
− | | 01: | + | | 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: | + | | 02:23 |
− | | | + | | As I have the '''.(dot)xlsx ''' file, I will select '''Excel XML File(*.xlsx).''' |
− | |- | + | |- |
− | | 02: | + | | 02:33 |
− | | | + | | Next, go to the left-side folders and select the folder where your '''Excel file '''is saved. |
− | |- | + | |- |
− | | 02: | + | | 02:41 |
− | | | + | |I have selected '''Downloads '''because that’s where I saved my '''Excel file'''. |
− | |- | + | |- |
− | | 02: | + | | 02:47 |
− | | | + | | So, from the '''Downloads''' folder, I have selected '''TestData.xlsx.''' |
− | |- | + | |- |
− | | 02: | + | | 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: | + | | 03:09 |
− | | | + | | The same window re-opens with the '''Source File '''as '''C:\Users\spoken\Downloads\TestData.xlsx'''. |
− | |- | + | |- |
− | | 03: | + | | 03:21 |
− | | | + | | Now, click on the folder icon adjacent to the '''Output File.''' |
− | |- | + | |- |
− | | 03: | + | | 03:27 |
− | | | + | | On doing so, '''Save File '''window opens prompting us to fill in the '''File name.''' |
− | |- | + | |- |
− | | 03: | + | | 03:34 |
− | | On | + | | 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: | + | | 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: | + | | 04:31 |
− | | | + | | Click on the '''Next '''button at the right side of the same window. |
− | |- | + | |- |
− | | 04: | + | | 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: | + | | 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: | + | |
− | | For example, '''Field 0 '''has a value of '''978-3-319-47238-6 (ISBN)''' on my machine. | + | |
− | |- | + | |- |
− | | 05: | + | | 05:15 |
− | | You may see a different value as per your '''Excel sheet.''' | + | | You may see a different value as per your '''Excel sheet.''' |
− | |- | + | |- |
− | | 05: | + | | 05:21 |
− | | Under the section''' DataSnapshot, '''locate the section '''Settings.''' | + | | Under the section''' DataSnapshot, '''locate the section '''Settings.''' |
− | |- | + | |- |
− | | 05: | + | | 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: | + | | 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: | + | | 05:41 |
− | | Remember, you can customize '''Map To: '''and '''Indicators.''' | + | | Remember, you can customize '''Map To: '''and '''Indicators.''' |
− | |- | + | |- |
− | | 05: | + | | 05:46 |
− | |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: | + | | 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: | + | | 06:05 |
− | |On the browser, type this URL and click on '''search'''. | + | |On the browser, type this URL and click on '''search'''. |
− | |- | + | |- |
− | | 06: | + | | 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. |
− | |- | + | |- |
− | | 06: | + | | 06:26 |
− | |This sequence will change as per your '''Excel data'''. | + | |This sequence will change as per your '''Excel data'''. |
− | |- | + | |- |
− | |06: | + | |06:30 |
− | | 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: | + | | 06:41 |
− | | Next is the check-box for '''Constant Data.''' | + | | Next is the check-box for '''Constant Data.''' |
− | |- | + | |- |
− | | 06: | + | | 06:44 |
− | |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 |
− | | Click '''Repeatable subfield '''if you want to repeat the same '''subfield'''. | + | | Click '''Repeatable subfield '''if you want to repeat the same '''subfield'''. |
− | |- | + | |- |
− | | 07: | + | | 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.''' |
− | |- | + | |- |
− | | 07: | + | | 07:15 |
| Similarly, let’s map all the other '''fields'''. | | Similarly, let’s map all the other '''fields'''. | ||
− | |- | + | |- |
− | | 07: | + | | 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: | + | | 07:27 |
− | | In the '''field''' for '''Map To, '''type: '''080$a.''' | + | | In the '''field''' for '''Map To, '''type: '''080$a.''' |
− | |- | + | |- |
− | | 07: | + | | 07:32 |
− | | 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 |
− | | Under the tab '''Select, '''from the drop-down, select '''Field 2.''' | + | | Under the tab '''Select, '''from the drop-down, select '''Field 2.''' |
− | |- | + | |- |
− | | | + | | 07:53 |
− | | In the field for '''Map To, '''type: '''100$a. ''' | + | | In the field for '''Map To, '''type: '''100$a. ''' |
− | |- | + | |- |
− | | | + | | 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’.''' | |
− | + | ||
− | + | ||
− | |- | + | |- |
− | | 08: | + | | 08:14 |
− | | | + | | Likewise, complete the mapping of all the fields up to '''Field 13 '''as being shown in the drop-down under '''Select'''. |
− | |- | + | |- |
− | | 08: | + | | 08:24 |
− | |You may use these to change the sequence of the values that appear. | + | | 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: | + | | 08:34 |
− | | 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: | + | | 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 |
| 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: | + | | 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. |
− | |- | + | |- |
− | | 09: | + | | 09:05 |
− | | 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: | + | | 09:10 |
− | |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: | + | | 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.''' | + | | 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: | + | | 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. |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | |- | + | |- |
− | | | + | | 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. | | 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. | + | | The '''saved template''' will be used if in case you face any problem with data conversion. |
− | |- | + | |- |
− | |10: | + | |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. |
− | |- | + | |- |
− | | 10: | + | | 10:10 |
− | |It will get saved as '''.mrd file''' | + | |It will get saved as '''.mrd file''' |
− | |- | + | |- |
− | | 10: | + | | 10:15 |
− | | 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: | + | | 10:22 |
− | | Second option is '''Sort Fields.''' | + | | Second option is '''Sort Fields.''' |
− | |- | + | |- |
− | | 10: | + | | 10:26 |
− | | Third option is''' Calculate common nonfiling data'''. | + | | Third option is''' Calculate common nonfiling data'''. |
− | |- | + | |- |
− | | 10: | + | | 10:31 |
− | | 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 |
− | | 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 |
− | | 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: | + | | 11:00 |
− | | 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: | + | | 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: | + | | 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: | + | | 11:27 |
− | | Now, click on '''Save '''button at the bottom of the page. | + | | Now, click on '''Save '''button at the bottom of the page. |
− | |- | + | |- |
− | |11: | + | |11:32 |
− | | 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 |
− | | 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 |
− | | 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 |
− | | 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: | + | | 12:11 |
− | | 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: | + | | 12:24 |
− | | A window '''Open File''' opens showing | + | | 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: | + | | 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. | | 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'''. | + | | 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. | + | |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. | + | | With this, we have converted '''Excel data '''of our library into''' Marc 21 format '''successfully. |
− | |- | + | |- |
− | | 14: | + | | 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: | + | | 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: | + | | 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 |
− | | 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 |
− | | 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 |
− | | 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 |
− | || Please post your timed queries in this forum. | + | || Please post your timed queries in this forum. |
− | |- | + | |- |
− | | | + | | 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. |
− | |- | + | |- |
− | | 15: | + | | 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 18:19, 11 February 2019
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. |