Importing data

I have a similar need but would like to know how to do it myself as it seems like a relatively easy process. I have read the FAQs and other information from this site and other but I still cannot seem to figure out what I am doing wrong.

I am running the latest version of Zen Cart, fresh install and so far no addons have been added (I actually scrapped my last cart because I was being reckless with addons and not backing my stuff up...) All I have done was added a template that does not affect database files.

I had gotten some considerable amount of work done with the site before it went up in flames (I do not feel like getting into it..). I figured that to save some time, I could backup my database and drop select pieces of it. I did not know which options to select to get an easy-to-drop .sql file so I just went with something (I do not remember what). I then opened up the file and started eliminating all of the information that I did not want to include (most of which were entries that I knew was going to be loaded with a fresh install and blank data tables) I cleaned it up a bit so now my .sql file has a series of data insert commands...

I am using 'REPLACE' commands just in case there are database entries that would add a primary key that was already used (so it overrides it instead). I have gone through and made sure the alterations as made by the addons were deleted. The link below is to my database with ONLY NON-SENSITIVE information being shown:

2monkey.com/fairydus_zc1.sql

The error I am receiving the following message when I try to upload the .sql files:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"INSERT INTO zen_banners (banners_id, banners_title, banners_url, `banne' at line 1

I removed the " ` " (backwards apostrophe?) to see if that would work but to no avail.

What is the proper syntax to use?

idk if this will help you help me, but here is some additional info:

PHP Version: 5.2.17

Database: MySQL 5.0.91

Server Host: Site5

Been working on this for WAAY too long :\

[moderator comment]

Your posts have been moved to their own thread. Hijacking somebody else's thread before their issue has been resolved is not appreciated in this forum, especially when your topic has such a tenuous link to the original.

kuroi:

[moderator comment]

Your posts have been moved to their own thread. Hijacking somebody else's thread before their issue has been resolved is not appreciated in this forum, especially when your topic has such a tenuous link to the original.

My bad, I figured that since he was looking for contracted work that he should do so in the 'Commercial Help Wanted' section. As such, I thought my question was more relevant to his question than his own question since he is not seeming like he'll wants support for it, rather to have somebody else do it. But I digress...

I'm new to forum posting, where do I go to get to where my link was moved to?

ConwayMJ:

My bad, I figured that since he was looking for contracted work that he should do so in the 'Commercial Help Wanted' section. As such, I thought my question was more relevant to his question than his own question since he is not seeming like he'll wants support for it, rather to have somebody else do it. But I digress...

I'm new to forum posting, where do I go to get to where my link was moved to?

Never mind, I figured it out...

ConwayMJ:

Never mind, I figured it out...

To clarify, I figured it out where it got moved to, but I am still having problems with this. Any help would be great. I tried looking up syntax rules in Google and nothing seems to work. If somebody could just show me how the table data replacement function would work using one of the replacement commands in the .sql, that would be uber awesome and I could just replicate the process for the 30 others I need to do... this is so frustrating!

The error that you're getting appears to relate to the quote character at the beginning of your query.

Is it possible that you have copied Zen Cart code that wraps the query with a single or double quote, which is being translated into an HTML entity that isn't valid in SQL?

The back quotes on the individual fields are rarely needed nowadays, at least if you're using a moderately recent version of MySQL.

