Topic: [How to] SOBI2 and importing data

[inspired]

Well, this thread has been around for over a year now. It has been read over 12200 times.
Am I correct to assume that this is still no end-user friendly import/export functionality in SOBI2?

Is this a planned feature? It seems like such a fundamental feature to include. There must be many potential SOBI users who have existing data. There must also be many potential and existing SOBI users who want to know they can easily export their data for possible migration to another system or for working with in a third party application, and are then able to import it back in again.

What's the current status of this import/export issue?

With much thanks,

Jonathan

[Philip Roy]

Neo, trying not to get in the middle of what looks like an argument, but I too have been monitoring this thread hoping that there would be a solution. I would love to use SOBI to replace my use of the mosDirectory component, but the only thread I can find on it talks of a possible solution and someone working on wrtiting it up, only for that not to have eventuated.

The easiest solution for me would be an import feature, so that I can get the data into SOBI and move away from mosDirectory. I have tried, but have got myself very confused in trying to get the component working.

Cheers, Phil

[cutgut1967]

I have connected to my Sobi tables using MyODBC for MS Access. Is there a map of the table relations to aide in importing my data.

Also has anyone had a large amount of listing and any slow down in performance???

Thanks for the Great Product!!!!

[charlynancy]

Hi,

Is there a map of the table relations to aide in importing my data

No, because we don't know 2 things :

• your data organization in Access
• your customised field in Sobi

I advice you to start a new topic with your specific data organisation
I believe that i already make a topic to say that importing data in sobi CAN NOT BE UNIVERSAL because of this 2 points.

However, here is some informations that can help you :
*Items and their main data  (name of the item, date publishing, approved, metakey... ) are in :
jos_sobi2_item

*Data of the item that are in your field (personalised or standart ) are in :
jos_sobi2_fields_data

*Relation between item and categories are in :
jos_sobi2_cat_items_relations

You have to look at 2 things in you sobi data base :

• In jos_sobi2_item , search the number of the itemid that is the more important, so you will increase after this number each data you import
• In each category where you import data, look at the last number of order, so you will increase after this  last number

i advice you to start step by step, so you can test your own process.
When all is OK you can import your thousand data

Finally :

a large amount of listing and any slow down in performance

It's depending on you memory server and on the average size of your item (just phone, adress, mail , short description or big description, many customised    fields...)

Good luck! the power is in your head...

[e_tietze]

Finally figured it out! Simple really!

If you have the above error, check out your reference to your table on the following line:

// Get the records from transition table
$mydata = mysql_query("SELECT * FROM excel.table", $link);

Remember that when calling to another database when connected to your joomla database, you need to make an absolute reference to the database as follows:

// Get the records from transition table
$mydata = mysql_query("SELECT * FROM root_excel.table", $link);
Where root is usually your username for cpanel/phpmyadmin.

I expect this problem only occurs for those who access all these features via PHPMyAdmin.

I hope this helps and I expect that this tutorial would be good as a sticky or FAQ as it works perfectly if you follow (the confusing) instructions point by point.

Thanks
Alex

OK, please bear with me, I'm new to all of this and just kind of fumbling my way through.  I've used the script mentioned in this forum, made the changes you noted above, and I'm still getting this error:

Connected successfully

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /hermes/web09/b2318/moo.ozarks2/content/import.php on line 16
Done!

Here is a copy of the script as I've modified for my fields and such.

<?php
$link = mysql_connect('localhost', 'id', 'password');
if (!$link) {
   die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br>';
mysql_select_db("farmhand", $link);

// Get the records from transition table
$mydata = mysql_query("SELECT * FROM root_farmhand.farmhand", $link);

//set the counter for the last query
$i=0;

//Add record jos_sobi2_item table
while ($row = mysql_fetch_assoc($mydata)) {
   //insert a record into the item table
   $results = mysql_query("INSERT INTO jos_sobi2_item (title) VALUES ('".$row['Company']."')", $link);
   
   //get the last item itemid
   $itemid = mysql_query("SELECT MAX(itemid) FROM jos_sobi2_item", $link);
   $row_itemid = mysql_fetch_row($itemid);

   //now build a record for the fields data

      $result1 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('1', '".$row['Phone']."', '".$row_itemid[0]."')");
      $result2 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('2', '".$row['Fax']."', '".$row_itemid[0]."')");
      $result3 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('3', '".$row['First_Name']."', '".$row_itemid[0]."')");
      $result4 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('4', '".$row['Last_Name']."', '".$row_itemid[0]."')");
      $result5 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('5', '".$row['Email']."', '".$row_itemid[0]."')");
      $result6 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('6', '".$row['Website']."', '".$row_itemid[0]."')");
      $result7 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('7', '".$row['Street']."', '".$row_itemid[0]."')");
      $result8 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('8', '".$row['City']."', '".$row_itemid[0]."')");
      $result9 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('9', '".$row['State']."', '".$row_itemid[0]."')");
      $result10 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('10', '".$row['Zip']."', '".$row_itemid[0]."')");
      $result11 = mysql_query("INSERT INTO jos_sobi2_fields_data (fieldid, data_txt, itemid) VALUES ('11', '".$row['Desc']."', '".$row_itemid[0]."')");
     
      // update the cat_items_relations table
      $result14 = mysql_query("INSERT INTO jos_sobi2_cat_items_relations (catid, itemid, ordering) VALUES ('3', '".$row_itemid[0]."', '".$i."')");
      $i++;

      //now update so the items are published and data is good     
      $result15 = mysql_query("UPDATE jos_sobi2_item SET published='1', confirm='0', approved='1', archived='0', publish_up=NOW(), publish_down='2050-12-12 00:00:00', checked_out='0', owner='62', ip='127.0.0.1'");

     
}

echo "Done!";
mysql_close($link);
?>