Glossary

Excel to Glossaries

 
Picture of Shin Okada
Excel to Glossaries
 
I have glossaries in MS excel format. Is there any way or software I can create glossaries and import to Moodle? Help plz.
 
Average of ratings: -
Picture of Joseph Rézeau
Re: Excel to Glossaries
Core developersParticularly helpful MoodlersPlugin developersTestersTranslators
This post may help.
Joseph
 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
I created an Excel spreadsheet macro that takes the basics and creates an XML file which can be imported to moodle, referred to in this thread:
http://moodle.org/mod/forum/discuss.php?d=47741
the spreadsheet itself is at:

http://moodle.org/file.php/5/moddata/forum/126/310982/MoodleGlossaryPrepSheetWithCategory.xls

I'm sure you will be able to convert it to your needs.

Dave

 
Average of ratings: Useful (1)
Picture of Shin Okada
Re: Excel to Glossaries
 
Thanks David. I lowered my excel security and I can display the spreadsheet now. But I have a couple of questions.

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

  • Creating temporary structures
  • Deleting old data
  • Copying zip file
  • Unzipping backup
  • Checking for BlackBoard export
  • Checking backup
Error checking backup file. moodle.xml not found at root level of zip file.


An error has ocurred

----------------------------------------
What can I do ?

Thank you for your input. I appreciate it.


 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
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.

The error:
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.
Macro form
 
Average of ratings: -
Picture of Shin Okada
Re: Excel to Glossaries
 
Thank you very much. I made it.

I did not know that there is "Import entries / Export entries" at the top right.

What I did was, as you said, I tried to restore through zip file and also import. But I found the Import entries command, then everything went nicely.

Thanks again, you saved me a lot of time.
Great smile !!
Printer-friendly version
 
Average of ratings: -
Picture of Shin Okada
Re: Excel to Glossaries
 
Everything fine, thank you.

But I installed Office 2007 and it looks different from 2003. Can I still use it in office 2007?
 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
...it will be a while before I get 2007 so I can't test it, but I'd be surprised if it wasn't backwards-compatible. I suggest you try it and see...you might want to apply it to an empty glossary first to see if it has any adverse effects.
 
Average of ratings: -
Picture of Matt Krause
Re: Excel to Glossaries
 
David, I am new to Moodle, and started using your MoodleGlossaryPrepSheet.xls a few minutes ago. I am using Excel 2000, on a Windows XP machine, and Moodle 1.8.2. My glossary has hundreds of entries, and your excel file rocks, thank you very much for saving a ton of my time!

Best regards,

Matt
 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 

Glad it helped you out.blush

and welcome to moodle, I have found that everyone tries hard to help out the new arrivals and the improvements I have seen in the core elements over the last year have been outstanding.

 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
Have you seen the newer version that allows you to put in categories?

http://moodle.org/file.php/5/moddata/forum/126/310982/MoodleGlossaryPrepSheetWithCategory.xls
 
Average of ratings: -
Picture of Matt Krause
Re: Excel to Glossaries
 
Yes, I have seen the newer version with categories, but my glossary is very simple at this point, so I haven't tried the souped-up version yet.

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.

Best regards,

Matt

 
Average of ratings: -
Picture of gut man
Re: Excel to Glossaries
 
David, I've tried the template that you've poseted in the thread.
http://moodle.org/mod/forum/discuss.php?d=79120

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!

Spencer
gut@ms48.url.com.tw

 
Average of ratings: -
Picture of Matt Krause
Re: Excel to Glossaries
 
Spencer, I am having a similar problem. My site (www.supplychainenglish.com) is in English and Turkish, and in the glossary, I need to show the English word, and give a Turkish definition.

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.

Best regards,

Matt Krause

 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 

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:

  1. Create a new moodle glossary
  2. Enter one concept/definition
  3. Export this glossary
  4. Create another new moodle glossary
  5. 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.

 
Average of ratings: -
Picture of gut man
Re: Excel to Glossaries
 
Hi,David
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!

Download.
http://www.toofiles.com/zh/oip/documents/zip/3073_excelmacro.html

 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 

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.

Dave

 
Average of ratings: -
Picture of gut man
Re: Excel to Glossaries
 
Hi~David,
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".

PS.
"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/...

