Database

Importing Entries Using a Text File

 
Picture of James Phillips
Importing Entries Using a Text File
 
Dear All,

Since the database module was introduced, I have periodically tried, on several versions of moodle, over the course of about one year, to import entries into the database using a text file. It has never worked, and I have usually asked a few questions and then ended up giving up on it. I have tried every conveivable combination of CSV including enclosed, unenclosed, simple comma separation, and even copied the example in the help file but nothing has worked. I therefore have the following questions.

1) Has anybody on here ever successfully imported a text file into a database using moodle?

2) If so, what was the file encoding (Unicode?, UTF-8, . . . ) and exact format of the file (comma separation? enclosed?).

3) Did the successfully imported file simply contain English? If so, has anybody imported a file other than English (my files contain Japanese and English).

4) Have most people, as I suspect, ended up bypassing this function and ended up inserting the entries directly into the database via php admin or some similar approach?

Regards,

James Phillips
 
Average of ratings: -
Picture of Peter Max
Re: Importing Entries Using a Text File
 
What about the PHP patch described in response to your Aug 2006 posting?

http://moodle.org/mod/forum/discuss.php?d=46048
 
Average of ratings: -
Picture of rich holman
Re: Importing Entries Using a Text File
 

James

Back in August I modded import.php because it called certain functions incorrectly. The changes I made enabled me to import using csv. One other user also reported that the changes worked for them as well.

I'm a bit out of touch with changes to the db module so I dont know if the modified script will still work, but its worth a try.

The script is attached in the AUGUST thread.

Rich

 
Average of ratings: -
Picture of Amir Elion
Re: Importing Entries Using a Text File
Translators

Has this problem been fixed in current version?

I am experiencing same trouble with Hebrew text and have struggled with various attempts/combinations to save from Excel and google docs, Unicode, UTF-8.

Sometimes I get the file uploaded but Hebrew text fields imported as blanks, sometimes I get this error message: The following fields in your file are not known in this database: 'Title'. Title is the first field name however in the DB...

Is this a known bug?

 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
Hi Amir,

Could you post a short sample file?

tia
Frank
 
Average of ratings: -
Picture of Amir Elion
Re: Importing Entries Using a Text File
Translators
Here is one. Will post another version of a different attempt.
I have now also tried with Open Office and also get same errors.
 
Average of ratings: -
Picture of Amir Elion
Re: Importing Entries Using a Text File
Translators
Another version
 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
Thanks! Just did a quick check in a text editor. This file is ANSI encoded and therefore the Hebrew is lost.
 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
This file looks OK in the text editor (UTF-8 encoded), but the line feed is DOS/Windows which might cause problems when uploading such a file to a Linux server because it won't see any line feeds at all (see http://en.wikipedia.org/wiki/Newline#Common_problems for further details).

I converted the file to UNIX format and attached it. Please try again with that file.

hth
Frank
 
Picture of Amir Elion
Re: Importing Entries Using a Text File
Translators

I tried with your file and this is what I got:

The following fields in your file are not known in this database: 'after {content: "fieldset";}Title'

I did send it save the file to my windows PC on the way as it is my PC and uploaded to the Linux system of the moodle server, but I don't think it should make any difference, should it - i did not open or edit the file on my windows before trying to upload it.

 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
Have you tried exporting your database and re-importing it (see Database export)?
Or did you create the database with an external application?

Database module FAQ and Import and export FAQ might provide further information.

hth
Frank
 
Average of ratings: -
Picture of Amir Elion
Re: Importing Entries Using a Text File
Translators

I did also export and used the exported file to add the content to the fields.

Can this not be a bug? Can we not submit it on the tracker for solution?

And another note - can we not aspire that the database import can handle upload from windows PCs as well?

 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
Here's a short list with all relevant forum discussions (9, including this one):
http://moodle.org/mod/forum/search.php?search=forumid%3A587+subject%3Adatabase+subject%3Aimport+-Re%3A+-MySQL&id=5

Especially http://moodle.org/mod/forum/discuss.php?d=46048 looks promising.

And there are already some (8) issues in the tracker:
http://tracker.moodle.org/secure/IssueNavigator.jspa?reset=true&&query=database+AND+import+AND+activity&summary=true&sorter/field=summary&sorter/order=ASC

Please have a look, whether your problem is among them. I haven't used database import/export myself yet, but will do some testing.

שלום
Frank
 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File - UTF-8 without BOM
Documentation writersPlugin developers
I think I found the culprit. I could reproduce your error message with your file and got suspicious because Moodle complained only about the very first entry in the file. So I saved your file in UTF-8 but this time _without_ BOM (http://en.wikipedia.org/wiki/Byte-order_mark) and voilá - it worked!

In the attachment you find:

1. your original file, saved in UTF-8 encoding without BOM (DOS/Windows line endings, so that was a false alarm)
2. the re-exported database entries (importing this again also worked)
3. a screenshot of an entry, (there seems to be some problem with the quotes, though, try leaving them out)

hth
Frank
Frank Ralf
Re: Importing Entries Using a Text File - UTF-8 without BOM
Documentation writersPlugin developers
JFTR:
Issue MDL-9075 ("User Upload Failure when file contains utf-8 bom") seems to be related.
 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
Oops, that was me, sorry. Somehow I messed up the first line of your file. Please try again.

Frank
Picture of Amir Elion
Re: Importing Entries Using a Text File
Translators
wink

I did another attempt and was able to succeed on another PC. I used Open Office calc and this time it was on Windows XP rather than Vista on my other machine.
I started by exporting the fields from the databse, then opened the csv file in Calc, pasted the content of the fields, and saved as CSV, UTF-8 encoding. And it finally worked.

Thanks for the help.
I still think it would be helpful to be able to import non-English text without all this nueances and hassle from other platforms as well...
 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
I finally managed to save an OpenOffice Calc file as CSV file but the UTF-8 still contains a BOM. And if you choose double quotes as text delimiter the BOM even sneaks in _after_ the first opening quote where you only can see and delete it with a good Unicode editor (see screenshot)!

This is a vast field and I think I will better write something about it in Moodle Docs.
For the time being see Category:UTF-8.

Cheers,
Frank

 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
And here's how a successful multilingual database import should look like:

 
Average of ratings: -
Frank Ralf
Re: Importing Entries Using a Text File
Documentation writersPlugin developers
JFTR

See UTF-8 and BOM on Moodle Docs and the forum discussion on "Beware of the BOM" for further information on this subject.
 
Average of ratings: -