Modifying large CSV files

[Guest]

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_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"

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!

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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 here:
http://incolor.inetnebr.com/zechiles/destination.csv

and here: 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!

 

[Guest]

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

 

[Guest]

Hi Joel,

Forgive my ignorance! But how do I use this and/or what type of code is this?

Sorry to be so dense... maybe the coffee hasn't kicked in yet.

Thanks again!

 

[Guest]

Ok Joel,

I had to give myself a crash course in macros... but I was able to get it to
run.

The output, however, is a bit different than expected:
http://incolor.inetnebr.com/zechiles/snipped.GIF

Any ideas? And thanks in advance!

 

[Guest]

I just got up from a long night. should of seen the problem last night try
this. I stoped the program from putting the 3 commars on the 1st line
containing jpg file names.

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
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

 

[Guest]

Works like a champ, Joel!

Thanks so much!

Cheers,

Michael