<DEFINITION>

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" ?>
- <GLOSSARY>
- <INFO>
<NAME>Nations</NAME>
<INTRO />
<STUDENTCANPOST>0</STUDENTCANPOST>
<ALLOWDUPLICATEDENTRIES>0</ALLOWDUPLICATEDENTRIES>
<DISPLAYFORMAT>dictionary</DISPLAYFORMAT>
<SHOWSPECIAL>1</SHOWSPECIAL>
<SHOWALPHABET>1</SHOWALPHABET>
<SHOWALL>1</SHOWALL>
<ALLOWCOMMENTS>0</ALLOWCOMMENTS>
<USEDYNALINK>1</USEDYNALINK>
<DEFAULTAPPROVAL>0</DEFAULTAPPROVAL>
<GLOBALGLOSSARY>1</GLOBALGLOSSARY>
<ENTBYPAGE>10</ENTBYPAGE>
- <ENTRIES>
- <ENTRY>
<CONCEPT>China</CONCEPT>
<DEFINITION>[n]中國</DEFINITION>
<FORMAT>1</FORMAT>
<USEDYNALINK>1</USEDYNALINK>
<CASESENSITIVE>0</CASESENSITIVE>
<FULLMATCH>1</FULLMATCH>
<TEACHERENTRY>1</TEACHERENTRY>
- <ALIASES>
- <ALIAS>
<NAME>China</NAME>
</ALIAS>
</ALIASES>
</ENTRY>
- <ENTRY>
<CONCEPT>Taiwan</CONCEPT>
<DEFINITION>[n]台灣</DEFINITION>
<FORMAT>1</FORMAT>
<USEDYNALINK>1</USEDYNALINK>
<CASESENSITIVE>0</CASESENSITIVE>
<FULLMATCH>1</FULLMATCH>
<TEACHERENTRY>1</TEACHERENTRY>
- <ALIASES>
- <ALIAS>
<NAME>Taiwan</NAME>
</ALIAS>
</ALIASES>
</ENTRY>
- <ENTRY>
<CONCEPT>Canada</CONCEPT>
<DEFINITION>[n].加拿大</DEFINITION>
<FORMAT>1</FORMAT>
<USEDYNALINK>1</USEDYNALINK>
<CASESENSITIVE>0</CASESENSITIVE>
<FULLMATCH>1</FULLMATCH>
<TEACHERENTRY>1</TEACHERENTRY>
- <ALIASES>
- <ALIAS>
<NAME>Canada</NAME>
</ALIAS>
</ALIASES>
</ENTRY>
</ENTRIES>
</INFO>
</GLOSSARY>

How come?
Do you have any suggestion?
Thanks!

Spencer

Download: (Click the yellow box then you can download the file)
http://www.toofiles.com/zh/oip/documents/zip/excelmacro.html

 
Average of ratings: -
Picture of gut man
Re: Excel to Glossaries
 
The dumpest way to creat an imported possible xml 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"?>
<GLOSSARY>
<INFO>
<NAME>Nations</NAME>
<INTRO></INTRO>
<STUDENTCANPOST>0</STUDENTCANPOST>
<ALLOWDUPLICATEDENTRIES>0</ALLOWDUPLICATEDENTRIES>
<DISPLAYFORMAT>dictionary</DISPLAYFORMAT>
<SHOWSPECIAL>1</SHOWSPECIAL>
<SHOWALPHABET>1</SHOWALPHABET>
<SHOWALL>1</SHOWALL>
<ALLOWCOMMENTS>0</ALLOWCOMMENTS>
<USEDYNALINK>1</USEDYNALINK>
<DEFAULTAPPROVAL>0</DEFAULTAPPROVAL>
<GLOBALGLOSSARY>1</GLOBALGLOSSARY>
<ENTBYPAGE>10</ENTBYPAGE>
<ENTRIES>
<ENTRY>
<CONCEPT>Taiwan</CONCEPT>
<DEFINITION>[n]台灣
</DEFINITION>
<FORMAT>1</FORMAT>
<USEDYNALINK>1</USEDYNALINK>
<CASESENSITIVE>0</CASESENSITIVE>
<FULLMATCH>1</FULLMATCH>
<TEACHERENTRY>1</TEACHERENTRY>
<ALIASES>
<ALIAS>
<NAME>Taiwan</NAME>
</ALIAS>
</ALIASES>
</ENTRY>
-----------------------------------------------
<ENTRY>
<CONCEPT>Canada</CONCEPT>
<DEFINITION>[n]加拿大
</DEFINITION>
<FORMAT>1</FORMAT>
<USEDYNALINK>1</USEDYNALINK>
<CASESENSITIVE>0</CASESENSITIVE>
<FULLMATCH>0</FULLMATCH>
<TEACHERENTRY>1</TEACHERENTRY>
<ALIASES>
<ALIAS>
<NAME>Canada</NAME>
</ALIAS>
</ALIASES>
</ENTRY>
-----------------------------------------------
</ENTRIES>
</INFO>
</GLOSSARY>

PS.
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.

Spencer



 
Average of ratings: -
Picture of Yasu Imao
Re: Excel to Glossaries
 
I wish I had found this thread before I worked on my own solution. Recently I had to import 21 Google Spreadsheet sheets (exported as .csv) to Moodle glossary for one of our faculty members.

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.


Yasu
Picture of David Fountain
Re: Excel to Glossaries
 
Yasu,

