**SPREADSHEET GURUS** HELP!

[FiberGuy]

Help seems to be my latest theme these days... 

This is somewhat related to my other thread about importing thousands of images, but I wanted to give this issue particular attention.

To put my question simply, how to I change this:

Code:

[DETAILED_IMAGES],,,
!PRODUCTID,!PRODUCTCODE,!PRODUCT,!IMAGE
510041235,510041235,FRIENDS Chevrolet Magazine March 1941 - Vivien Leigh,"P9225543_vintage_magazines_.jpg,P9225543a_vintage_magazines_.jpg,P9225544_vintage_magazines_.jpg,P9225544a_vintage_magazines_.jpg,P9225544aa_vintage_magazines_.jpg"
506230710,506230710,1958 Dodge - Christmas Music Record - Lawrence Welk,"P30520231.jpg"
506230646,506230646,Vintage Mentholatum Ink Blotter ~ Magellan ~,"PA3100901.jpg,PA3100911.jpg,PA3100921.jpg"
506230650,506230650,Happy Indian Girl Paper Mask Cir. 1950,"PA0843431.jpg,PA0843471.jpg,PA0843481.jpg"

To this:

Code:

[DETAILED_IMAGES],,,
!PRODUCTID,!PRODUCTCODE,!PRODUCT,!IMAGE
510041235,510041235,FRIENDS Chevrolet Magazine March 1941 - Vivien Leigh,P9225545_vintage_magazines_.jpg
,,,P9225543a_vintage_magazines_.jpg
,,,P9225544_vintage_magazines_.jpg
,,,P9225544a_vintage_magazines_.jpg
,,,P9225544aa_vintage_magazines_.jpg
506230710,506230710,1958 Dodge - Christmas Music Record - Lawrence Welk,P30520231.jpg
506230646,506230646,Vintage Mentholatum Ink Blotter ~ Magellan ~,PA3100901.jpg
,,,PA3100911.jpg
,,,PA3100921.jpg
506230650,506230650,Happy Indian Girl Paper Mask Cir. 1950,PA0843431.jpg
,,,PA0843471.jpg
,,,PA0843481.jpg

For reference:  http://dewittco.com/origin.csv & http://dewittco.com/destination.csv

I'm prepared to offer a *reward* for something that works! 

[regic]

I don't know but most spreadsheet programs should be able to convert it automatically for you.

If you have a large spreadsheet download gnumeric because it can handle more rows and columns of data than Excel.  It's free:
http://www.gnome.org/projects/gnumeric/downloads.shtml

[Angnor]

Hello FiberGuy,

If you just want to remove certain values from the spreadsheet - you can find a lot of solutions for  this. Even use Google Docs and Spreadsheets
but after that modification your spreadsheet can't be imported into x-cart using default functionality. Each image should be binded to some product by productid or productcode (SKU).
And as far as I see there will be images which are not binded to products.

I suppose you need a custom solution.

[FiberGuy]

A kind gentleman at the excel user groups message board wrote me an Excel macro for this, and it works like a champ!  This will save many hours of work, no doubt!

Code:

Sub ConvertCSV()

   Const Sourcefile = "C:\Documents and Settings\Michael\Desktop\Origin.csv"
   Const Destfile = "C:\Documents and Settings\Michael\Desktop\Destination.csv"
   Const ForReading = 1
   Const ForWriting = 2
   Const ForAppending = 3
     
   Set OriginCSV = _
      CreateObject("Scripting.FileSystemObject")
   Set FOrigin = _
      OriginCSV.GetFile(Sourcefile)
   Set FSOrigin = _
      FOrigin.OpenAsTextStream _
         (ForReading)

       
   Set DestinationCSV = _
      CreateObject("Scripting.FileSystemObject")
   DestinationCSV.CreateTextFile Destfile
   Set DestinationCSV = DestinationCSV. _
       GetFile(Destfile)
   Set FSDestination = DestinationCSV. _
      OpenAsTextStream _
         (ForWriting)
   
   
   
   Do While FSOrigin.ATENDOFSTREAM = False
       
       
       InputString = FSOrigin.readline
       
       'If no JPG on line just write the data
       If InStr(InputString, ".jpg") = 0 Then
          FSDestination.writeline InputString
       
       Else
          'Loop until no more characters in line
          First = True
          Do While Len(InputString) > 0
             'check if jpg is in the line
             GetJPGPos = InStr(InputString, ".jpg")
             ' exit if no more jpg to strip out
             If GetJPGPos = 0 Then Exit Do
             
             'Get everything before the first commar
             OutputString = Left(InputString, _
                GetJPGPos + 3)
           
             If First = False Then

                'Add three commars to begionning of line
                OutputString = ",,," + OutputString
             Else
                 First = False
             End If

             'write string to output file
             FSDestination.writeline OutputString
             
             'Get everything to the right of 1st commar
             InputString = Mid(InputString, _
                GetJPGPos + 5)
         
          Loop
               
       End If
   Loop
   
   FSOrigin.Close
   FSDestination.Close

End Sub