Super I/E Mod Problem - Excel 255 Character Limit

[aquariumpro]

We needed to change all the prices in our store. Tried Sebenza's module which works great, but we had a lot of tweaking to do with costs and had to raise some product prices within a category by different amounts.

I have the Copernicus Super Import/Export Mod, so I exported, pulled the file into Excel, did the work, then imported. Prices are all changed now, but I forgot about Microsoft's 255 character limit. Our descriptions got cut off. Some of the descriptions are quite lengthy because they have to be (charts/tables, etc.)

Anyone know of an editor which won't cut off our descriptions and will still allow price changes. Excel was great because we could apply formulas to various cells yielding the results we wanted in a short time. We have 2800 products. :o

I don't think we can use StoreMan because we're using OpenXB and I think StoreMan still uses Access-based database which would have the same character limitation.

If I'm wrong (Bruce - you there?), please let me know.

[ILoveHostasaurus]

If you're just changing prices on existing products and this is OpenXB, how about just create your data in SQL format? It would just be update queries against the Merchant2_0000001_Products_dbf table setting price where the product ID equals the one in your file.

[aquariumpro]

Thanks, but I'm lost on that one David. I'm not an SQL wiz. Can I do that in Excel, or is there an SQL database editor. I know how to export tables using the SQL admin. Where do I go from there?

I'm willing to try anything, but thank goodness I'm hosted with Hostasaurus if I;m going to start messing with SQL tables. Hehe :)

[ILoveHostasaurus]

It's actually pretty easy. All you need to be able to do is generate a flat text file with the following format:


UPDATE Merchant2_00000001_products_dbf SET sql_PRICE = "123.45" WHERE sql_CODE = 'ABC123';


In that example, I'm setting product code ABC123 to have a price of $123.45.

If you can produce the file with all of your appropriate prices and product ID's, I can show you how to run it against your database. Maybe you can use a macro in Excel or something similar to output just two columns of data mixed into the format above?

[aquariumpro]

One statement per product per line? Or do I use a seperator for the txt file?

[ILoveHostasaurus]

Correct, each update query will be setting one price on one specific product code.

[aquariumpro]

Ok, easy enough. I can isolate the column for price in Excel, then use a formula to plug the prices into that line - no mproblem. However, when saving the txt file, it uses Tab or comma separators.

Let me play with it. That kind of stuff is usually easy to figure out. Probably have to save as a csv, then import that into a text editor with a little more flexibility.

[aquariumpro]

I'll let you know when I get it done. Excel is not the answer. It only knows tab delimited for saving txt files. Wish find and replace removed tabs!

[aquariumpro]

DOH! It also does comma delimited - ah ha! Find and replace will sure as shootin' remove commas. Yay!

[ILoveHostasaurus]

See, it's easy as long as you do it the way Microsoft wants you to. :)

[aquariumpro]

David:

I have that text file ready. I also have created a text file for cost, which also needs to be changed using this string:

UPDATE Merchant2_00000001_products_dbf SET sql_COST = 202.99 WHERE sql_CODE = 5793

I am assuming that will work.

Now what?

Thanks

[aquariumpro]

Also, I copied that string right out of your post here. Just clarifying that there should be a space before and after the = sign??

[ILoveHostasaurus]

Yes, but single quote the values.