Mike: Below is the code. Pretty simple. It help keep me awake. Just
monitoring some tests for my boss.
The code doesn't put any data into an excel spreadsheet. Instead it it
reads an input file c:\temp\origin.csv
and creates an output file called c:\temp\destination.csv. You can changes
these filenames to anything you
want.

Sub ConvertCSV()

Const Sourcefile = "c:\temp\Origin.csv"
Const Destfile = "c:\temp\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
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)

'Add three commars to begionning of line
OutputString = ",,," + OutputString

'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


"Mike" wrote:

Hi again,

I'm not sure this will work, but here is a snapshot of the original file:
http://incolor.inetnebr.com/zechiles/origin.GIF

And here is a snapshot of what I need the original to look like:
http://incolor.inetnebr.com/zechiles/destination.GIF

The details of the desired (destination) spreadsheet are as follows:

!PRODUCTID: always one number
!PRODUCTCODE: always one number
!PRODUCT: always a single descriptive string of text
!IMAGE: anywhere from one to 12 image files, usually five or less

You can also view a snippet of each spreadsheet he
http://incolor.inetnebr.com/zechiles/destination.csv

and he http://incolor.inetnebr.com/zechiles/origin.csv

I hope this is making some sense... let me know if you need more info..

... and thanks so much for looking at this!






"Joel" wrote:

I'm not clear of the modifications. It looks like you want to take any of
the jpg items and put them on a seperate line with 3 commas in front of these
lines? I'm working 3rd shift tonight and have some time to do a problem like
this. Reply and give better description in words and I will try to get it
done tonight.

"Mike" wrote:

Hi Joel,

Thanks for the reply.

I should have put my question better. This will be a "one time only"
modification, but the original file is quite large.

I should have asked if there is another way to modify this file short of
manually editing each line.




"Joel" wrote:

A CSV file is a text file that can be manually editied. If you ae only
making these changes once and it is a small amount of changes then use
Notepad editor. Open the files and select type as being all. Make the
changes as needed and save the file.

For large changes or changes that are going to be made often, then a macro
would be recommended.

"Mike" wrote:

Hello,

I'm an Excel novice, as I only work with it when I *have* to.

I need to convert a comma delimited file to a slightly different format. It
goes like this:

Original Format:
!PRODUCTID,!PRODUCTCODE,!PRODUCT,!IMAGE
510041235,510041235,FRIENDS Chevrolet Magazine March 1941 - Vivien
Leigh,"P9225543_vintage_magazines_.jpg,P9225543a_v intage_magazines_.jpg,P9225544_vintage_magazines_. jpg,P9225544a_vintage_magazines_.jpg,P9225544aa_vi ntage_magazines_.jpg"
506230710,506230710,1958 Dodge - Christmas Music Record - Lawrence
Welk,"P30520231.jpg"

Desired Format:
!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

Any ideas?

Thanks in advance for any suggestions!