Welcome, Guest
Username: Password: Remember me

TOPIC: [HELP GUIDE] Importing records

[HELP GUIDE] Importing records 23 Sep 2012 15:36 #3904

  • sdm7001
  • sdm7001's Avatar
  • Offline
  • Junior Member
  • Posts: 20
  • Karma: 0
I have several thousand records to import.

I see that there is no import feature in Cook ... is the easiest way to do this through the MySQL DB after the component is installed to Joomla?

Thank you,

SDM
The administrator has disabled public write access.

Re: Importing records 23 Sep 2012 16:04 #3912

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 984
  • Karma: 140
Not available yet.

Sorry.
Coding is now a piece of cake
The administrator has disabled public write access.
The following user(s) said Thank You: sdm7001

Re: Importing records 23 Sep 2012 16:11 #3915

  • sdm7001
  • sdm7001's Avatar
  • Offline
  • Junior Member
  • Posts: 20
  • Karma: 0
Is the easiest way to do this through the MySQL DB after the component is installed to Joomla?
The administrator has disabled public write access.

Re: Importing records 23 Sep 2012 16:21 #3916

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 64
Its the only way currently (locally).

I'd suggest reading the data from a csv file as its dead easy!

The other advantages of doing this is that you can;
  1. Transform the data before input
  2. custom validation & checking before input
  3. Make it dynamic in a function you could reuse for any of your tables by passing table name in via input field, pass the field names in the first line of your csv file or get them from array_keys() on your db

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.
The following user(s) said Thank You: sdm7001

Re: Importing records 23 Sep 2012 17:16 #3920

  • VeCrea
  • VeCrea's Avatar
  • Offline
  • Platinum Member
  • Absolute JCook fan
  • Posts: 473
  • Thank you received: 100
  • Karma: 30
Have been importing many times through php my admin, never had a problem
The administrator has disabled public write access.

Re: Importing records 27 Sep 2012 10:10 #4065

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 64
Something that I forgot to ask for clarification on...

Are you asking if there's a way to import records into the sandbox, or how to build it into your component - i.e. you want to create the functionality for your component to import records?

There are many security & performance implications of J-Cook allowing such a feature in the sandbox. Namely, the server resources getting slammed by many many users importing potentially millions of records at any time + the potential to do a lot of damage to the J-Cook server. This has been discussed previously here on the forum and understandably, admin's response to such a feature is NO!

If on the other hand, you want to look at the CSV option I mentioned earlier on in the topic, I will post a brief description/code on how to do it shortly.

This is something that you may be able to put in a helper script to call from your component as a function.

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.
The following user(s) said Thank You: admin

Re: Importing records 27 Sep 2012 12:13 #4072

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 64
OK, here it goes...

The following pre-supposes that your CSV file has been formed correctly, i.e. integers ARE NOT enclosed in quotations and 'strings' ARE.

It's strength is that an associative array is built out of the first line of the file which will allow you to easily transform/manipulate your input data. For example, say for instance, you have some fields "address1, address2, address3", all of which you want to insert into one field, with a little bit of if/else evaluation, they can easily be appended to a new var in the loop.

N.B. To achieve this, you will also have to overwrite the $keys var to account for this. This function could then be extended to take a third argument - an array of $newKeys - with which to build your new array (lines of csv) before they are imploded into a string of values to insert. Furthermore, you could make it optional by checking for it's existence at the top of the function, only overwriting it if it exists.

TO DO:
  1. Add the following to your component's helper file;
    function importCSVFile($file,$table)
    {
    	
    	//GET THE CSV FILE & CONTENTS
    	$csv = array_map("str_getcsv", file("$file.csv",FILE_SKIP_EMPTY_LINES));
    	$keys = array_shift($csv);
    	
    	//Make the data an associative array to target your data using $csv['your_field_name']
    	foreach ($csv as $i=>$row) {
    	    $csv[$i] = array_combine($keys, $row);
    	}
    	
    	/* ...NOTE ANY CUSTOM DATA MANIPULATION GOES HERE... */
    	
    		//Ensure that whatever new array you've assigned replaces $csv in the next foreach() loop
    		
    	/* ...END CUSTOM */
    
    	//Loop to prepare each row of vals inside parentheses 
    	foreach($csv as $row=>$value){
    			$rowVals[] = "(". implode(", ", $value) . ")" ;
    	}
    	
    	//Get the keys from the CSV to use as the field names in query
    	$keys = implode($keys, ", ");
    	
    	//Build the insert
    	$sql = "INSERT INTO $table ($keys) ";
    	$sql .= "VALUES ". implode(", ", $rowVals);
    
    	return $sql;
    }
  2. To call the function, simply pass the 2 arguments $file & $table (both strings) as follows;
    $query = importCSVFile("path_to/my_csv_file", "my_table");
    N.B.
    $file = file path with no extension (.csv added in function), $table = the name of the table
  3. To check your query,
    echo $query
  4. Run your query
    $db =& JFactory::getDBO();
    $db->setQuery($query);
    $db->query();
Hope it helps!!!

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
Last Edit: 27 Sep 2012 12:15 by JoomGuy.
The administrator has disabled public write access.
The following user(s) said Thank You: admin, VeCrea