REPLACE zen_banners (banners_id, banners_title, banners_url, banners_image, banners_group, banners_html_text, expires_impressions, expires_date, date_scheduled, date_added, date_status_change, status, banners_open_new_windows, banners_on_ssl, banners_sort_order) VALUES
(1, 'Wear', 'index.php', 'banner1.jpg', 'banner-1', '', 0, NULL, NULL, '0001-01-01 00:00:00', NULL, 1, 0, 1, 0),
(2, 'Unwind', 'index.php?main_page=product_info&cPath=7&products_id=13', 'banner2.jpg', 'banner-2', '', 0, NULL, NULL, '0001-01-01 00:00:00', NULL, 1, 0, 1, 0),
(3, 'Survive ', 'index.php?main_page=product_info&cPath=7&products_id=11', 'banner3.jpg', 'banner-3', '', 0, NULL, NULL, '0001-01-01 00:00:00', NULL, 1, 0, 1, 0),
(4, 'COBRABRAID', 'index.php?main_page=product_info&cPath=7&products_id=11', 'banner4.jpg', 'banner-4', '', 0, NULL, NULL, '0001-01-01 00:00:00', NULL, 1, 0, 1, 0),
(5, 'Nike Running', 'index.php?main_page=product_info&cPath=7&products_id=11', 'banner5.jpg', 'SideBox-Banners', '', 0, NULL, NULL, '0001-01-01 00:00:00', NULL, 1, 0, 1, 0);

I tried removing those backward apostraphe's and importing it but it did not work. I am not seeing any unneccessary single or double quotes, just the unneccesary `s that I tried removing but I still got the same error.

When I open up the .sql, it opens up in Excel and it goes:

REPLACE affected_table (column headers) VALUES

then on separate lines it goes:

(value1,value2,etc),
(value1-2,value2-2,etc),
(etc);

could it be because it is not all on the same line?

kuroi:

The error that you're getting appears to relate to the quote character at the beginning of your query.

Is it possible that you have copied Zen Cart code that wraps the query with a single or double quote, which is being translated into an HTML entity that isn't valid in SQL?

There is no quotes in the beginning of the query when I open it up in Excel... I also am not seeing a quote character at the beginning of the query?

:frusty:

The SQL that you've posted is OK and would work provided your banners table has a zen_ prefix.

You've said that you keep getting the same error, but you haven't said what the error is when you use the REPLACE version of your query, so it's difficult to provide any help on it.

And I don't understand why you keep opening the query in Excel. Excel has its own way of interpreting data that render is an unreliable way of viewing raw data or code.

I'm opening it in Excel because I'm a newb that read a Tim Ferris book and now thinks he can create businesses out of thin air. Funny thing is that I've managed to convince a multi-millionaire that I could after saying how bad his websites were so now I am doing that because I'd rather not use my degrees in economics or accounting. :smartalec:

In all honesty though I am a quick learner when I have the available information but I just did not think to download a sort of SQL reader. I just double clicked the SQL file and Excel decided it was going to take the job so I went with it. Seems rather basic now that you've pointed that out. I'll try using that to edit it er something and post how that goes. Do you have any recommendations for SQL readers/writers?

Why don't you setup a separate 'test' area?

You should have a 'test' area anyway so you can test things. For example, setup a test store then Export the database. You can then see how things should be. Make changes, Import the changed database and see if anything broke.

Most any Spreadsheet can open a 'csv' file, which is a format phpMyAdmin will allow you to use. Excel has given many people problems I suggest using http://openoffice.com/ which is free and since using it, I have never needed to purchase or use MS Office.

lmao, yeah i'm def nubbed. apparently any standard text editor will do.... as for the error, i was getting a syntax error related to the use of quotes. i was just confused because i was not seeing the quotes when i opened up the file (which defaulted to Excel).

i opened it up in standard txt editor and see the quotes so we'll see what happens XD

Although any Text Editor can open a 'csv' or 'sql' file, as they are just text files anyway, using a Spreadsheet program gives some nice formatting to the text. All depends if you want formatting or not.

So it looks like that was it. Excel just hid the " " text wrappers so I just did not see them. Also found that Dreamweaver has an SQL reading capability but oddly enough I like the standard notepad better (with the Find/Replace function, it showed the lines each instance was on). Opened up the .SQL in Excel, copied Column A, then pasted it into Notepad to get this horrendous blob of characters I could not make heads or tails of without staring intensely at the monitor. Saved the file, changed the extension to .SQL and opened it in Dreamweaver to have it display all nicely (may be it would have displayed nicely if I had just reopened the file in Notepad but I did not check that).

At any rate, problem solved. Damn you Excel.....