Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Easy Populate


webshark

Recommended Posts

When I d/l the .txt file to excel and load, the date field is built like this:

mm/dd/yyyy hh:mm

 

However I use dates in my database like dd/mm/yyyy.

 

And when I u/l again all the dates are corrupted and left as 00/00/0000 00:00

 

Is there a setting in easy populate for the date ?

 

I checked out the Doc and files couldn't see anything.

 

Would appreciate the help.

 

Thanks,

 

Simon

Link to comment
Share on other sites

I had the same problem and this is what I posted on another forum in order to fix it once and for all, you can give it a shot i suppose !

 

Right, this date thing stared to really piss me off so I decide to get into the easypopulate.php code and fix it.

For some reason it looks like the code wrap the value passed for the dates in " .. I dont care why really, but whatever I sent in it was just messed up at the insertion or update stage. I tried changing the excell cell to date or text, no change, which make sense since i anyway export to a tab delimited text file in the end.

 

The fix is simple, just send the date into the format YYYY-MM-DD HH:MM:SS and then tell MySQl that this is a date.

 

Look for:

$query = "INSERT INTO ".TABLE_PRODUCTS." (
           products_id,
    	products_image,
products_image_med,
products_image_lrg,
products_image_sm_1,
products_image_xl_1,
products_image_sm_2,
products_image_xl_2,
products_image_sm_3,
products_image_xl_3,
products_image_sm_4,
products_image_xl_4,
products_image_sm_5,
products_image_xl_5,
products_image_sm_6,
products_image_xl_6,
    	products_model,
    	products_price,
    	products_cost,
    	products_status,
products_last_modified,
products_date_added,
products_date_available,
products_tax_class_id,
    	products_weight,
    	products_quantity,
    	manufacturers_id)
      VALUES (
     '$v_products_id',
'$v_products_image',
'$v_products_image_med',
'$v_products_image_lrg',
'$v_products_image_sm_1',
'$v_products_image_xl_1',
'$v_products_image_sm_2',
'$v_products_image_xl_2',
'$v_products_image_sm_3',
'$v_products_image_xl_3',
'$v_products_image_sm_4',
'$v_products_image_xl_4',
'$v_products_image_sm_5',
'$v_products_image_xl_5',
'$v_products_image_sm_6',
'$v_products_image_xl_6',
'$v_products_model',
  '$v_products_price',
  '$v_products_real_price',
  '$v_db_status',
    CURRENT_TIMESTAMP,
  $v_date_added,
  $v_date_avail,    	'$v_tax_class_id',
  '$v_products_weight',
  '$v_products_quantity',
  '$v_manufacturer_id')";

 

Notice the 2 lines $v_date_added & v_date_avail ?

Change them to:

DATE($v_date_added),
DATE($v_date_avail),

 

further down u will find the update section:

 

$query .= '", products_weight="'.$v_products_weight .'", 
products_tax_class_id="'.$v_tax_class_id .	'", 
products_date_available= ' . $v_date_avail .',
products_date_added= ' . $v_date_added . 

 

change this to

$query .= '", products_weight="'.$v_products_weight .'", 
products_tax_class_id="'.$v_tax_class_id .	'", 
products_date_available= DATE(' . $v_date_avail . ')' .', products_date_added= DATE(' . $v_date_added . ')' .

 

And voila, no more messing around with that stupiud date.

 

Really the best ay to fix this would be to write a function equivalent to the TO_DATE function in Oracle which would let us specify the mask of the date passed as a string, just to avoid error and we could then let everyone send dates in the format they want.

 

Devs, let me know if my logic is wrong and if my fix actually screw up things down the line ..so far it works for me

Link to comment
Share on other sites

I had the same problem and this is what I posted on another forum in order to fix it once and for all, you can give it a shot i suppose !

 

Tried it made no difference.

 

Still all dates are 00/00/0000 00:00

 

If I change the format of the cell in excel to YYYY-MM-DD it works.

 

How can I make this change permant and not need to change manualy when loading the data to the .txt file ?

Edited by webshark
Link to comment
Share on other sites

Tried it made no difference.

 

Still all dates are 00/00/0000 00:00

 

If I change the format of the cell in excel to YYYY-MM-DD it works.

 

How can I make this change permant and not need to change manualy when loading the data to the .txt file ?

 

In my database tables the date looks like this:

2005-01-11 00:00:00

 

In the excel that same line looks like this:

01/11/2005 00:00:00

 

if it re-insert the data with it looking like this in excel its gets messed up.

Link to comment
Share on other sites

Easy Populate requires the date to be in the forum of yyyy-mm-dd. Excel won't let you set that as the default (unless it is your system's default setting) so you have to change it each time. I just finished a patch for Easy Populate to allow this to be changed automatically and will be posting a message with the code either today or tomorrow in the Tips and Tricks section. So if you need the fix, be sure to look for it there.

 

Jack

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...