profile date fields not importing correctly
Andro01 - September 9, 2007 - 12:18
| Project: | User Import |
| Version: | 6.x-1.2 |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | patch (to be ported) |
Description
Great module, like the way it works. Only thing I can't figure out is the format for uploading date fields in the profiles. What is the format I am supposed to use?
#1
I have a date cell formated as "d-mmm-yy" and it seems to come through just fine.
#2
I am unable to get a profile field that is a date to work. I have an expiration date in the old system I'm importing. I've tried these formats:
31-12-2010
01-September-2008
"Mon, 01 Sep 2008 00:00:00 UTC"
The imports run without any warnings, but the profile_expire_date is still empty so that when I try to view the data it defaults to today's date.
What is the proper date format? Here a typical line of my file:
First,Last,password,31-12-2010
#3
I never tried but I think the default date in Drupal looks like
2004-02-12T15:19:21+00:00
but I'm not sure...
just try to put some dates from one of the rows in the .csv and see if the date keeps on the system.
#4
I think i found a bug in _user_import_save_profile function. Problem is that profile module save date fields in serialized format so its not possible to just save dates like plain texts. I have modified _user_import_save_profile function to work correctly with date fields (although not sure is this the best way):
function _user_import_save_profile($field, $uid, $value) {
// BEGIN CHANGE
// It seems that drupal saves date profile fields in serialized format.
$type = db_result(db_query("SELECT type FROM {profile_fields} WHERE fid = %d", $field));
if ($type == 'date' && $value != '') {
// Assuming MM/DD/YYYY format.
list($month, $day, $year) = split('/', $value);
$value = serialize( array('month' => $month, 'day' => $day, 'year' => $year) );
}
// END CHANGE
$profile = db_query("INSERT INTO {profile_values} (fid,uid,value) VALUES(%d,%d,'%s')", $field, $uid, $value);
return;
}
For more info take a look to http://api.drupal.org/api/function/profile_save_profile/5 and http://api.drupal.org/api/function/_profile_field_serialize/5
#5
I have experienced the smae problems trying to import a date field and believe you have hit the nail right on the head with your code change.
For anyone (like me) preparing their data for import using Excel, remember to format the date fields as 'm/d/yyyy' before saving as CSV and importing. If you leave the formatting at the default 'mm/d/yyyy' or 'mm/dd/yyyy' you will end up with leading zeroes on at least the month field for months up to September and they will not import properly (defaulting instead to January).
#6
Have applied the modifications in #4 to the user_import.module and formatted as m/d/yyyy as suggested in #5 and this works! Thanks very much, I've spent way to long trying to solve this problem and the suggestions in #1 to format the date as d-mmm-yy does NOT work. Wonder if this should be a formal patch?
#7
Good "patch" in #4 for this wonder module.
For other date formats, like dd/M/yyyy (in my case), simply change the order of list and serialized array.
Dont forget the "M". "MM" doesnt works.
*********ADDED LATER***********
My imported fields are repeated. Even if I import in M/dd/yyyy format (not a date problem). I identify this problem in view module, in a list of users, and check in the database table. In every user imported, only in 3 fields of profile (of 10) information are included, but the same fields are included with empty values (repeated). The other fields are empty, and are OK. After a look in the profile over an administration interface and SAVING, the problem are solved, with the empty duplicated fields deleted by drupal. I will dont do this with 1200 included users........................
Any suggestion?
Regards,
#8
Solved in here:
http://drupal.org/node/174322
There's one comment at the end that suggests to comment one line, and you will not have any duplicate field.
Cheers
#9
Man,
You are the best. Kill my headache!!!
Thanks a lot.
regards,
#10
You're welcome :)
#11
Automatically closed -- issue fixed for two weeks with no activity.
#12
Hi all,
First of all, the version of user_import module that i use is: user_import-5.x-2.8.tar.gz
For the date format, I have tried suggestion in #4, but i think this code need to implement more in the profile_user_import_save_profile function, which is in profile.inc
I implement like the one below:
function profile_user_import_save_profile($field, $uid, $value, $updated, $update_setting, &$data) {
// when the profile field is displayed on the registration form an empty value is automatically saved by the Profile module
$exists = db_result(db_query("SELECT value FROM {profile_values} WHERE fid = %d AND uid = %d LIMIT 1", $field->fid, $uid));
if($field->type == "date"){
// Assuming MM/DD/YYYY format.
list($month, $day, $year) = split('/', $value);
$value = serialize( array('month' => $month, 'day' => $day, 'year' => $year) );
}
if ($updated) {
switch ($update_setting) {
case UPDATE_NONE:
return;
case UPDATE_ADD:
if (empty($value) || (!empty($exists) && $exists != '')) return;
case UPDATE_REPLACE:
if (empty($value) && $update_setting == UPDATE_REPLACE) {
db_query("DELETE FROM {profile_values} WHERE fid = %d AND uid = %d", $field->fid, $uid);
unset($data[$field->name]);
return;
}
if ((empty($exists) && $exists != '') || $exists === FALSE) {
db_query("INSERT INTO {profile_values} (fid,uid,value) VALUES(%d,%d,'%s')", $field->fid, $uid, $value);
}
else {
db_query("UPDATE {profile_values} SET value = '%s' WHERE fid = %d AND uid = %d LIMIT 1", $value, $field->fid, $uid);
}
$data[$field->name] = $value;
return;
}
}
else {
if (empty($value)) return;
if ((empty($exists) && $exists != '') || $exists === FALSE) {
db_query("INSERT INTO {profile_values} (fid,uid,value) VALUES(%d,%d,'%s')", $field->fid, $uid, $value);
}
else {
db_query("UPDATE {profile_values} SET value = '%s' WHERE fid = %d AND uid = %d LIMIT 1", $value, $field->fid, $uid);
$data[$field->name] = $value;
}
}
return;
}
And also need some little changes in function profile_user_import_after_save, which is like the one below:
function profile_user_import_after_save($settings, $account, $password, $fields, $updated, $update_setting_per_module) {
// get all fields
$profile_fields = profile_get_fields();
$data = $old_data = unserialize($account->data);
foreach ($profile_fields as $field) {
profile_user_import_save_profile($field, $account->uid, $fields['profile'][$field->fid][0], $updated, $update_setting_per_module['profile'], $data);
}
// data column in the user table needs to be updated
if ($data != $old_data) {
//db_query("UPDATE {users} SET data = '%s' WHERE uid = %d LIMIT 1", serialize($data), $account->uid);
db_query("UPDATE {users} SET data = '%s' WHERE uid = %d LIMIT 1", 'a:0:{}', $account->uid);
}
return;
}
Note that, without changes in function profile_user_import_after_save, your date will still not display properly, even though the value in table profile_values already in a form of serialized array.
So the correct date format should be: MM/DD/YYYY
Thanks.
#13
I have created a patch with the code in #12.
It also adds a select option on the configure page (admin/user/user_import/configure) that allows you to select a date format to use (more date format options can be added if required).
The option only appears if the profile module is enabled.
The patch is against 5.x-2.8 and seems to work correctly in my testing.
#14
Renaming to properly describe the issue
#15
Here is a 6 version of this patch.
I have not been able to test it yet, I just did a quick port of the 5 patch as all the changed code seems to be the same across both versions.
#16
I have marked #449466: date field does not seem to work as a duplicate of this issue.
#17
#18
I have made a little change to these patches as I was having problems with days and months with a leading zero.
The patch now strips leading zeros from the day and month parts and that problem is fixed.
#19
Hi,
Just one small typo fix affecting YYYY/MM/DD users:
-+ else if ($date_format == 'YYYY/MM/DD/') {
++ else if ($date_format == 'YYYY/MM/DD') {
Cheers,
Paul
#20
Oops, thanks for fixing that.
#21
Hi Justin,
I tried your patch for D6 (#19) and it doesn't seem to work, no matter which date format I use.
Cheers,
Chris
#22
Can you give some more information like a date that doesn't go in or something?
#23
I've attached a sample file I tried to upload. The dates import correctly if I specify a text field as the destination but then they need transforming to be of any use.
#24
I was able to successfully import your file in with date data going into a date field.
I am using Drupal 5.12 and User import 5.x-2.8
The field I am importing into is a date field not a text field
On admin/user/user_import/configure for Date field format: I have selected DD/MM/YYYY
Is there anything else you can think of about your set up that might affect this import?
#25
Only that I'm using Drupal 6.12 and User Import 6.x-1.2.
#26
That would explain why it does not work as expected. This patch is for Drupal 5 and it is rare that a module is so similar from D5 to D6 that a patch will work for both.
And seeing as this patch will more likely be accepted with a 6 version as well I'll change the issue.
This patch will have to be ported to D6.
#27
I won't be able to work on porting it to 6 any time soon though so if anyone else wants to that would be great.
#28
It would be nice, if this could be fixed for the D6 version of the module.
For importing users with a field "birthday" I used this "workaround":
1. Open the CSV file with open office
2. Insert a new column "birthday new"
3. Enter the following formula (this is for the german OO version!)
="a:3:{s:3:" & ZEICHEN(34) & "day" & ZEICHEN(34) & ";s:" & LÄNGE(TAG(BU2))&":"&ZEICHEN(34) &TAG(BU2)&ZEICHEN(34)&";s:5:"&ZEICHEN(34)&"month"&ZEICHEN(34)&";s:"&LÄNGE(MONAT(BU2)) &":"&ZEICHEN(34)&MONAT(BU2)&ZEICHEN(34)&";s:4:"&ZEICHEN(34)&"year"&ZEICHEN(34)&";s:" &LÄNGE(JAHR(BU2))&":"&ZEICHEN(34)&JAHR(BU2)&ZEICHEN(34)&";}"
4. Copy the formula down into each row
5. Repeat steps 1 to 4 if you have more date fields
6. Import the CSV file
The formula converts a date field entry like "dd.mm.yyyy" to something like this
a:3:{s:3:"day";s:2:"31";s:5:"month";s:1:"3";s:4:"year";s:4:"2000";}
Thats what the profile field expects.