Having a right nightmare with exporting Tab Delimited from 2003 to .txt format for Google Shopping Upload

[Rhothgar]

Hi

I have been tearing my hair out with this conundrum and still no nearer to solving.

We have a TXT feed from our website to Google Shopping which uploads every morning at 6:00am largely without errors.

From time to time, we need to edit items and re-upload during the day.

I have downloaded the file from our ecommerce host and then used Beyond Compare to compare it to the http link. It is 100% identical.

However, when we need to do an edit, I import external data into Excel 2003 and then save a Tab Delimited then do a manual upload. It has NEVER worked and here's why!

I make the amendments in Excel as it is fast to do and then save a Tab Delimited. I've also tried Unicode.txt format. Again, to no avail.

In some item descriptions, we may have size 3.5" x 4.5" and either Excel or Google Shopping is interpreting it as have quoted field delimiters!

When I import into Excel it automatically sets delimiters to ". I have changed it None. I tried it with " and even '. Again, no joy!

I have even tried saving to .txt format then opening the text file and checking the encoding in there and setting it to UTF-8.

Google Shopping can accept ASCII, ISO-8859-1, Windows-1252, UTF 8, UTF 16LE, XML and there is a quoted fields which can be ticked or unticked.

Any advice would be greatly appreciated. I've lost the will to live...

 

[Yongle]

Until you find a better alternative ...

search the description column for " and replace with inches

EITHER manually by selecting column and using Search & Replace OR use this simple macro to achieve the same result (amend refs as appropriate)

Sub ReplaceQuotes()
    ActiveSheet.Columns("K").Replace Chr(34), " inches"
End Sub

(inserting a space between the number and inches looks better IMHO)

 

[Rhothgar]

Thanks for this Yongle.

One issue I foresee is I have some HTML code pulling in a CSS sheet. Is it possible to amend the Macro so it only searches for quotes after the HTML by perhaps concatenating or ignoring the first x number of characters.

 

[Yongle]

How about only replacing quotes that are preceded by a number?

 

[Rhothgar]

That could work but the number might vary?