Import data?

Hello,

I have a new Joomla website for a sporting club with about 2500 members. At the moment the membership is maintained in another application (which I'm going to change eventually), but I would like for now, to display membership lists in the new site.

Is there a way in ChronoConnectivity and/or Chronoforms to create a bulk import/update process so that I can upload a .csv file or similar containing member details, into tables in the Joomla database?

I would like to set this up so that it can be done by office staff without IT expertise.. for example they export a csv file from the old application, then perform a Browse/File Upload style import of data which synchronises the two?

Using Joomla 2.5 CF 4.0 CC 4.0

Cheers

Tim

Hi Tim,

Nothing out of the box, you will have to write the PHP code to parse this CSV file and use it into a Chronoforms action if you like, which can then be used by your form.

Regards,
Max

Hi Tim,

Sorry, I missed this earlier :-(

I recently built a ChronoForm that uploads a CSV file into a database table. It's not very complicated, a file upload plus a Custom Code action that loops through the file records. I can probably put the code here if that would help.

Bob

Hi, Bob.

I'm waiting for your magic...

Please, I need the code to read under Joomla framework a "simple"😈 csv file and create one record by each line of its contents!

Best regards,

Hi neuronageek,

Here it is:

<?php
// get the path to the uploaded file
jimport('joomla.filesystem.file');
$file =& $form->data['_PLUGINS_']['upload_files']['csv_upload']['path'];
if ( !JFile::exists($file) ) {
  echo "Unable to find the file";
  // Report error
  return false;
}
// open the file and read the data
$handle = fopen($file, 'r');
$data = array();
while ( ( $data[] = fgetcsv($handle, 1000, ",")) !== FALSE ) {
}
fclose($handle);
if ( count($data) <= 0 ) {
  echo "data file is empty";
  return false;
}
//echo'<div>$data: '.print_r($data, true).'</div>';
$defaultUserGroup = array(2, 9);
$user =& JFactory::getUser();
$db =& JFactory::getDBO();

// validate the data, probably the first and last row are not real data
$results = array();
foreach ( $data as $k => $v ) {
  // check if routing number is set and numeric
  $route_number = trim($v[0]);
  if ( is_numeric($route_number) === false ) {
    $results[$k] = "Route#: {$v[0]} Failed: non-numeric route number";
    unset($data[$k]);
    continue;
  }
  // check if email is set and valid
  $email = trim($v[1]);
  $email = filter_var( $email, FILTER_SANITIZE_EMAIL );
  if ( !filter_var( $email, FILTER_VALIDATE_EMAIL ) ) {
    $results[$k] = "Route#: {$v[0]} Failed: invalid email address";
    unset($data[$k]);
    continue;
  }
  // we have a valid row

// the next block of code registers a user based on the uploaded row
// you probably don't need this but I've left it in for reference
  // register user
  //set the post fields values
  $user_data['name'] = $route_number;
  $user_data['username'] = $route_number;
  $user_data['email'] = $email;
  //generate the random pass if enabled
  jimport('joomla.user.helper');
  $random_pass = JUserHelper::genRandomPassword();
  $user_data['password'] = $random_pass;
  $user_data['password2'] = $random_pass;

  $new_user = clone(JFactory::getUser());
  // Bind the post array to the user object
  if ( !$new_user->bind($user_data, 'usertype') ) {
    $results[$k] = "Route#: {$v[0]} Failed: {$new_user->getError()}";
    continue;
  }

  // Set some initial user values
  $new_user->set('id', 0);
  $new_user->set('usertype', 'deprecated');
  $new_user->set('groups', $defaultUserGroup);

  $date = JFactory::getDate();
  $new_user->set('registerDate', $date->toMySQL());
  //echo'<div>$new_user: '.print_r($new_user, true).'</div>';
  if ( !$new_user->save() ) {
    $results[$k] = "Route#: {$v[0]} Failed: {$new_user->getError()}";
    continue;
  }

  //echo'<div>$new_user: '.print_r($new_user, true).'</div>';
  $results[$k] = "Route#: {$v[0]} New User: name: {$new_user->username} pw: {$new_user->password_clear} id: {$new_user->id}";

// end user creation

  // set extra values for db table
  $values = array(
    '0' => '', // cf_uid
    '1' => date('Y-m-d H:i:s'), // cf_created
    '2' => date('Y-m-d H:i:s'), // cf_modified
    '3' => $user->id, // cf_created_by
    '4' => $user->id, // cf_modified_by
    '5' => '', // cf_ipaddress
    '6' => $new_user->id // cf_user_id
  );

  // process some of the data before saving
  foreach ( $v as $kk => $vv ) {
    //echo'<div>$v: '.print_r($v, true).'</div>';
    if ( $kk == 1 ) {
      $temp = explode('#', $vv, 2);
      $temp = array_pad($temp, 2, '');
      //echo'<div>$temp: '.print_r($temp, true).'</div>';
      $values[] = $temp[0];
      $values[] = $temp[1];
    } else {
      $values[] = $vv;
    }
  }
  //echo'<div>$values: '.print_r($values, true).'</div>';

  // db quote the values
  foreach ( $values as $kk => $vv ) {
    $values[$kk] = $db->quote($vv);
  }
  //echo'<div>$values: '.print_r($values, true).'</div>';
  $values = implode(', ', $values);
  //echo'<div>$values: '.print_r($values, true).'</div>';

  // save the data
  $query = "
INSERT INTO `#__chronoforms_data_some_table`
(`cf_uid`, `cf_created`, `cf_modified`, `cf_created_by`, `cf_modified_by`, `cf_ipaddress`, `cf_user_id`, `route_number`, `email`, `alpha_name`, `business_number`, `address_number`, `address_1`, `address_2`, `city`, `state`, `postal_code`, `route_type`, `division`, `region`, `reg_description_1`, `dst`, `spv`, `spv_description_1`)
VALUES ({$values});
  ";
  //echo'<div>$query: '.print_r($query, true).'</div>';
  $db->setQuery($query);
  $db->query();
}
// output the results (one line per record) this confirms which rows have been added.
foreach ( $results as $rv ) {
  echo"<div>{$rv}</div>";
}
?>

I've added a few extra comments to the original code and have left in some debugging code (commented out).

This reads the whole file before processing which was OK in this case as the CSV files weren't too enormous (a few hundred records). If they were bigger you could read one line at a time, or a block of say 100 lines.

Bob

Amazing. I'm going to adapt ths routine to feed my "Bar" tables, taking care of your recommendations about internal keys on table.
But tomorrow... Now it's beer time.

Consider one is fom you.

Really, thank you.

I must apologise.. many things have happened and now I am back here.

OK... I tried the code and have it executing but I don't know much php and so I am stuck at the first line... how to select the filename.

My form is test_import, my file upload field name is input_file_1, the path is the default (components/com_chronoforms/uploads/test_import)

But when I try to feed tose parameters in it just gives the "Unable to find file" message.

OK I do understand that this will be trivial for some one who knows - but what do I place in the $file = line, so it will find and process the file?

Thanks

Tim

HI Tim,

You probably just need to replace csv_upload in my code with the name of the form input that you are using. (Or rename your input to csv_upload).

Bob

Thanks Bob,

My form name is "test_import" and the upload file field name is the default "input_file_1".

I tried replacing "csv_upload" with "input_file_1" - no success.

I tried replacing "upload files" with "test_import - no success.

I tried both changes - still nothing.

I guess I am not 100% sure which of your "upload_files" and "csv_upload is the form name and which is the field name.

In any case I have not been able to find a combination that works😟

Sorry - I know this will be simple.

Cheers

Hi Tim,

Please drag a Debugger action into the On Submit event, then submit the form and post the debug - including the 'dummy emails' results here.

Note: if you are using the Easy Wizard you can turn on Debug on the Others tab.

Bob

No emails actually, because I wanted only to import data into a table. I notice that the file upload field input_file_1 has nothing in it, yet I selected a csv file before submitting

Unable to find the fileData Array: 
Array
(
    [chronoform] => Test_Import
    [event] => submit
    [option] => com_chronoforms
    [Itemid] => 
    [input_file_1] => 
    [input_text_3] => 
    [input_submit_2] => Submit
    [99d9b30d6636d6b593d88bc3f213c74c] => 1
    [_PLUGINS_] => Array
        (
            [input_file_1] => Array
                (
                    [test_import] => Array
                        (
                            [path] => 
                        )

                )

        )

)
Validation Errors: 
Array

Hi Tim,

It looks as though the Upload Files action may not be set up correctly and, as a result, the file isn't being uploaded.

Bob

Yes! Progress is made, the File Upload action was not inside the On Submit.

When I put it there, some new things happened.. better things. But then,what is this "non-numeric route number?"

Route#: input_pilot_name Failed: non-numeric route number
Route#: Tim Shirley Failed: non-numeric route number
Route#: Bongo Drum Failed: non-numeric route number
Route#: Irish Flute Failed: non-numeric route number
Route#: Failed: non-numeric route number
Data Array:
Array
(
[chronoform] => Test_Import
[event] => submit
[option] => com_chronoforms
[Itemid] =>
[input_file_1] => 20140410211505_Test_Import.csv
[input_text_3] => aaaaa
[input_submit_2] => Submit
[205d05cb5d1b053fc2ef15b172fa4c39] => 1
[_PLUGINS_] => Array
(
[upload_files] => Array
(
[input_file_1] => Array
(
[name] => 20140410211505_Test_Import.csv
[original_name] => Test_Import.csv
[path] => /home/glidinga/public_html/components/com_chronoforms/uploads/Test_Import/20140410211505_Test_Import.csv
[size] => 84
[link] => http://glidingaustralia.org/components/com_chronoforms/uploads/Test_Import/20140410211505_Test_Import.csv
)

)

)

)
Validation Errors:
Array
(
)
Debug Data
Upload routine started for file upload by : input_file_1
/home/glidinga/public_html/components/com_chronoforms/uploads/Test_Import/20140410211505_Test_Import.csv has been uploaded successfully.

I should add, my import file is this:

input_pilot_name input_gfa_nbr
Tim Shirley E64
Bongo Drum M222
Irish Flute N555

and the custom code looks like this:

INSERT INTO `#__chronoforms_data_Badge_Declaration`
(`cf_uid`, `cf_created`, `cf_modified`, `cf_created_by`, `cf_modified_by`, `cf_ipaddress`, `cf_user_id`, `input_pilot_name`, `input_gfa_nbr`)

If this is the problem, how should I set up the cf_ variables?

Cheers

Tim

Hi Tim,

My guess is that the column in that table is set up to be numeric but the data you are entering e.g. M222 is a string - set that column to a type like VARCHAR(8) (or more that 8 if necessary). Also ' non-numeric route number' suggests that the column name may be set to 'route number'. If it is please change that to route_number with no spaces.

Bob

Hmm,

Sorry to be such a pain.

Both input_pilot_name and input_gfa_nbr are VARCHAR(255). There's no column "route number" or any variation thereof.

Here is the custom code I have now:

<?php
// get the path to the uploaded file
jimport('joomla.filesystem.file');
$file =& $form->data['_PLUGINS_']['upload_files']['input_file_1']['path'];
if ( !JFile::exists($file) ) {
echo "Unable to find the file";
// Report error
return false;
}
// open the file and read the data
$handle = fopen($file, 'r');
$data = array();
while ( ( $data[] = fgetcsv($handle, 1000, ",")) !== FALSE ) {
}
fclose($handle);
if ( count($data) <= 0 ) {
echo "data file is empty";
return false;
}
//echo'<div>$data: '.print_r($data, true).'</div>';
$defaultUserGroup = array(2, 9);
$user =& JFactory::getUser();
$db =& JFactory::getDBO();

// validate the data, probably the first and last row are not real data
$results = array();
foreach ( $data as $k => $v ) {
// check if routing number is set and numeric
$route_number = trim($v[0]);
if ( is_numeric($route_number) === false ) {
$results[$k] = "Route#: {$v[0]} Failed: non-numeric route number";
unset($data[$k]);
continue;
}
// check if email is set and valid
$email = trim($v[1]);
$email = filter_var( $email, FILTER_SANITIZE_EMAIL );
if ( !filter_var( $email, FILTER_VALIDATE_EMAIL ) ) {
$results[$k] = "Route#: {$v[0]} Failed: invalid email address";
unset($data[$k]);
continue;
}
// we have a valid row

// set extra values for db table
$values = array(
'0' => '', // cf_uid
'1' => date('Y-m-d H:i:s'), // cf_created
'2' => date('Y-m-d H:i:s'), // cf_modified
'3' => $user->id, // cf_created_by
'4' => $user->id, // cf_modified_by
'5' => '', // cf_ipaddress
'6' => $new_user->id // cf_user_id
);

// process some of the data before saving
foreach ( $v as $kk => $vv ) {
//echo'<div>$v: '.print_r($v, true).'</div>';
if ( $kk == 1 ) {
$temp = explode('#', $vv, 2);
$temp = array_pad($temp, 2, '');
//echo'<div>$temp: '.print_r($temp, true).'</div>';
$values[] = $temp[0];
$values[] = $temp[1];
} else {
$values[] = $vv;
}
}
//echo'<div>$values: '.print_r($values, true).'</div>';

// db quote the values
foreach ( $values as $kk => $vv ) {
$values[$kk] = $db->quote($vv);
}
//echo'<div>$values: '.print_r($values, true).'</div>';
$values = implode(', ', $values);
//echo'<div>$values: '.print_r($values, true).'</div>';

// save the data
$query = "
INSERT INTO `#__chronoforms_data_Badge_Declaration`
(`cf_uid`, `cf_created`, `cf_modified`, `cf_created_by`, `cf_modified_by`, `cf_ipaddress`, `cf_user_id`, `input_pilot_name`, `input_gfa_nbr`)
VALUES ({$values});
";
//echo'<div>$query: '.print_r($query, true).'</div>';
$db->setQuery($query);
$db->query();
}
// output the results (one line per record) this confirms which rows have been added.
foreach ( $results as $rv ) {
echo"<div>{$rv}</div>";
}
?>

What does the "Route#:" mean in the error messages in debugger result?

Cheers

Tim

Hi Tim,

Looks like the validation check here:

if ( is_numeric($route_number) === false ) {
  $results[$k] = "Route#: {$v[0]} Failed: non-numeric route number";
  unset($data[$k]);
  continue;
}

is checking for a numeric route number. You probably need to change the test being used.

Bob

OK, progress - no errors now but nothing is imported.

Debug:

Array
(
    [chronoform] => Test_Import
    [event] => submit
    [option] => com_chronoforms
    [Itemid] => 
    [input_file_1] => 20140411000627_Test_Import.csv
    [input_text_3] => 
    [input_submit_2] => Submit
    [6e3e6cce2a97b4b781346a09f1878e02] => 1
    [_PLUGINS_] => Array
        (
            [upload_files] => Array
                (
                    [input_file_1] => Array
                        (
                            [name] => 20140411000627_Test_Import.csv
                            [original_name] => Test_Import.csv
                            [path] => /home/glidinga/public_html/components/com_chronoforms/uploads/Test_Import/20140411000627_Test_Import.csv
                            [size] => 84
                            [link] => http://glidingaustralia.org/components/com_chronoforms/uploads/Test_Import/20140411000627_Test_Import.csv
                        )

                )

        )

)
Validation Errors: 
Array
(
)
Debug Data
Upload routine started for file upload by : input_file_1
/home/glidinga/public_html/components/com_chronoforms/uploads/Test_Import/20140411000627_Test_Import.csv has been uploaded successfully.

Code is:

<?php
// get the path to the uploaded file
jimport('joomla.filesystem.file');
$file =& $form->data['_PLUGINS_']['upload_files']['input_file_1']['path'];
if ( !JFile::exists($file) ) {
  echo "Unable to find the file";
  // Report error
  return false;
}
// open the file and read the data
$handle = fopen($file, 'r');
$data = array();
while ( ( $data[] = fgetcsv($handle, 1000, ",")) !== FALSE ) {
}
fclose($handle);
if ( count($data) <= 0 ) {
  echo "data file is empty";
  return false;
}}
echo'<div>$data: '.print_r($data, true).'</div>';
$defaultUserGroup = array(2, 9);
$user =& JFactory::getUser();
$db =& JFactory::getDBO();



// set extra values for db table
  $values = array(
    '0' => '', // cf_uid
    '1' => date('Y-m-d H:i:s'), // cf_created
    '2' => date('Y-m-d H:i:s'), // cf_modified
    '3' => $user->id, // cf_created_by
    '4' => $user->id, // cf_modified_by
    '5' => '', // cf_ipaddress
    '6' => $new_user->id // cf_user_id
  );

echo "Insert Starts";

// save the data
  $query = "
INSERT INTO `#__chronoforms_data_Badge_Declaration`
(`cf_uid`, `cf_created`, `cf_modified`, `cf_created_by`, `cf_modified_by`, `cf_ipaddress`, `cf_user_id`, `input_pilot_name`, `input_gfa_nbr`)
VALUES ({$values});
  ";
echo'<div>$query: '.print_r($query, true).'</div>';
  $db->setQuery($query);
  $db->query();
}
// output the results (one line per record) this confirms which rows have been added.
foreach ( $results as $rv ) {
  echo"<div>{$rv}</div>";
}
?>

Hi Tim,

Is this Joomla! 3? If so then $db->query(); needs to be replaced with $db->execute();

Bob

OK tried that, no change in behaviour.

Would it be better to deal with this off list? Happy to buy as much coffee as needed🙂

Hi Tim,

By all means email or PM me the site URL, the form name, and a SuperAdmin login and I'll take a quick look.

Bob