Jix: Import, Export & Update Utility for Joomla

PtrNrs · **Posted:** Fri Mar 20, 2009 9:59 pm

bwoester wrote:

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.

Thanks for your contribution, bwoester.

I'm using the PHP function fgetcsv (http://au2.php.net/fgetcsv) - so I'm bound by the strengths &/ or weaknesses of that function. I'm afraid I'm not planning to write a custom function to replace it.

The new version soon to be released (I've been promising that for a while!) will allow you to delimit with any ASCII character - may be a neat way of sidestepping this problem.

Pete

bwoester · **Joined:** Fri Mar 20, 2009 12:12 pm **Posts:** 3

Oh, thanks for your fast reply!

I just did a quick test for this function with the example data I've posted before. For me it works perfectly:

(The code is from php.net, I only changed the delimiter and the enclosure parameter to fit my test data)

Code:

$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ";", '"')) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
    }
}
fclose($handle);

Feeded with an text.csv containing the following data:

Code:

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

Returned the following output:

Code:

4 fields in line 1:
col1
col2
col3
col4

4 fields in line 2:
test
test "quotes"
test;delimiter
"test delimiter";"and quotes"

Exactly what one would expect the data to be.

Does this mean the problem you described with commas within the data only occurs if the importer is feeded with ill-formed csv?

PtrNrs · **Posted:** Sun Mar 22, 2009 5:53 am

bwoester wrote:

Oh, thanks for your fast reply!

You're very welcome. I'm still in the stage where I'm rather excited seeing people using my component

bwoester wrote:

Exactly what one would expect the data to be.

Does this mean the problem you described with commas within the data only occurs if the importer is feeded with ill-formed csv?

I think I'd expect the behaviour you've observed.

However, I want this to work using the CSV (or more correctly DSV) files "straight out of the box" from Open Office (since Excel proves to be a bit of a dud!). I don't want people to have to trick around with the file in text mode.

