Post by Qphoria » Sat Nov 20, 2010 11:40 pm

READ THIS FIRST!
==========================
The purpose of this tool is to allow "raw" data to be edited similar to the way you can edit in phpmyadmin, except much easier since its using the openfield CSV format. You can use Excel to edit the fields the same. Just be sure you save the files in csv format (as excel always offers to convert to xls for you).

This is not meant to be a replacement for the "Excel Import/Export" tool. That is a structured product import tool that correlates products, options, and more into one formatted structure. This means that changes to the database will need to coded into that mod.

This mod is "raw" by design to remove dependency on version or customizations to the database, and read it on-the-fly when generating the output. However, it can be used for product import (or any other field) but all individual tables need to be edited separately.

For example, if you want to add new coupons, you can export the coupon table, add your new coupons, and import it back in. But if you want to add new products, you need to export each of the following at a minimum:
product
product_description
product_to_store
product_to_category

In each of these, you must add the record of data, using the product_id as the primary key. It requires some general knowledge of mysql so it is more of an intermediate tool.

Supported OpenCart Versions:
================
v1.4.x

What does it do:
================
This contrib adds a new tool page for CSV Import/Export.
The mod will list ALL tables in a dropdown.
Choose individual tables to export in CSV format.
Edit the CSV file in an editor or Spreadsheet program (recommended)
Save and reimport the CSV file. It will replace the existing data with the new imported csv data


Main features:
==============
* Tables are Completely dynamic. Automatically gets the list of ALL tables, even custom ones
* Columns are also completely dynamic. All columns are found and listed, even custom ones
* Edit tables individually. This is a raw format editing tool which allows it to edit new fields at anytime without dependencies.
* Ignores columns that don't exist or that were left out purposely when reimporting.


How to install it:
==================
1) Unzip and upload the "admin" folder to the root of your OpenCart install. No files are overwritten
2) a. EDIT: admin/view/template/common/header.tpl
b. FIND:
----------------------------------------------------

<li><a href="<?php echo $backup; ?>"><?php echo $text_backup; ?></a></li>

----------------------------------------------------


c. AFTER, ADD:
----------------------------------------------------

<?php @$this->load->language('tool/csv'); ?>
<?php if (@$this->language->get('text_csvmenu') != NULL) { ?>
<li><a href="<?php echo (((HTTPS_SERVER) ? HTTPS_SERVER : HTTP_SERVER) . 'index.php?route=tool/csv&token=' . $this->session->data['token']); ?>"><?php echo $this->language->get('text_csvmenu'); ?></a></li>
<?php } ?>

-----------------------------------------------------

3) From the admin menu, go to 'Admin->Users->User Groups'. Edit the Top Administrator.
4) Find and check the new entries for both modify and access.
5) From the admin menu, go to 'System->CSV Import/Export'.


Usage Notes:
===============
- This is an atomic import. Meaning it deletes the data in the table before importing the new data. An Append mode is planned for a later date.
- The top row of the exported CSV files is in "table.column" format. Do not change these headings. You may, however, remove complete columns that you don't want to import. These will just be left blank.
- Microsoft Excel is a good CSV editor, but erroneously converts the date column to "xls" format. I do attempt to correct this behavior programmatically but the feature is in beta and may or may not work. In the case it does not work, you will likely see a date of 01/01/1970 which is the default date mysql uses. Please report cases of this when noticed.
- This tool is for raw editing of table data. Do not delete or change any "ID" columns as they are used to link to other tables for extra data
- This tool is designed to be dynamic performance may or may not be affected. Performance is not the goal since it is on the admin side. Dynamic functionality is the goal.
FREE DOWNLOAD

I've not tested it on large databases yet, but because it uses individual tables, it may be able to handle larger amounts of data. Anyone with a large store and some intermediate know-how, please let me know.

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by JAY6390 » Sun Nov 21, 2010 12:34 am

Cool mod, very useful to those not well versed in MySQL. Alternatively, use Navicat live on your server ;) Best $130 I ever spent :)

Image


User avatar
Guru Member

Posts

Joined
Wed May 26, 2010 11:47 pm
Location - United Kingdom

Post by Johnathan » Sun Nov 21, 2010 6:13 am

JAY6390 wrote:Alternatively, use Navicat live on your server ;) Best $130 I ever spent :)
For Macs, the best thing is Sequel Pro. It makes editing the database a breeze -- much better than phpMyAdmin.

Image
Image Image Image Image


User avatar
Global Moderator

Posts

Joined
Fri Dec 18, 2009 3:08 am


Post by imaginetech » Sun Nov 21, 2010 9:05 am

Nice one and great to see you again release a valuable contrib for free. Good call, leading the way.

Image
www.opencartstore.com
Get OpenCart Templates and OpenCart Modules.


User avatar
Active Member

Posts

Joined
Fri Sep 04, 2009 12:25 pm
Location - Darwin, Australia

Post by thegeekz » Wed Dec 01, 2010 5:32 pm

Hmm so this could work for Importing of customers?

That would be cool!

No more using Apsona, as they are not updated.

  • Every upgrade -- rem. 2 reinstall vqmod & mindful of modules w/ VQmod -- E.g Import / Export Tool by MHC


Active Member

Posts

Joined
Tue Nov 02, 2010 10:24 am

