Export/Import from an external database

[pezza]

Hi,

I need to transfer data from another database in to AbleCommerce. The existing database is SQL 2008, but one of the fields (details) is HTML. This is causing me issues when trying to export to either XLS or CSV, as this field has commas, semi-colons and tabs, therefore it is splitting in to seperate columns.

I have managed to export as XML, but i have got lots of extra fields that i do not need to migrate over, and this is proving tedious using an XML editor to remove the additional fields on over 1,000 products.

I wondered if DataPort can be used to connect to my none AC database to export, and then use it to import in to AC7? From what i have readm it looks like dataport will only work with AC. If this is the case, can anyone advise if:

1) Anyone knows of any tools where i can remove a field in XML across all records at once
2) Can i export HTML in to the "description" field of AC7 or will it have to be converted to the text equivalent? One of the reason for the HTML is that it contains tables for sizing charts in each product description.
3) What is the best way to approach a migration from a none AC database?

I would really appreciate any help with this, as i don't fancy the prospect of re-entering more than 1,000 products

Thanks

Andrew

[Mike718NY]

I had the same problem.
You can change the default text delimiter in Excel to a Pipe (|).

Start -> Settings -> Control Panel -> Regional Settings
"Number" tab, . . in the "List Separator" field, replace the current default separator

more here : viewtopic.php?f=42&t=7541

[pezza]

Hi Mike,

Thanks for your reply.

That seems to have worked I had to export from SQL as a TAB delimiter though, as the problem i have is i only have a control panel cut down version of MyLittleAdmin, which is extremely restricted on what you can do. Very useful for quick access, update and queries, but lacks some exporting features. I changed the default seperator to a pipe, but I am not sure if this is what did the trick. I tried TAB previously but this split too, however this time, i also set the column to TEXT in the Text to Columns section in Excel and it worked, so not sure if it was the pipe (as i couldn't export using a pipe) or whether it was because i hadn't set the column to text on my first attempt. I am going to try a few more though, so that i can establish the true fix

The only thing is that in excel, the column shows # signs. Did yours do the same? I assume that this is to do with the settings in the cell in terms of length settings. I can still see all the code in the editor bar in excel so it has definately retained the correct data, and more importantly, the columns are now correctly aligned

Did everything import fine for you when you imported this HTML in to the description field in AC7?

Thanks for your help with this

Andrew