With regard to your Read Me file if you have somewhere else you can host it you can then just post the link.

Dave
 
Average of ratings: -
Picture of Yasu Imao
Re: Excel to Glossaries
 
Thank you for your suggestion, David. But I made some changes to the program so that it doesn't need any screenshots to help figure out how to use (that's why the Read Me file was big). I included text-only Read Me file in rtf.

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.

Yasu
Picture of Yasu Imao
Re: Excel to Glossaries
 
Sorry to mess up the thread again.

I found a bug in my program, which I introduced when I made changes last time.

To make up my mess, I also wrote (and included in the zip file) a simple HTML page with Javascript. What you can do is copy and paste glossary entries from Excel file, convert them, copy and paste output to text editor document (plain text with UTF-8 or ASCII encoding) and save as .xml. Concept and Definition are necessary but Category and Keywords are optional. I did a quick check with IE on Windows XP and it looks working. This should work locally on your computer or you can put it anywhere (i.e. on Moodle site), though it doesn't save a file for you. As always, if you find any bug, I'll try to fix it.

Yasu
 
I Teach With Moodle
Re: Excel to Glossaries
Plugin developersTesters
Just a quick message to say a big "Thank you" Yasu. Your program does exactly what it says on the box, very well, no fuss! The instructions are very easy to follow.

You've saved me a lot of time,

thanks again,

Fred
 
Average of ratings: -
Leon Huang
Re: Excel to Glossaries
 

Your application is great. I've put this link for my uni to dowload your application. Thankyou Yasu.

Picture of David Fountain
Re: Excel to Glossaries
 
Spencer,
Yes the problem with my VBA is the UTF-8, I haven't found a solution for that yet though I have seen some references to it.
Dave
 
Average of ratings: -
Picture of Pedro Guevara
Re: Excel to Glossaries
 
Hi
When I use spanish accented characters (á é í ó ú) the resulting xml does NOT work. Without these characters it works OK

What can I do?

Thanks
 
Average of ratings: -
AugSEP2007
Re: Excel to Glossaries
 
I am having trouble using your .xls conversion file for glossary entries on the MacBook side. It works on the windows side for me, but have you had trouble, too, making it work on the Mac side?

 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
Hi Chick
Fraid I don't have/use a mac at all so have no experience. sad

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!
 
Average of ratings: -
Picture of ola feurst
Re: Excel to Glossaries
 

Dear David,  Thank you soo much for your generous contributions! I downloaded your Excel to Moodle macro and followed your built in intrstructions. It save my day! My warmest thanks to you!  /Ola

 
Average of ratings: -
Picture of Hellmuth Sole
Re: Excel to Glossaries
 
Hi all, I think I've found a workaround for those which are using non-english characters and wish to import the file from an excel worksheet through one of the xml converters. I've tested this with spanish characters (á, é, í, ó, ú and ñ and they work perfectly).

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!!
 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
Sounds like a great addition Hellmuth, mind if I add your instructions to the Excel file?

Dave
 
Average of ratings: -
Picture of Hellmuth Sole
Re: Excel to Glossaries
 
Sounds fine by me Dave!! As long as we can save some time for others, it's well worth doing.

Oh an update, I found that the & symbol doesn't properly export, so I'd recommend a search and replace beforehand and subsititute it for "and" in whatever language you're using.

 
Average of ratings: -
Picture of Jean-Luc Delghust
Re: Excel to Glossaries
 
Thanks, this is what I was looking for!

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 smile
 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
Jean,
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'.
 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 

Jean,

This should do the trick.

Just separate the categories with commas. (Do the same for aliases)

Picture of Jean-Luc Delghust
Re: Excel to Glossaries
 
Wow, thanks!
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 smile 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):

come:
came, came
venir

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!


 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
Jean-Luc, (and anyone else) please feel free to adapt it - glad to have been of help.

Dave
 
Average of ratings: -
Picture of David Fountain
Re: Excel to Glossaries
 
...and this is a tidier version with some other bug fixed.
 
Average of ratings: Useful (1)
Picture of Christoph Ruda
Re: Excel to Glossaries
 

Thanks for this. Even though it's 6 years ago that you posted this i found it today and it worked great. Much appreciated.

 
Average of ratings: -
Picture of Jocelyn Chappaz
Re: Excel to Glossaries
 

Hi.

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 ??

 
Average of ratings: -
Picture of Yasu Imao
Re: Excel to Glossaries
 
I haven't looked at this forum for a long time, but it looks like some people are using my javascript. I guess David's Excel solution would be a better option, though.

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.
 
Average of ratings: Useful (1)
Picture of Maria S
Re: Excel to Glossaries
 

So many years later, and still works! Wow! Thank you!!

I used the glossaryXMLconverter, and then I used Textwrangler for Mac to save it as XML. Worked like a charm!




 
Average of ratings: -