By AlanAT on

Hi,

I'm a Drupal newbie with very limited PHP coding - apologies for cross-posting.
I'm having some issues with Feeds and how Dates are handled - any help would be appreciated.....

Sytem: Windows XP, Drupal 6.22, XAMP distro with Apache/2.2.17; MYSQL 5.5.8 and PHP 5.3.5 .

Context: I'm setting up a site with 8000+ books listed on it. I want to import the original list from a CSV or Excel file to a custom Node built with CCK fields and I then need to be able to update these nodes with new prices and publication dates on a fairly regular basis.

It looks like the Feeds module is the answer.
When I run the CSV Importer most of the data is imported correctly but about 50% of the 'date' data isn't imported.
I've opened the spreadsheet in LibreOffice and then saved it set to UTF-8, making sure that data in the date column is consistent.
From what I can see it is a formatting issue.
I want to use DD/MM/YYYY formatting as this makes the most sense to my customer base. I've set up the CCK Datefield to that style of formatting and looking at the CSV file it appears in that formatting style as well.
But when the import is finished the Nodes are showing the date field in MM/DD/YYYY format.
As I've set up the Drupal end in the style that I want I presume that it must be the CSV file that formats date in MM/DD/YYYY as it's standard format - is there any way to change this?

Does any one have any ideas, pointers to what I should be looking for?

⋅ Categories: Drupal 6.x

Comments

WorldFallz’s picture

WorldFallz commented

No need to crosspost -- especially to an incorrect forum, lol. Since this has nothing to do with language translations, I've deleted the dupe.

As for your question-- is the date in drupal correct and just formatted differently than you want or incorrect? In other words, is MM the correct month and DD the correct day and they just appear in the wrong order in drupal?

AlanAT’s picture

AlanAT commented

Thanks WorldFallz - it wasn't until after I'd sent the original post that I realised (doh!) that it meant Language translations and not Data translations (must remember to read the manual ...)

Anyway, no - the problem seems to be that the importer is reading the day data as month data so that, say a date of 01/12/2001 is being processed as 12/01/2001. This explains why so much of the Date data is missing because any date field with day data of more than 12 will be treated as invalid (there are no months greater than 12) and discarded.

I've set up all the Drupal date parameters as DD/MM/YYYY - in Site Configuration | Date and Time as well as in the pub_date field in the Custom CCK node I created - and when I look at the CSV file in LibreOffice it appears to be in DD/MM/YYYY format as well.
I thought I'd give the Excel Importer that is also available but had even less success with it - I couldn't get it to import anything except the header row! I doubt that I have enough coding to be able to make use of Migrate.

I'm feeling a bit dumb and at a bit of a loss as to where to go next.....so any help....

WorldFallz’s picture

WorldFallz commented

You'll almost always want to avoid excel (or other spreadsheet) formats whenever possible-- csv is universal so you want to use that.

don't feel dumb-- feeds is an amazing module but it takes on quite a complex thing. And I always find date fields to be a headache, lol.

I don't have feeds handy atm, but I'll post back later or tomorrow when I get to a place I can take a look.

AlanAT’s picture

AlanAT commented

Thanks to a posting by stella on March 21, 2011 at 1:25pm 'CCK Date fields not always imported - check your date format', I've resolved these issues.
It is due to the way that the strtotime() function assumes dates formatted using the / separator are american style mm/dd/yyyy; so changing the date formatting on the CSV spreadsheet to using the - separators means that I can use European style dd-mm-yyyy dates.
Apart from this date issue I've found the feeds module excellent - thanks to everyone involved.....

Anandyrh’s picture

Anandyrh commented

Hi,

Could you please let me know how you managed to import dates.. I have been trying since a day now.. by importing with various/different date formats and count to get it worked.. please let me know which format of date you used in csv file which worked importing.

johan2’s picture

johan2 commented

Hi, when you import European format d/m/Y then it will only work if you change the date in csv to d-m-Y , or you can use Tamper and add a plugin "Find replace" and replace / by - . In "admin/config/regional/date-time" and in the content-type field setting for the date the same order must be followed.

Anandyrh’s picture

Anandyrh commented

I have it working now.. It was a problem with the date format mentioned in my csv file.

maxplus’s picture

maxplus commented

Great!

format dd-mm-yyyy also works for me!

Thanks

wusel’s picture

wusel commented

For the modules Feeds, Migrate and ...:

The only allowed date formats in the input-file are:
"YYYY-MM-DD" or "MM/DD/YYYY" or "DD.MM.YYYY".
The delimiters are different for each format and have to be used properly!
This is only for the import!
Within e.g. a view, you can chose the format of the output.

Wusel

naziaali’s picture

naziaali commented

I have got many information to here.Keep it up.

wusel’s picture

wusel commented

For more information look at http://drupal.org/node/622710#csv

Wusel

ikeigenwijs’s picture

ikeigenwijs commented 
/*
 * Implementation of hook_feeds_after_parse().
 * http://www.mikestiv.com/blog/using-feeds-api * 
 * processes dates of dd/mm/yyyy
*/
function ls_feeds_date_feeds_after_parse(FeedsSource $source,FeedsParserResult $result) {
  
  //dvm($source->id);
  //the id of the importer configuration
  if ($source->id == 'import_language_tests_csv_not_yet_translated_1field_csv') {
    //the fields of the import that need to be handeld by this snipet
	$array_date_fields=array('datum','uiterste datum in uct','uiterste datum via website');
	watchdog('LS_feeds_date', 'Date fields manipulated by LS_feeds_date on import, table names need to be added to add other dat fields');
    foreach ($result->items as $key => &$item) {
//	  	dvm($item); 
		  for($i=0;$i < count($array_date_fields); $i++){  		  	  	
			$your_import_date = explode('/', $item[$array_date_fields[$i]]);
//			dvm($your_import_date);		
			$your_import_date_formated = $your_import_date[1] . '/' . $your_import_date[0] . '-' . $your_import_date[2];
			$item[$array_date_fields[$i]] = strtotime($your_import_date_formated);
		  }
//		dvm($item);      
    }     
  }
najamfzl’s picture

najamfzl commented

For anyone having difficulty in D7, please note that two settings are important.

1. Field and DateTime Format: Firstly, in Config > Regional > Date-time > Formats, create a format suitable to your needs. Then, in the date field settings, select the correct format in More Settings and Values > Date Entry > Date Entry Options.

2. Now in Feeds Importer, in Mapping for Node Processor, ensure that Target field selected shows the correct Date Attribute (start or end). For example, if your field name is field_date and you're collecting only start date, then the Target field should be field_date:start. Likewise, if you're collecting an end date then it must be field_date:end. I was missing this distinction and took me sometime to figure out.