Excel to Glossaries
the spreadsheet itself is at:
I'm sure you will be able to convert it to your needs.
1. Can I change Concept to Term?
2. Do I need to fill the format, usednalink, casesensitive, fullmatch, category?
3. If so, what do I need to input? Boolean, 0 or 1?
I tried this and I got the following message.
Course restore: Bio_lifeand_cells_MoodleGlossary.zip
Error checking backup file. moodle.xml not found at root level of zip file.
What can I do ?
Thank you for your input. I appreciate it.
1. Can I change Concept to Term? -
Yes, the first row is not imported. You should fill in Concept and definition. Category is optional.
2. Do I need to fill the format, usednalink, casesensitive, fullmatch, category?When you run the macro it should give you a form to select how you want these to be treated, there is a button on the form to push to have it fill in the format, usedynalink, casesensitive and fullmatch (category you can fill in)
3. If so, what do I need to input? Boolean, 0 or 1?0 or 1 if you want to do this manually.
You are trying to use the restore function from the admin panel (I think) - this is to restore 'courses'.
My excel macro creates an XML file to import into a glossary. You need to create a blank glossary (unless you already have one set up ready for your new terms) and use the import glossary function.
But I installed Office 2007 and it looks different from 2003. Can I still use it in office 2007?
Thanks again for making these things, they are an immense help and they plug what seems to be an obvious open space in the software.
I found that if I key in the English words into the concept column and definition column.
The glossary will be imported without any problem.
If I key in the Chinese words into the concept column and definition column,
the glossary seems to be imported incomplete.
Do you think if these is any solution?
Thanks a lot!
As far as I can tell, the Excel macro provides Turkish-language output just fine, but when I try to import it into Moodle, that's when things error out.
I haven't figured out how to import Turkish characters into the glossary yet, so I settled for using the English alphabet only, when writing the Turkish words. I'm missing Turkish characters, but they are close enough so my students can figure things out. For Chinese, I figure that approach would be of no use whatsoever, though.
However, it's only half a solution. For me, too, figuring out how to get those non-English-alphabet characters to import into the glossary will be very useful. Perhaps it's an easy solution, I just haven't had time to look into it deeper.
Hi Spencer and Matt,
I'm afraid that is probably beyond my skill set, however...
I would try the following to see if it is possible:
- Create a new moodle glossary
- Enter one concept/definition
- Export this glossary
- Create another new moodle glossary
- Import your exported file into this glossary
If it doesn't work then it is a moodle issue.
If it does work then you can examine the xml exported file in a text editor to see what differences it has to the output my spreadsheet produces, send me a copy if you like and I will have a look to see what I can do.
I've tried the suggestion you told me in the email.
First, I created one vocabulary (concept/definition) in the new glossary.
Then, I export the xml file which contained only one vocabulary.
And import the xml file into another new created glossary.
There is no problem.
Second try out,
I created three vocabularies (concept/definition) in the new glossary.
Then, I export the xml file which contained three vocabularies.
And import the xml file into another new created glossary.
There is no problem, too.
So, I guess there isn't any issue with my moodle 1.6.3. (Lucky ^_^)
Here are the xml files.
a.1 word xml file
b.3 words xml file
Actually, I'm not really good at programming,
so I don't know how to compare the procedures between your excel file and
my xml files. Besides, I also show you the procedures that I used your excel macro.
Please have a look, Thanks!
I think this is related to the encoding ie utf-8 and the fact my spreadsheet macro doesn't do this properly. I will investigate but at the moment this is not in my set of skills...
I opened the xml file produced by my spreadsheet and saved it using UTF-8 encoding and it now imports but I think that the definition is messed up.
Could you send me a copy of the spreadsheet with 3 definitions in it so I can play with the macro code.
Sorry for keep you waiting.
The spreadsheet with 3 definitions is called "MoodleGlossary.3words.xml".
I've put it into a compressed file called "excelmacro".
You can just extract the compressed file.
There are some explanations in the "read.txt".
"MoodleGlossary.3words.xml": the xml file which produced by your excel macro
"Nations.3words": exported from moodle glossary
Today, I further found one specific situation between the xml produced by your excel and the xml produced by moodle export.
When I try to use IE6.0 to open the "MoodleGlossary.3words.xml",
the message showed that...
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
An invalid character was found in text content. Error processing resource 'file:///C:/Documents and Settings/Administrator/...
When I open the "Nations.3words.xml" with IE6.0, it seems don't have such a problem...(as you can see in the following)
<?xml version="1.0" encoding="UTF-8" ?>
Do you have any suggestion?
Download: (Click the yellow box then you can download the file)
Nations_try.xml xml exported from moodle glossary without any words
1Nations.xml xml exported from moodle glossary (including 1 word)
2Nations.xml xml exported from moodle glossary (including 2 words)
try/3Nations.xml self-modified the "2Nations.xml" into "3Nations.xml" (including 3 words)
Way to do it.
1.copy "2Nations.xml" and rename it into "3Nations.xml"
1.Open "2Nations.xml" and "3Nations.xml" in "PSPad"(an utf8 editing sofware)
2.look at the "2Nations.xml", mark line 33 to line 47, and copy it (it includes all the data you need when you add one single word into the moodle glossary)
3.paste it to the line 47 in "3Nations.xml".
4.modified the words in the three tags: "Concept", "Definition", "Name"
5.save the file
7.go to moodle glossary and import this xml file. You will be fine.
<?xml version="1.0" encoding="UTF-8"?>
I think this is the dumpest way to creat an imported possible xml file.
I really hope that someone can really program or design a small tool that can replace all the procedures i mentioned.
I use Leopard (OS X 10.5) on Mac, so my solution is probably not very useful to most of the people here, but I want to share it anyway.
Originally, I wrote a Ruby script, but Leopard comes with a bridge (available for free) between Ruby and Cocoa. So I put interface to my script and made it a little fancy (this is why my program only runs on Leopard, though you might be able to run it if you install RubyCocoa on Tiger; no guarantee). I'm not a programmer (a Humanities grad student working as a tech support), so my script is messy, but it worked fine for my purpose.
Basically what it does is take CSV or tab-delimited text files encoded in UTF-8 and export an XML file that Moodle glossary module can import.
If you are one of the rare people who use Leopard now, feel free to try this. I also created a Read Me file, but it's too big to upload here. Is there any other way? Anyway, if you find any error, please post it here. I will try to fix them if I have time.
So for those who might be interested, this small GUI utility program only runs on Mac OS X 10.5 Leopard (sorry).
What this does is convert CSV files exported from Apple's Numbers or Google Doc's Spreadsheet (encoded in UTF-8) to an XML file that Moodle Glossary Tool can import. You can import CSV files to this program from menu or by clicking a button. Or you can drag and drop the files onto the table.
And I forgot to mention in the previous post (well, this thread is Excel to Glossaries...) that this program accepts Drag and Drop from Excel 2004. This means you can select columns, grab them, and drop onto the table on this program. I don't have Excel 2008 in my office, so I haven't tested it yet.
If you find any bug, please post it here. I'll try to fix it.
I found a bug in my program, which I introduced when I made changes last time.
Fraid I don't have/use a mac at all so have no experience.
I would only be guessing, could you describe the problem a little more fully...my first thoughts are that the macro (VBA) is having trouble writing to a file due to some difference with file structures/permissions. But it's only a guess!
Yasu's glossaryXMLconverter.zip file works perfectly for most, but for those which have multiple lines (separated by an alt-enter in excel) in a definition, it will all a quotation mark before the first definition of the concept and an end quote after the last.
With David Fountain's MoodleGlossaryPrepSheetWithCategory.xls macro file, it works perfectly with the enters, however it does give problems as many of you mention when importing special characters. Apparently the problem has to do with the encoding of the xml when the macro is run.
To solve this, quite easy:
1. make your glossary in excel using David Fountain's MoodleGlossaryPrepSheetWithCategory.xls file. Save the file to the desktop or somewhere obvious.
2. Right click the file, and select "open with". Here choose notepad as the program to open the file.
3. Once you open, simply go to file - save as and save the file with a different name (add a one for example to the end) and make sure the extension is xml. VERY IMPORTANT that in the encoding box at the bottom, you choose UTF-8.
4. Upload the resulting file to Moodle. Non-english characters should appear and if you have multiple lines in your definitions, these should show up correctly.
In my case I get a series of rejected entries which state "--- No Concept or Definition Found" but I think this has something to do with certain blank cells in excel, since all terminology is successfully imported.
Hope this helps other languages with special characters as well. Please post to confirm if this workaround is successful!!
Well, almost, but it's going to save me loads of time.
I have two questions though:
- Is it possible to entre multiple categories?
- Is it possible to add aliases? We use the glossaries in texts (as dictionaries) and the alias feature is pretty important to us.
Still, thanks for this, I can get started on my glossaries
There are two programs in this thread (my Excel spreadsheet/macro and Yasu Imao 's converter program?)
My spreadsheet does not allow multiple categories, and I'm not sure what you mean by aliases, are they 'keywords'?
However the macro is quite simple and it shouldn't be too big a job to add these in, particularly if we specify a maximum number of categories so I don't have to get too bogged down with coding it 'properly'.
This should do the trick.
Just separate the categories with commas. (Do the same for aliases)
Should have checked back yesterday during the day, I spent most of my day figuring out how macros work. I think I figured it out more or less, I came up with a system in which I had to clean up the code afterwards, but kind of did the trick Your spreadsheet seems simpler to use though, which is great news for my colleagues and myself. I'll give it a go tonight after my courses. Then again, I didn't completely waste my time, at least I've learnt something.
I found an answer for html code, I think: in definition, just type html code but replace all < and > by "& l t ;" and "& g t ;" (without the spaces). The file still needs converting in UTF8 afterwards (we have a lot of latin characters in our courses)
I created a spreadsheet with an additional column for lexical information, and the macro automatically adds the html code to put it in italics and add a line break after the lexical information. So the entry looks a bit like this: (EN-FR glossary):
The info in that extra column is added between the <DEFINITION> tags... I can upload or send you the spreadsheet if you want
(yup, we're a language school, we need detailed glossaries)
Again, thanks! I'll give your spreadsheet a go. Do you mind if I customize it to fit our needs?
Have a great day!
Thanks for this. Even though it's 6 years ago that you posted this i found it today and it worked great. Much appreciated.
I've tested both .xls and the html converter... none of them worked.
the xls give a VBA error at launch, the html converter do ouputs a .xml file but when I import it into moddle, it says that nothing can be imported...
is someone able to do something with them with the actual version of moddle ??
Anyway, I also updated my script for those who might be interested. You can add multiple categories/keywords (separated by a comma , in a single column). Also I made an attempt to keep line breaks generated on Excel. The script assumes you have multiple lines only in definitions (not in concepts). I did a simple test, but I'm not sure if this works with real data. I'm wondering if someone could test this.