How can I export and import items that contain formatted HTML

[Greg.Tomkins]

Has anyone identified how you can create a product export file that can then be re-imported where your products contain formatted HTML code in the description field?

The problem is my descriptions contain all the usual characters Excel CSV uses to control field import such as " and , along with CR characters so when it comes to importing yuor products you find the description broken up across multiple rows in your import file. I have tried all sorts of ways including editing teh file with a hex editor but I just can't seem to work out a consistent method for achieving this.

This is rather important as this is our only means of creating backups of some rather complex product descriptions but is of no use if we can't import the data back in the case of a major system failure or as is more likely the case, the user inadvertently destroys records by mistake.

Of course this same problem applies to web apps needing to be backed up for same reasons

Thanking you all...

[Liam Dilley]

Hi greg,

BC exports the csv files as utf-8 so you need to make sure you have no funny character encoding settings in your Excel. Outside of that I am not sure what would be creating your issues as we and our clients are importing and exporting things like products all the time...

The only issues can come from..

- Expiry date issues (excel reading date - clearing the column will fix this)

- Funny characters in first cell (delete them)

- If you have custom fields for web apps that a client needs to put say a price field in with $ and excel converting them into number cells (convert to text)

- And when the character encoding is wrong in Excel and the csv is saved to something like latin-1 which then just makes a mess and then when you export again it does as your reporting Greg.

Outside of that if what is happening to you... Not sure.. It is not the norm, BC supports HTML in description area on the CSV

[Greg.Tomkins]

Hi Liam

My product descriptions include html that has CR char at end of each line of the html code. Also with hat HTML there are quote and comma characters. When I import the BC created file, excel treats the CR character as a new record.

I did find that some records with HTML were fine but others were not yet I could find no difference in the data even when viewing in hex mode editor

Greg

[Liam Dilley]

We have HTMl markup with youtube videos and more and they are fine Greg - lots of people are. In a normal working instance BC is fine with this and excel does not mess up the file like that.

That is the important thing to note, it is not something that if you have HTML in your description it will never work.

So you need to look at if there is any malformed html messing up the CSV, character encoding issues etc. Narrow down the clean products, get to the messy one, view in the admin and see if it does not have anything odd in the markup.

[Greg.Tomkins · Accepted solution]

Success... in a way. I have discovered what the problem was and certainly not one too many people may face... the length of my HTML text held in the description field was too big for Excel 2010 to import as a csv file. It seems that Excel 2010 will not import a column of text whose content is > 32759 characters (well that was the length of the field content) after which it puts the rest into a new row and then gets all upset form there.

I have however found a solution - open the CSV file in Google Sheets - Google Sheets does not seem to object at all to the size of the column data. After that I exported to both an Excel XLSX and CSV file format and the import all looked good.  Even taking the XLSX file and saving as CSV format was fine.

It seems the problem lies with Excel importing large text columns.

[Liam Dilley]

I do not think I have ever come across that and over reach of characters... Why do the products have so much in there?

[Greg.Tomkins]

This was an extraordinary circumstance where we have a series of technical tables of many columns and rows relating to the product and as a result the HTML is verrrry long….

Greg

[Liam Dilley]

Ahh Yeah, not a normal use case this one.