Therefore, when I saw a problem importing comma separated files (and I confess that I haven't investigated it in detail), I set about creating a way to side step that by allowing the use of other delimiters.

Flash68 · **Joined:** Fri Mar 27, 2009 9:36 pm **Posts:** 4

I'm experimenting with Jix as I need to import over 6000 articles into a new Joomla site and Jix seems like the perfect tool for the job.

I'm not sure why but the sample import data provided for the Jix fields table only has intro text for content not full text. I changed the import file for Jix fields to also import the 'full text' field for content (defined just like intro text). This imported correctly except that the 'full text' field appeared at the end of the content fields (instead on next to intro text which is where I had added it).

I then modified the content import file to add the new data for the 'full text' field. I added it as the last field to match up with the definition in Jix fields.

However, when I tried to import the file, every line failed. I'm not sure if it's the reason for the failure but the error message showed that the new 'full text' field did not have quotes around it.

e.g.

Failed: INSERT INTO `jos_content` (`title`, `alias`, `introtext`, `sectionid`, `catid`, `full`) VALUES ('Can Joomla! 1.5 operate with PHP Safe Mode On?', 'can-joomla-15-operate-with-php-safe-mode-on', 'This is the intro', 7, 36, This is the Full Text);

I must be missing something. Any ideas would be appreciated.

davidkazuhiro · **Joined:** Sat Mar 21, 2009 9:55 pm **Posts:** 11

Right now jix saves everything in quotes, but openoffice scalc and ms excel have different behaviour.

ms excel strips all quotes

openoffice scalc strips quotes if the value is an integer.

This means that I can't use openoffice scalc to edit jix csv files, since jix will fail to import if it does not have quotes around each value.

Ultimately I'm wanting to be able to import microsoft outlook exported contact csv files into my Joomla contacts. I'm not concerned with linking them to users. Is jix the right tool for this, is there something more suited, or would I be better off writing my own extension?

------
Open Office 2.4
Excel 2007

Flash68 · **Joined:** Fri Mar 27, 2009 9:36 pm **Posts:** 4

That's not it I edited my sample file with notepad and ftp'ed. If I ftp back I still see the quotes. Plus it's only happening with the new 'full text' field, the others keep their quotes.

Flash68 · **Joined:** Fri Mar 27, 2009 9:36 pm **Posts:** 4

I've solved the problem of the added 'full text' field (on the content table) not importing. In the database it's entry for JixfDataType was blank. I added 'mediumtext' into it and everything imported correctly.

I think the problem was that when I first imported my amended jix fields file I had wrongly called the full text field 'full' instead of 'fulltext'. I assume Jix picks up JixDataType automatically but did not seem to be able to change it once I had named the field correctly and re-imported.

davidkazuhiro · **Joined:** Sat Mar 21, 2009 9:55 pm **Posts:** 11

My comment was a question not an answer, sorry. I'm glad Flash68 resolved his issue though =). So any thoughts on my comment above regarding contact importing? Is jix the way to go or should I use something else?

PtrNrs · **Posted:** Mon Mar 30, 2009 10:54 pm

Flash68 & davidkazuhiro,
Thanks for your contributions to this forum.

I have overcome the last barrier preventing me releasing the new version of Jix.

Please consider registering on www.jix.com.au to receive notification of the release.

davidkazuhiro · **Joined:** Sat Mar 21, 2009 9:55 pm **Posts:** 11

Hello PtrNrs,

I have registered on your website. Will your next release enable jix to import Microsoft Outlook exported csv files into Jooma's contacts?

PtrNrs · **Posted:** Tue Mar 31, 2009 4:01 am

Hi David,

Thanks for registering.

I'm not acquainted with the format of Outlook CSVs. However, as long as the CSV file matches column for column with that for a particular table in Jix Fields, you'll be able to import it even with the current version.

If you have any problems send me an email.

johnbljr · **Joined:** Fri Mar 20, 2009 5:55 am **Posts:** 8

I have a question.
I just downloaded Jix and installed it on my localhost site.
The problem that I am having is when I export the user list to CSV and then add a new user then save and reimport all it imports is the Pete Nurse user.
The odd thing is that this user is not even in the list.

Any ideas on how I can get this to work properly?
I installed Open Office and I am running Win XP SP2, Joomla 1.5.10.

Any help would be appreciated.

davidkazuhiro · **Joined:** Sat Mar 21, 2009 9:55 pm **Posts:** 11

When you save the file, make sure you save it in the "import" folder and not the "export" folder. Jix imports from the "import" folder and exports to the "export" folder.

johnbljr · **Joined:** Fri Mar 20, 2009 5:55 am **Posts:** 8

davidkazuhiro wrote:

When you save the file, make sure you save it in the "import" folder and not the "export" folder. Jix imports from the "import" folder and exports to the "export" folder.

Perfect! That was it - Thanks!

johnbljr · **Joined:** Fri Mar 20, 2009 5:55 am **Posts:** 8

Here is another question.
Are the ID numbers in the Users tab and the Contact Details tab supposed to match up?

PtrNrs · **Posted:** Wed Apr 01, 2009 6:33 am

Thanks, David. I should pay you a retainer!

John, the IDs are assigned automatically on creation of new records, There is no relation between id in Users and Contact Details - these are assigned automatically when you create a new record.

You just need to make sure that User ID in Contact Details matches the related User Name in Users.

johnbljr · **Joined:** Fri Mar 20, 2009 5:55 am **Posts:** 8

PtrNrs wrote:

Thanks, David. I should pay you a retainer!

John, the IDs are assigned automatically on creation of new records, There is no relation between id in Users and Contact Details - these are assigned automatically when you create a new record.

You just need to make sure that User ID in Contact Details matches the related User Name in Users.

OK so what about updating the Contact Details. I attempted to do this and it created duplicate records once I exported again.

davidkazuhiro · **Joined:** Sat Mar 21, 2009 9:55 pm **Posts:** 11

Jix will create brand new records of contact details unless you specify in the ID field which record you are updating.

So if one record is of John Doe with ID 1, then change the ID field in the CSV from blank to 1

PtrNrs · **Posted:** Wed Apr 01, 2009 8:30 pm

Actually the version you guys are using inserts (ie appends) records only rather than updating records.

The new version will update exising records as well.

johnbljr · **Joined:** Fri Mar 20, 2009 5:55 am **Posts:** 8

PtrNrs wrote:

Actually the version you guys are using inserts (ie appends) records only rather than updating records.

The new version will update exising records as well.

Do we have an ETA on the new version Peter?
Also what affect will the inserting (appending) of records have? Will I notice any difference in the contact info?

davidkazuhiro · **Joined:** Sat Mar 21, 2009 9:55 pm **Posts:** 11

oops my bad. Apparently I was getting ahead of myself there =P.

mdiep · **Joined:** Sun Apr 12, 2009 2:58 am **Posts:** 1

Hi!.. im making an online local phone guide, and i decide to do it as contacts, classified in categories (in fact the letter A,B, C etc).. i have a local DB with the data im gonna use (name, last name, street, number, phone number, fax) and i only have one issue.. i import correctly everything (5000+) but all of my contacts r uncategorized... i create a few with the right category and export those, but a cannot make that field to be exported.. neither of course imported (Category field).. how con i solved this? i dont want to change the category of each contact manually.. thx in advance

Turko
Argentina

PtrNrs · **Posted:** Mon Apr 20, 2009 6:28 am

¡Buenas dias, Turko!

Sorry I misssed your post. The latest version of Jix (V0.020) includes some Jix config files which now includes the category field.

But with Jix, you can change the configuration files (I'm talking about jix_fields_044.csv here) and reimport whenever you wnat.

Good luck with that!

kenmcd · **Posted:** Mon Apr 20, 2009 8:28 am

.
Hi Pete,

I see your download page now also has a Joomla plugin.
"Also now available Jix's new plugin, Jix User Field Replace."
http://www.jix.com.au/images/jix_3453456/jix_user_field_replace_0002.zip

What does this new Joomla plugin do?
And what is the proper format to use in content?

Thanks.

KM

.

PtrNrs · **Posted:** Mon Apr 20, 2009 10:05 am

Ken, we're getting a bit of Jix proper here. I was prevailed on by my Joomla Users' Group to put that link up and I forgot to remove it.

Anyway, it's currently set up to extract user information from the database (I only configured it for jos_users & jos_comprofiler from Community Builder).

The format to be used is "{jix_tblname_fieldname}" as in the following example:-

Code:

Hello {jix_comprofiler_firstname} {jix_comprofiler_lastname} of {jix_comprofiler_cb_streetaddress}, {jix_comprofiler_cb_streetsuburb}, {jix_comprofiler_cb_streetstate}, {jix_comprofiler_cb_streetpostcode} ({jix_users_email})

Please feel free to use it as you wish! It could quite easily be extended to other tables.

BTW, it uses the old (superseded) style - the purists wouldn't approve!

Another thought: What I didn't realise is that I was implementing a very primitive version of CCK and I didn't even know what it was!

moustik510 · **Joined:** Thu Apr 23, 2009 8:02 am **Posts:** 4

Jix is a great extension and it's exactly what I need.
I imported some contents for test but the creation date is wrong.

In my import file the creation date is : 2008-06-26 16:49:11
but in the created article the creation date is : 1970-01-01 01:33:28

Of course I added the date field in Jix Fields table.

Where is my mistake ?
Thanks.

[EDIT: with a timestamp it works so I have to convert my date 2008-06-26 16:49:11 in timestamp]

(Sorry for my English :-[

)

PtrNrs · **Posted:** Thu Apr 23, 2009 10:58 am

I'm not sure if you fixed the problem yourself however, if you've still got a problem, you could try setting Verbose Import option to Yes and see what you get.

moustik510 · **Joined:** Thu Apr 23, 2009 8:02 am **Posts:** 4

Yes I solved the problem by using a timestamp instead of a string 'YYYY-MM-DD HH:MM:SS' !
Great now my categories and content items are imported.

Now I have to find a free comment extension (maybe !JoomlaComment) and then to import my comments.

Good Bye B2Evolution, Hello Joomla

For your PM> I can't use PM for the moment : "You need at least 5 posts before you can use the PM send feature."
I'm a beginner in Joomla so I never make component translation. With some explanation I agree to make the french translation

PtrNrs · **Posted:** Thu Apr 23, 2009 9:17 pm

Thanks for the response, Moustik510. Let me extend this into a general request to the Jix community (small though it is!).

Jix Translators Needed!

If you've used Jix, please help out by editing or updating Jix language files. Here's how:-

In the Jix install file, com_jix_0020.zip, the folder administrator\language contains folders of the form xx-YY (language xx as spoken in country YY) each containing one language file of the form xx-YY.com_jix.ini.

Just create/update a translation file in your language (base your work on en-GB.com_jix.ini as this is the most up-to-date).

To test your new translation file xx-YY.com_jix.ini, copy it to the administrator/language/xx-YY folder of your website.

Then please send it to me at at pete@jix.com.au and I'll incorporate it in the next release of Jix.

Thank you!

moustik510 · **Joined:** Thu Apr 23, 2009 8:02 am **Posts:** 4

PtrNrs wrote:

Jix Translators Neeeded!

Ok quite easy !
I'm creating the french translation

Joomla! Discussion Forums

Forum rules

Jix: Import, Export & Update Utility for Joomla

Quick reply

Who is online