Difference between revisions of "Koha-Library-Management-System/C3/Convert-Excel-to-MARC/English-timed"
From Script | Spoken-Tutorial
| Line 191: | Line 191: | ||
| 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. | ||
| − | + | |- | |
| + | | 06:24 | ||
| + | | I will enter '''020$a '''in the '''Map To:''' field. | ||
|- | |- | ||
| Line 221: | Line 223: | ||
| 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.''' | + | |- |
| + | | 07:15 | ||
| + | |On doing so, the value '''0 020$a 0 '''appears in the field under the section '''Arguments.''' | ||
|- | |- | ||
| Line 228: | Line 232: | ||
|- | |- | ||
| − | | 07: | + | | 07:30 |
|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. ''' | ||
| Line 239: | Line 243: | ||
| 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.''' | + | |- |
| + | | 07:50 | ||
| + | | On doing so, the value '''1 080$a 0 '''appears in the field under the section '''Arguments.''' | ||
|- | |- | ||
| Line 341: | Line 347: | ||
| 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. | + | |- |
| + | |10:58 | ||
| + | |Click here, if you have a header in the '''Excel sheet '''and if you need to ignore the headings. | ||
|- | |- | ||
| Line 347: | Line 355: | ||
| 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. | + | |- |
| + | |11:15 | ||
| + | |I will leave them as they are. | ||
|- | |- | ||
| − | | 11: | + | | 11:18 |
| 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.''' | ||
| Line 365: | Line 375: | ||
| 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.''' | + | |- |
| + | |11:48 | ||
| + | |And, in the '''field''' for '''File name, '''I will type '''TestData.''' | ||
|- | |- | ||
| Line 405: | Line 417: | ||
| Click and select the '''TestData.mrk '''file. | | Click and select the '''TestData.mrk '''file. | ||
| − | It will show in the field for''' File name.''' | + | |- |
| + | |13:07 | ||
| + | |It will show in the field for''' File name.''' | ||
|- | |- | ||
| − | | 13: | + | | 13:11 |
| Now click on '''Open''' at the bottom of the window. | | Now click on '''Open''' at the bottom of the window. | ||
| Line 427: | Line 441: | ||
| Another new window '''Save File''' opens. | | Another new window '''Save File''' opens. | ||
| − | Here, locate '''File Name: '''and type the appropriate name in the '''field'''. | + | |- |
| + | | 13:39 | ||
| + | |Here, locate '''File Name: '''and type the appropriate name in the '''field'''. | ||
|- | |- | ||
| Line 491: | Line 507: | ||
| '''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. | + | |- |
| + | |15:45 | ||
| + | |More information on this mission is available at this link. | ||
|- | |- | ||
Revision as of 16:05, 12 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. |
| 06:24 | 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. |
| 07:15 | 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:30 | 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. |
| 07:50 | 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. |
| 10:58 | 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. |
| 11:15 | I will leave them as they are. |
| 11:18 | 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. |
| 11:48 | 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. |
| 13:07 | It will show in the field for File name. |
| 13:11 | 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. |
| 13:39 | 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. |
| 15:45 | 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. |
