Jix: Import, Export & Update Utility for Joomla

[PtrNrs]

September 2009 update: Security Update Jix V0.023 now available at http://www.jix.com.au

Jix is a powerful & flexible bulk import, export & update utility for Joomla which was launched in Feb 2009. It does the following:-

• Imports &/or updates Joomla table data from a CSV* file;
  Exports data in a readable format from Joomla to a CSV* file;

* Alternatively, you may use you own custom delimiter.

You can read more and download a free copy of Jix at http://www.jix.com.au.

I hope you like Jix and I invite your comments & suggestions here and/or on the Joomla Extensions Directory page at http://extensions.joomla.org/extensions ... 30/details

[sdclee]

Testing it on a brand new 1.5.9 db with no content and attempting to import sections (where I have deliberately removed the supplied sestions_???.csv files, I now see that Jix references the parameters and uses the ascii value of the separator character as part of the filename, so a comma separatator is decimal 44, therefore Jix expects to find 'sections_044.csv'.
Using an exported sections csv file from my 1.5.0 beta I get the following when I try to import to the new DB:

Code: Select all

Warning: Call-time pass-by-reference has been deprecated; 
If you would like to pass it by reference, modify the declaration of [runtime function name](). 
If you would like to enable call-time pass-by-reference, you can set 
allow_call_time_pass_reference to true in your INI file in ... [snip]... on line 122
Error 100: Expected header 'ID' not found in ... (cropped to save wide-screen display)
Error 101: Expected header 'Title' not found in ...
Error 102: Expected header 'Alias' not found in ...
Error 103: Expected header 'Image' not found in ...
Error 104: Expected header 'Scope' not found in ...
Error 105: Expected header 'Image Position' not found in ...
Error 106: Expected header 'Description' not found in ...
Error 107: Expected header 'Params' not found in ...

The warning should have no effect on the errors which follow and it looks as though Jix runs case-insensitively with respect to column names, so I have the following question:

Whilst I understand that my columns will not map directly onto a 1.5.9 schema (and I am simply trying to better understand Jix' structure and error reporting), I don't follow why it errors for fields which are in the csv file eg id, title, where obviously it would for fields such as alias (which isn't).

Here's a snippet:

Code: Select all

"id","title","name","image","scope","image_position","description","published","checked_out"... [snip]
3,"FAQs","Frequently Asked Questions","key.jpg","content","left","From the list below ... [snip]

Can you explain why Jix is erroring on fields such as 'id' and 'title'?

I think the path is for me to look at both my exported (1.5.0 beta) sections and create new jix_fields_044.csv which reflects both versions, *or* to pass my exported cvs through an awk script to re-map it.

[PtrNrs]

Thanks for your feedback, sdclee.

Which version of Jix are you using? The latest V 0.014 is available at http://www.jix.com.au. I believe I have fixed the Call-time pass-by-reference problem in V0.014.

Yes, you're right about the file suffix. *_044.csv is comma delimited, *_124.csv is vertical bar or pipe delimited. Maybe CSV is no longer a great choise of extension!

As for the import - this is the old case of the code preceding the documentation. Jix's exact column/field structure is dictated by the configuration of the table jos_jix_fields. You can see this in the Jix Fields tab. This will be the exact format that you export from Jix and ia also the expected import format.

In order to add extra fields you need to import those additional fields in to Jix Fields. It's technical and a little tricky - I'll have to write a tutorial about it.

Send me your CSV file and I'll have a look at it.

[sdclee]

Timezones, who'd have 'em eh?
I'll probably leave it until the weekend for another stab due to other commitments.
What would be really neat (I'm sure you have this in mind already) is to do a Jix from the ground up, ie without the pre-existing csv files, in order to give a deeper understanding of the 'way of jix'.
I really appreciate your work on both Jix and your forum contributions.
What you have achieved to date looks very promising indeed :-)

[PtrNrs]

I'll probably leave it until the weekend for another stab due to other commitments.

Over the weekend, I'll be busy launching Jix at the Melbourne JoomlaDay (http://melbourne.joomladay.org.au) - there's going to be quite a number of top dollar Joomla egg-heads there maybe even the convenor of this form Ssshhh! So I won't be so contactable. Your Sunday night (my Monday morning) might be the go. Try Skyping me.

What would be really neat (I'm sure you have this in mind already) is to do a Jix from the ground up, ie without the pre-existing csv files, in order to give a deeper understanding of the 'way of jix'.

I'm guessing from that that you got everything going . . .? Yes, that's definitely the idea. The key to Jix's power is in the three Jix tables themselves - it takes a little time to get your head around that concept. I need to do an advanced video to cover that, I think.

I only ask that if you do make progress, that you share it with the community.

I really appreciate your work on both Jix and your forum contributions.
What you have achieved to date looks very promising indeed :-)

Much appreciated. Can I clone you?

[sdclee]

I have Jix working ok though I have to find a complete solution to my 1.5.0 Beta upgrade.
Have a great launch weekend

[PtrNrs]

Jix has just been posted in the Joomla Extensions Directory (JED) at http://extensions.joomla.org/extensions ... 30/details.

I welcome your feedback both here and on the JED page.

[kenmcd]

.
Pete,

As you are trying to build a following and a testing group for your new extension,
I would highly recommend you dispense with the required registration to download Jix.

Requiring registration will severely limit the number of downloads and users.
Right now this will only hamper the number of testers and the amount of feedback you receive.

Jix may fulfill a very often requested Joomla user need - easy and powerful content import.
So I wish you success.

Regards,

KM

.

[yellowdog]

Best of luck with this. I needed just something like this a while back.
If you need any testing please pm me.

[PtrNrs]

Ken, thanks for the suggestion about registration. I'm pretty green with this phase of the project and I'm keen to receive input from all.

I'm at the Melbourne JoomlaDay this weekend with a bunch of top dollar Joomla egg-heads (that's a compliment!), so I'll toss the issue around and my future appoach will probably be the sum total of your (plural) gathered wisdom.

In the meantime, I've removed the rego.

[PtrNrs]

I've just spent 4 hours kneeling at the feet of Andrew Eddie at the Melbourne Joomladay.

Newly inspired, I'll be making some changes to Jix in the coming week.

[mscruse]

I saw Jix at Joomla Day in Melbourne and it's just what I need to import 200 users into Joomla. The only problem I have come across is that the passwords don't work.

My clients wants usernames and passwords for their users to be the same as their old site. The supplied a xls file of user details (including passwords). I put it into the correct format for import, and imported them successfully, however the passwords don't work - I can't login as any of the users.

I'm using Joomla 1.5.9. Any ideas?

Michael.

[gamamblue]

Looks promising!

For what it's worth, I recently released a similar extention for importing article content, using csv, pipe, or other delimeters:

Bulk Import

[kenmcd]

.

I saw Jix at Joomla Day in Melbourne and it's just what I need to import 200 users into Joomla. The only problem I have come across is that the passwords don't work.

My clients wants usernames and passwords for their users to be the same as their old site. The supplied a xls file of user details (including passwords). I put it into the correct format for import, and imported them successfully, however the passwords don't work - I can't login as any of the users.

I'm using Joomla 1.5.9. Any ideas?

Michael.

Were the passwords already encoded? (probably were)
Jix 0.014 is automatically encoding any password text when imported.
So it is expecting a plain text password.

Userport has an option to not encode (or re-encode) the imported passwords.
http://extensions.joomla.org/extensions ... 30/details
This may work if the current password encoding is compatible with Joomla.

.

[kenmcd]

.

Looks promising!

For what it's worth, I recently released a similar extention for importing article content, using csv, pipe, or other delimeters:

Bulk Import

I have been watching Mass Import to see when bulk import from one file was added.
So good to see you have added this needed feature.

BUT, this is the Jix support thread and not the proper place to discuss this.
Please start a new thread for Bulk Import. (you need a support thread somewhere anyway)
.

[PtrNrs]

Michael, did Ken's assistance help? If you were importing the encoded passwords, Ken's advice was correct.

Maybe I should add an extra column for encoded passwords so you can either enter them either as plain text or encoded. What do you think?

[PtrNrs]

Michael, did Ken's assistance help? If you were importing the encoded passwords, Ken's advice was correct.

Maybe I should add an extra column for encoded passwords so you can either enter them either as plain text or encoded. What do you think?

. . . And Ken, thanks for protecting my thread!

[kenmcd]

.
Hi Pete,

Yes, you will need a way for users to import either text passwords or encoded passwords.
Users often want to export from an old J1.0 site (or sites) and import to a new J1.5 site.
So they will be importing already encoded passwords.
This is why the various user import tools over the years have added this import switch.

Most existing sites of any kind will have encoded passwords so this will be an issue.


Would you like to add me to the Jix beta testers?
I have a user account at the Jix site.

Regards,

KM
.

[mscruse]

Hi Pete/Ken

I ended up finding and using Userport and it worked perfectly.

The passwords I was importing were text and not encoded.

Cheers
Michael.

[PtrNrs]

Michael, I've just checked it out again and it works OK for me.

Is it possible you entered the Name rather than the Username into the login form?

Would you mind sending a test file (with confidential data suitably obscured of course) to [email protected] so I can check it out?

I appreciate you feedback. Thanks very much.

[PtrNrs]

.
Yes, you will need a way for users to import either text passwords or encoded passwords.
Users often want to export from an old J1.0 site (or sites) and import to a new J1.5 site.
So they will be importing already encoded passwords.
This is why the various user import tools over the years have added this import switch.

Most existing sites of any kind will have encoded passwords so this will be an issue.

Thanks, Ken. I'll add it to my outstanding work list.

[rich168]

Pete,

your Jix is great. I make use of it to import 9000+ articles to my new website.

But i have this problem when I was trying to cross 10k mark.

I am at the content screen, then i got this message before I click on anything:

Error Loading ModulesMySQL server has gone away SQL=SELECT id, title, module, position, content, showtitle, control, params FROM jos_modules AS m LEFT JOIN jos_modules_menu AS mm ON mm.moduleid = m.id WHERE m.published = 1 AND m.access <= 2 AND m.client_id = 1 ORDER BY position, ordering

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/singapo2/public_html/myarticlesdb/libraries/joomla/database/database/mysql.php on line 221

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/singapo2/public_html/myarticlesdb/libraries/joomla/database/database/mysql.php on line 342

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/singapo2/public_html/myarticlesdb/libraries/joomla/database/database/mysql.php on line 342

Do you have any ideas what goes wrong?

[PtrNrs]

Brilliant, Rich.

I see it's your first post. Welcome to the forum!

I haven't pushed Jix any where near that hard! One of the problems I expected to have to confront was what happened with big number of imports.

The errors you report are not in Jix but the base problem is no doubt triggered by Jix. However Jix has no hard limit on the import size. I rather expected to hear about time out problems, though.

One consideration is that the version you're using (V0.014?) just uses comma delimiting which can cause problems if there are commas embedded in your articles.

Could you try to reverse the order of the lines in the CSV file. Does it break at a different location?

I'd love to follow this up. It be great if you could send me the import file (subject to confidentiality of course!) Please email me at [email protected].

Last thought. Have you registered on the Jix website? I'm looking for people who can collaborate in future development and particularly in testing of new versions.

[kenmcd]

.
@rich168

Error Loading Modules MySQL server has gone away . . .

This indicates the MySQL server connection was lost - probably due to how long the import was taking.
Then the script fails because there is no database connection.

If you have access to your MySQL server settings you can increase the related MySQL settings.

As a workaround you can split your import file into smaller chunks.


@Pete
The Joomla Migrator component (which also does big MySQL imports) works around this "server has gone away" issue by using modified code from the MySQL BigDump importer script.

Joomla Migrator component
http://extensions.joomla.org/extensions ... 23/details

BigDump: Staggered MySQL Dump Importer
http://www.ozerov.de/bigdump.php


This will be a ongoing issue with large imports as users' MySQL servers may timeout while Jix is working.
Perhaps you could add similar code to Jix.

KM

.

[rich168]

Pete,

Thanks for the respond.

Fyi, the error come out when I click on the content tab. Not when I click on the import. I believe your import logic is well done. Reason why I said that is I imported 1200+ articles at each time. And it take less than 2 seconds. I just wondering how you did that.

I believe the problem now lies with the mysql setting in my hosting server. Because you select all the content out and display it as pages in screen. During this selection, I probably hit some max size of query or total row limit of this hosting machine.

To work around on this problem, may be i have to export jos_content data out and truncate it. Then reimport again after I use the jix to import the rest of my content. But this is a bit more work to do.

Can I suggest that you have a global setting not to display the current jos_content table to prevent this problem?

Is there a place in your code that I can comment out the selection?

I am using the latest available version. I hv not registered yet but not a problem on the collaboration and helping on testing.

Thanks,

Rich

[rich168]

Ken,

You are right. But the problem appear before I click on import csv button. Meaning, the max query size problem is with the current page that display all the content out.

I am asking Pete to give an option not to display it out. I may hit 100k articles anytime soon.

Thanks,
Rich

[PtrNrs]

Rich, would you be able to send me your import file (subject, as I said before, to confidentiality) so I can reproduce the error.

Great suggestion, Ken. I never worked out how they did that. And JoomlaPack must do something similar. Thanks!

[rich168]

Pete, I am unable to send the import file due to some copy right reason. but you should be able to reproduce the error if you upload 10k of the article content up.

Rich

[rich168]

Pete,

I manage to solve the problem by commenting some codes on jix.php.

I am still unable to use the PM yet. will PM you when I can do that later.

Rich

[bwoester]

From http://www.jix.com.au/menu-item-documen ... delimiters:

For many Jix applications, CSV files are quite adequate. However, when there are commas embedded in the data, the use of CSV files fails since each comma is interpreted as a new field. Therefore, Jix offers you complete control over the delimiter used in your DSV files.

I'd suggest to use some mechanism of quoting characters to prevent these sort of problems. Guessing a character and hoping it won't be contained in the data you'd like to export simply isn't a choise. And since this is a very common problem, there should be a number of solutions out there.

From what I know, OpenOffice e.g. uses a quote character to enclose every data field and uses the same character to quote each such characters found in the input data.

Here is an example:

input data

Code: Select all

col1    col2             col3              col4
test    test "quotes"    test;delimiter    "test delimiter";"and quotes"

data as OpenOffice would save it

Code: Select all

"col1";"col2";"col3";"col4"
"test";"test ""quotes""";"test;delimiter";"""test delimiter"";""and quotes"""