Complicated Find & Replace on a CSV

[Mike Hughes]

Out of interest I tested my code and it works fine.

Just remember to change the "'/" as "\" for directory paths and set X to the correct column.

Mike

[eighteentee]

Out of interest I tested my code and it works fine.

Just remember to change the "'/" as "\" for directory paths and set X to the correct column.

Mike

Sorry for the late reply but thank you! I'm just going to give this a shot now...

[eighteentee]

I read this as you have data in an Excel column like:

\NOT_CATALOG\mydir\mysubdir\durble.jpg


Where the mydir\mysubdir bit could vary a lot.

And you want:

durble.jpg

Here's how to do it using Notepad++

In Excel, copy / paste the column containing these files to the clipboard.

Paste into Notepad++.

We should now have a list of filenames like.

\NOT_CATALOG\mydir\mysubdir\durble.jpg
\NOT_CATALOG\mydir\mysubdir2file\wurble.jpg

Now in NotePad++ click Control/F and select Replace.

Set Find what to \\NOT_CATALOG\\.*\\
Set Replace with blank
Tick Regular Expression

Click Replace All.

All should change to strip the everything but the filename. E.g.

durble.jpg
wurble.jpg

Now copy to clipboard, go back into Excel, click the first entry in the filename column and do Paste.

Repeat for any other Excel columns that might contain such filenames.

NOTES:

If you want to add a sub-folder path to these resulting file names then set

Replace with to SubFolder\

And we'll end up with

SubFolder\durble.jpg
SubFolder\wurble.jpg

TECHNICAL NOTE:

I'm doing this on individual Excel columns, rather than directly on the .CSV file as the Notepad++ regexp engine does not support shortest length matching and we cannot risk this on lines that might contain more than one \NOT_CATALOG\ filename as we'd inadvertantly gobble up all the text between the first \NOT_CATALOG\ and the final ones filename.

In the interests of keeping this simple, I opted to try Normans version first. The problem is now the file isn't readable by Actinic even though it is still a CSV. Technically, the only difference between my 'before' and 'after' files is that there is no folder structure prior to the image filename. Actinic is complaining that there 'are no fields found in the first line of the file'. Like I said, these files are identical aside from the image file name folders names being removed.

[Chunkford]

What version of excel are you using? I'm guessing 2007

[eighteentee]

What version of excel are you using? I'm guessing 2007

Aye - I'm guessing 2007 has issues with CVS's then...

[Chunkford]

Yea it does

You will need to open the saved csv file in notepad++ (or similar) and find the header lines and remove all the ,,,,,,,,,,,,,,,, from the end of the lines. For some reason it adds extra , so it matches the lengths to the longest header line you have

[eighteentee]

Yea it does

You will need to open the saved csv file in notepad++ (or similar) and find the header lines and remove all the ,,,,,,,,,,,,,,,, from the end of the lines. For some reason it adds extra , so it matches the lengths to the longest header line you have

Is OpenOffice 3.1 any good for this?

[Chunkford]

Never used it so can't say. Sorry

[Chunkford]

It's quiet simple to do tho.
Just open the csv file in notepad++ and you will see at the top something like this