Post by Qphoria » Thu Dec 02, 2010 12:33 am

Yes absolutely

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by thegeekz » Mon Dec 13, 2010 2:24 pm

Ok.. Got the mod... now the thing is.. how do I read it like a normal Excel file with columns? Its in its native CSV format now with commas and all, and is confusing...

Heh heh

No more using Apsona, as they are not updated.

  • Every upgrade -- rem. 2 reinstall vqmod & mindful of modules w/ VQmod -- E.g Import / Export Tool by MHC


Active Member

Posts

Joined
Tue Nov 02, 2010 10:24 am

Post by Xsecrets » Mon Dec 13, 2010 9:29 pm

thegeekz wrote:Ok.. Got the mod... now the thing is.. how do I read it like a normal Excel file with columns? Its in its native CSV format now with commas and all, and is confusing...

Heh heh
just use excel or pretty much any spreadsheet program to open it. They pretty much all understand csv format. The only thing you have to pay attention to is that when you save the changes to save them back in the same format. It is going to popup a box wanting you to change it to the spreadsheets native format, and you have to choose the appropriate option to not change the format.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by SapporoGuy » Mon Dec 13, 2010 11:14 pm

+1 on sequel pro for OSX
I gave on phpmyadmin when it was causing me headaches by not wanting to install. The app just as good if not better in some situations.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by bluerayconcepts » Sat Jan 01, 2011 6:18 am

If we export all the tables can we them import them as one single file or do they all need to be reimported separately?

DJ
BlueRay Concepts
http://www.bluerayconcepts.com


New member

Posts

Joined
Thu Nov 04, 2010 3:56 am

Post by SapporoGuy » Sat Jan 01, 2011 2:28 pm

export normally is a dumb which can be per table or per database.
So, I guess it depends on your software that you use.

I normally just dump my whole database.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by Qphoria » Sat Jan 01, 2011 2:40 pm

It is table by table only.. 1 in, 1 out.. meant to be simple

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by SapporoGuy » Sat Jan 01, 2011 3:04 pm

"is a dumb" ... ETF ????

Sorry,
I meant "is a dump" ... auto correction ;)

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by Qphoria » Sat Jan 01, 2011 3:43 pm

lol perhaps a Freudian slip

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by markus00 » Tue Jan 18, 2011 6:21 pm

Hello,

I have problem with the module Raw Dynamic CSV Import/Export.

I'm running v1.4.9.3

My cart installation is absolutely new.

Export works well.
But if I export a file and then should import it directly without any edit I get this fault:
Error: Column count doesn't match value count at row 1
Error No: 1136
INSERT INTO

Newbie

Posts

Joined
Tue Jan 18, 2011 5:40 pm

Post by dry_flood » Wed Jan 19, 2011 2:49 am

I installed the latest version of this extension this morning and am having exactly the same error. Straight after the error, I checked the DB Table and the table that corresponded to the file I was uploading was now empty.

Up until the update, it was working fine.

New member

Posts

Joined
Tue Dec 14, 2010 4:33 am

Post by Qphoria » Wed Jan 19, 2011 3:47 am

what table was this on?

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by markus00 » Wed Jan 19, 2011 4:40 am

This was on category but I get exactly the same fault with all the files.

I have tried convert the file between ASCII and binary but same problem with both.

Here is the category.csv file

Code: Select all

"category.category_id","category.image","category.parent_id","category.sort_order","category.date_added","category.date_modified","category.status"
"25",,"0","3","2009-01-31 01:04:25","2010-03-21 15:30:07","1",
"27",,"20","2","2009-01-31 01:55:34","2010-03-21 15:29:49","1",
"20",,"0","1","2009-01-05 21:49:43","2010-03-29 15:15:17","1",
"24",,"0","5","2009-01-20 02:36:26","2010-03-21 15:30:50","1",
"18",,"0","2","2009-01-05 21:49:15","2010-03-21 15:30:00","1",
"17",,"0","4","2009-01-03 21:08:57","2010-03-21 15:30:44","1",
"28",,"25","1","2009-02-02 13:11:12","2010-03-21 15:30:17","1",
"26",,"20","1","2009-01-31 01:55:14","2010-03-21 15:29:42","1",
"29",,"25","1","2009-02-02 13:11:37","2010-03-21 15:30:12","1",
"30",,"25","1","2009-02-02 13:11:59","2010-03-21 15:30:22","1",
"31",,"25","1","2009-02-03 14:17:24","2010-03-21 15:30:27","1",
"32",,"25","1","2009-02-03 14:17:34","2010-03-21 15:30:35","1",
"33",,"0","6","2009-02-03 14:17:55","2010-03-21 15:30:55","1",
"34",,"0","7","2009-02-03 14:18:11","2010-03-21 15:31:01","1",

Newbie

Posts

Joined
Tue Jan 18, 2011 5:40 pm

Post by Qphoria » Wed Jan 19, 2011 5:44 am

very strange.. I just tried it on my 1.4.9.3 with no issue. if you set me up with temp FTP access I can try to debug the issue

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by dry_flood » Wed Jan 19, 2011 8:02 am

PM incoming with login details

New member

Posts

Joined
Tue Dec 14, 2010 4:33 am
Who is online

Users browsing this forum: No registered users and 6 guests