Modify a CSV file

  • Thread starter Thread starter Jchick
  • Start date Start date
J

Jchick

Firstly, I am a noobie with some exposure to VB.net

Secondly, I am tasked with trying to do the following:

I have a CSV file that I'd like to have a vb.net program make a change
to:

Here is a line from the CSV file:

"3/16/2006
2:45:24PM","c:\check21\$_314002__231387576_8888880013__0000200000_$_A.tif","<314002<:231387576:8888880013<;0000200000;"

I'd like to rearrange each line to look like this:

"3/16/2006 2:45:24
PM","314002","231387576","8888880013","0000200000","c:\scans\$_314002__231387576_8888880013__0000200000_$_B.tif"

So, I think I need to do this:
1. Cut the 2nd field (c:\scans\...etc) and put it on the end
2. Then replace the "<" characters with nothing.
3. Then replace the ":" characters with a ","
4. Then replace the ";" characters with a ","
5. Write the result to a new CSV file

I think it will give the result I am looking for.

I am such a noob that I don't really know how to start. Is there any
code already written for this that I can modify?

Thanks for any assistance
 
Jchick said:
Firstly, I am a noobie with some exposure to VB.net

Secondly, I am tasked with trying to do the following:

I have a CSV file that I'd like to have a vb.net program make a change
to:

Here is a line from the CSV file:

"3/16/2006
2:45:24PM","c:\check21\$_314002__231387576_8888880013__0000200000_$_A.tif","<314002<:231387576:8888880013<;0000200000;"

I'd like to rearrange each line to look like this:

"3/16/2006 2:45:24
PM","314002","231387576","8888880013","0000200000","c:\scans\$_314002__231387576_8888880013__0000200000_$_B.tif"

So, I think I need to do this:
1. Cut the 2nd field (c:\scans\...etc) and put it on the end
2. Then replace the "<" characters with nothing.
3. Then replace the ":" characters with a ","
4. Then replace the ";" characters with a ","
5. Write the result to a new CSV file

I think it will give the result I am looking for.

I am such a noob that I don't really know how to start. Is there any
code already written for this that I can modify?

Thanks for any assistance
Click to expand...

I was getting ready to further assist with your last request and now I see
this. Does this supercede your last question or is this further
explanation?
 
Well, its the same project. The current system produces this CSV file.
I am not sure which is easier, creating a new CSV based on the files
crated or modifying the
'jumbled' CSV. What do you think?
 
Jchick said:
Well, its the same project. The current system produces this CSV file.
I am not sure which is easier, creating a new CSV based on the files
crated or modifying the
'jumbled' CSV. What do you think?
Click to expand...

Assuming that the date/time is not critical, I would just recreate the CSV
from the file name as the filename appears to have all on the index data
encoded in the name. You ware given examples of how to read the file names
from the directory and several ways to extract the index fields from the
name. Now all you need to do is open a file to write the data to and for
each file processed, write a new line to the CSV file. When you are done,
close the CSV file. That's all there is to it.

Do you need more?

HTH
 
I might be over my head on this. I can do it in another program that
allows really easy search-and-replace but I guess I wanted to try this
in VB.

So, ya, I need more help. I am not sure how to open the file to write
the data and write a new line to the new file.
 
Hi Jchick,
I had some spare time that I didn't know what to do, so I went ahead and
write this little program that does exactly what you've asked for....
Open your VS IDE, create a new project\console application then paste the
following code into Module1.
I suppose you have the old CSV file as "C:\oldCSV.csv", so you'll need to
change this path to reflect the actual path to the CSV file.

Code starts
========================================
Imports System.IO
Imports System.Text

Module Module1

Sub Main()
Dim sepChar As Char = ","
Dim oldCSVFile As String = "C:\oldCSV.csv"
Dim newCSVFile As String = "C:\newCSV.csv"
Dim newCSVFileContents As String = ReArrangeCSV(oldCSVFile, sepChar)
Call WriteNewCSVFile(newCSVFile, newCSVFileContents)
End Sub

Private Function ReArrangeCSV(ByVal fileFullPath As String, ByVal
seperator As Char) As String
Dim output As String = ""
Dim line As String
Dim temp As String = ""
Dim i As Integer
Dim fieldValues, parts As String()
Dim f As IO.File
Dim myReader As IO.StreamReader
Dim oldLine As String = ""
Try
'Open file and read first line to determine how many fields
there are.
myReader = f.OpenText(fileFullPath)
While myReader.Peek() <> -1
oldLine = myReader.ReadLine()
fieldValues = oldLine.Split(seperator)
'Grab the first field since there's no change to it
line = fieldValues(0)
'Grabing the 3rd field and parse it
temp = fieldValues(2).Replace("""", "") 'Remove the
quotation marks
temp = temp.Replace("<", "") 'Remove the < marks
temp = temp.Replace(":", ";") 'Replace commas with semi-colons
'Split it into parts
parts = temp.Split(";")
'Now add each part to the line
For i = 0 To parts.Length() - 1
If parts(i).Trim <> "" Then
line &= "," & """" & parts(i) & """"
End If
Next
'Adding 2nd field to the end
line &= "," & fieldValues(1)
'Adding this line to the output
output &= line & vbCrLf
MsgBox(output)
End While
Catch ex As Exception
MsgBox("Error Re-Arrange CSV fields: " & ex.Message)
Return ""
Finally
myReader.Close()
End Try
Return output
End Function

Private Sub WriteNewCSVFile(ByVal newFileName As String, ByVal
fileContents As String)
Dim f As File
Dim oWrite As System.IO.StreamWriter
Try
If f.Exists(newFileName) Then
'If file exists then open for appending.
'You can just overwrite it by putting in 'False' instead
oWrite = New StreamWriter(newFileName, True)
Else
'If file doesn't exist, create it
oWrite = f.CreateText(newFileName)
End If
'Write contents to file
oWrite.Write(fileContents)
Catch ex As Exception
'Catch error here
MsgBox("Can't write new CSV file: " & ex.Message)
Finally
oWrite.Close()
End Try
End Sub
End Module
====================================

Hope this helps,
VHD50.
 
Although I cannot contribute to this thread in terms of assistance, I just
wanted to add that how much in awe I am at times at the help that is
available. If it wasn't for newsgroups and the priceless information that
people so willingly give, I don't know what I would do. The time and effort
that the mvp's and others give is truly humbling.

I too am a newbie to vb .net and spend so much time reading and trying
things that you cannot find very easily in books.

What you did VHD by creating that code was a really decent thing and I hope
in time and some more experience I too can pass on some information to
others when I know more.

Cheers
Michelle
 
Geez, VHD50, you are truly awesome! Sorry it took so long for me to
respond, but I just read your post.

Thanks for the code, it works great.

I was able to make it work a different way - but there is more then one
way to skin a cat.

Thanks again!!!

Jchick
 
Back
Top