Header:Section,Section name,Section Description,Section Image,Section Meta Description,,,Section Meta Keywords,Section Included in Search,Section Hidden on Web Site,Page Name,IsDeleted,Section Use Parent Settings,Section Single Add To Cart Button,Section Quantity Placement,Exclude From Google Product Search Data Feed,Page Title,Include to Site Map,Content Category,StandardLayout,ExtendedInfoPageDesign,ExtInfoWindowWidth,ExtInfoWindowHeight,ProductLayout,ProductList,SingleAddToCartButton,SubSectionListTop,MainArea,SectionLink,ProductImageLayout,PriceLayout,AttributeLayout,AttributeList,ComponentLayout,ComponentList,FragmentImage,SubSectionListBottom,SectionLinkColumnCount,ProductColumnCount,SectionLinkFirstRowColumnCount,ExtendedInformationLinks,ExtendedInformationType,MaxSize,EngravingCharge,PencePerChar,FreeChars,Line5Length,Line4Length,Line3Length,Line2Length,Line1Length,Overlap,SectionNav,SectionNumber,FooterCollection,StarBuy,NewProduct,DeliveryBand,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Header:Product,Product Reference,Short Description,Full Description,Report Description,Eng-Charge,Ex VAT Price,Price,Image Filename,Can be Ordered Online,Max Quantity Orderable,Min Quantity Orderable,Date Prompt Text,Other Info Prompt,Detail URL or File,Detail Link Text,Hide on Web Site,Shipping Data,Price Text,Button Text,Use External Data,Stock Checks Enabled,Stock in Hand,Stock Warning Level,Stock Suspend Level,Pricing Model,Ship Separately,V4 Tax 1 Data,IsDeleted,No Order Line For Main Product,Date Prompt Minimum Year,Date Prompt Maximum Year,Generate Popup Page,Extended Info Text,Extended Info Image,Display By Image,Display By Link,Display By Button,Extended Info Page Name,Extended Info Link Text,Digital Download File Name,Auto Ship,Product Group ID,Product Type,Original Product Reference,Product Duplicate Index,Fragment Layout,Linked Item ID,Link Type,Is Linked,Fragment Title,Fragment Text,Fragment Image File Name,Image Clickable,Link Text,Exclude From Google Product Search Data Feed,Thumbnail,nContentCategory,Include in BestSellers List,Include in New Products List,Include in Also Bought Lists,Share with Actinic EPOS,Barcode,Exclude From Shipping,Shipping Supplement Applied Once,Shipping Supplement,Handling Supplement Applied Once,Handling Supplement,Alternative Weight,Shipping Category,Shipping Quantity,Cost Price,ExtendedInfoPageDesign,ExtInfoWindowWidth,ExtInfoWindowHeight,ProductLayout,ProductImageLayout,PriceLayout,AttributeList,AddToCartButton,ComponentList,ProductPriceDescription,CartButtonText,ExtendedInformationLinks,AttributeColumnCount,ExtendedInformationType,PushButtonGrid,NumberAttributesInPushButtonGrid,YourPricePrefix,RetailPricePrefix,Engraving Option,MaxSize,EngravingCharge,PencePerChar,FreeChars,Line5Length,Line4Length,Line3Length,Line2Length,Line1Length,Overlap,StarBuy,NewProduct,DeliveryBand,MOTOProductLayout,OnlinePriceLayout
Header:Price,Customer Group,Price,Generated,Can be Ordered,,,Product Visible,Minimum,Customer Message,IsDeleted,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Just remove what is in red for example

[eighteentee]

The problems I'm having with this seem to be never ending!

I managed to get the CVS sorted (thanks to an old version of Excel) but when I import, the full description from all the products is missing! It's definately mapped correctly during the import but all that's in there is 'Price £xx.xx' or the very last part of the description.

Huh?

[Duncan Rounding]

Sounds like you have line feeds in your description and Excel is only seeing the last line.
Check in Notepad++ and see if the data is actually saved correctly in the CSV first.

[eighteentee]

Sounds like you have line feeds in your description and Excel is only seeing the last line.
Check in Notepad++ and see if the data is actually saved correctly in the CSV first.

Hmmm. Looking at the document I can't see any line feeds. I need to dig around some more...

[AndyBorrett]

Having a commar in your description would cause the problems you are seeing too

[eighteentee]

...so if anyone out there ever places commas in their product descriptions, what you're saying is that an import / export will always fail?

Eeeeek!

[Mike Hughes]

AFAIK you can use commas in the description but they have to be enclosed within quotes to avoid being seen as separators.

See http://community.actinic.com/showthread.php?t=12828

Mike