Re: Importing records 27 Sep 2012 12:40 #4081

  • VeCrea
  • VeCrea's Avatar
  • Offline
  • Platinum Member
  • Absolute JCook fan
  • Posts: 473
  • Thank you received: 100
  • Karma: 30
Man... ;) k++
Now the same thing for exporting csv please ;-)
Thanks ;)
Last Edit: 27 Sep 2012 12:41 by VeCrea.
The administrator has disabled public write access.

Re: Importing records 27 Sep 2012 12:47 #4084

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 984
  • Karma: 140
Now the same thing for exporting csv please

MDR !!!
:lol: :lol: :lol: :lol:

VeCrea can understand the joke.
Coding is now a piece of cake
The administrator has disabled public write access.

Re: Importing records 27 Sep 2012 12:48 #4085

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 64
I haven't actually done that ever!

Honestly, it shouldn't be too difficult - importing is supposed to be a lot more difficult and I'm no expert by any stretch of the imagination. Let me take a look!

Thanks for the Karma!

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.

Re: Importing records 27 Sep 2012 13:46 #4093

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 64
Just a bit of testing and a function to export CSV to file with or without attachment (download) will exist very soon!
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.
The following user(s) said Thank You: admin

Re: Expoerting records to CSV 27 Sep 2012 16:02 #4099

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 64
READY!!! Here we go...

Similar to the export script I provided, this could be added to your helper functions

I've taken this a bit further in that a few more parameters/args can be passed to grab certain fields, add a where clause, overwrite the file name by appending the time() function, auto download the file and grab the field names as headers or not...

Here's the script:
function exportCSVFile($file, $table, $fields = false, $where = false, $overwrite = false, $download = true, $headers = true) {
	//If Overwrite False, check if the file exists and make unique
	if(!$overwrite){
		if(file_exists($file)) { 
			$file = $file . "_" . time() . '.csv'; 
		}
	}
	
	// Check if download required
	if($download) {
        // send response headers to the browser
        header( 'Content-Type: text/csv' );
        header( 'Content-Disposition: attachment;filename='.$file);
        $fp = fopen('php://output', 'w');
    } else {
        $fp = fopen($file, 'w');
    }
    
	//The Query
	// Have fields been passed?
	if(!$fields) {
		$fields = "*";
	}
	// Has where clause been passed?
	if(!$where) {
		$where = "";
	}else{
		$where = "WHERE " . $where;
	}
	
	$query = "SELECT $fields FROM $table $where";
	
	$db = JFactory::getDBO();
	$db->setQuery($query);
    $result = $db->query($query);
   
    if($headers) {
        // output header row (if at least one row exists)
        $row = mysql_fetch_assoc($result);
        if($row) {
            fputcsv($fp, array_keys($row));
            // reset pointer back to beginning
            mysql_data_seek($result, 0);
        }
    }
   
    while($row = mysql_fetch_assoc($result)) {
	
			$line = "";
		    $comma = "";
		    foreach($row as $value) { //Escape quotes properly
		        $line .= $comma . '"' . str_replace('"', '""', $value) . '"';
		        $comma = ",";
		    }
		    $line .= "\n";
		    fputs($fp, $line);
    }
   	//Close the file
    fclose($fp);     
}
To call the function...
$export = exportCSVFile("your_filename.csv", "table_name", false, "WHERE your field = true");
The args
You must pass at least 2 arguments (the first 2) - $file & $table.
Arguments 3, 4, 5, 6 & 7 are optional as they are set to default values in the function.
  • $fields can be passed a comma separated list of fields from the $table - example "field1, field2, field3"
  • $where can be passed a valid where clause - example "id > 100" or "id >10 AND published = 1"
  • $overwrite can be passed a true or false - true will always overwrite the filename passed in arg1 if it exists, false (default) will append an underscore "_" and the current time() if the file exists already.
  • $download can be set true (default) or false and will force a download of the file as opposed to storing it
  • $headers allows you to auto grab field names for the first line of the file - set this = true or false.
If you want to pass any non-required arg, naturally, you will have to pass any consecutive args that come after 2 until you reach the arg(s) you wish to pass.

Hope this helps!

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
Last Edit: 27 Sep 2012 19:27 by JoomGuy.
The administrator has disabled public write access.
The following user(s) said Thank You: VeCrea

Re: Expoerting records to CSV 27 Sep 2012 19:52 #4103

  • VeCrea
  • VeCrea's Avatar
  • Offline
  • Platinum Member
  • Absolute JCook fan
  • Posts: 473
  • Thank you received: 100
  • Karma: 30
Will stay up all night trying this. would be soooooo perfect
The administrator has disabled public write access.

Re: Expoerting records to CSV 27 Sep 2012 19:55 #4104

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 64
It worked first time - UNBELIEVABLE!

One thing to note - currently, if you pass in field names that don't exist, the SQL errors get printed in the CSV output.

Gez
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.

Re: Expoerting records to CSV 27 Sep 2012 21:01 #4105

  • JoomGuy
  • JoomGuy's Avatar
  • Offline
  • Moderator
  • Joomla Enthusiast, Lover of Cooking
  • Posts: 1115
  • Thank you received: 195
  • Karma: 64
Got it working ok?
Need help with your Cook/Joomla Project? . PM me to find out what I can help with. NO time wasters please!!!
The administrator has disabled public write access.
Time to create page: 0.133 seconds

Get Started