extract columns from a text file in vb

[jagpreet]

can anyone provide me with the script for extracting columns from a text file.
the script needs to be in visual basic

[hopalongcassidy]

What kind of text file are you talking about? A CSV file?

Hoppy

[jagpreet]

yeh its a csv file.
i heve attached it with this thread

[hopalongcassidy]

yeh its a csv file.
i heve attached it with this thread

As I understand it, the philosophy behind this and virtually all other forums I have either joined or observed is that the participants are there to help people over the stumbling blocks in their path to a solution, not to actually provide the entire answer. Doing that job is what I do to make a living. Providing help to people on DaniWeb is more in the nature of recreation. So, to help you, I will describe how you can approach the problem. You are going to have do the actual work.

First I would create a Class that provided services related to CSV files. It would do things like:

Open a CSV file.

Provide a method to create a Collection of "Column Headings", either from the first row of the CSV file or by an "AddColumnHeading" method that would add a column heading to an originally empty Column Heading Collection.

Provide a method that read the "Next Line" from the CSV file into a Collection that corresponds to the ColumnHeading Collection which would hold the data values for each column.

Provide a method that would return the data value of the current record from the CSV file from a given column heading.

Close the current CSV file.

_____________________________________

Then I would create an instance of the above class, set up its column headings and set up a loop that reads through the individual records of the CSV file. With each record read, I would access the data from the columns I was interested in.

I hope that this give you the idea of how to attack the problem.

Hoppy

[jagpreet]

thanks hoppy for ur advice..

i m trying to use the following code..........but it is giving some errors and moreover i just want to extract column no 4 and 6........how can i do this with this code?

lPublic Function SortCSVFileA(ByVal sSourceFile As String, ByVal sTargetFile As String, ByVal sOrderBy As String, Optional ByVal sDelimiter As String = ",", Optional ByVal bHasHeaders As Boolean = True, Optional ByVal iColumns As Long = 0) As Long
On Error GoTo Hell

Dim aFieldNames() As String
Dim Trash As String
Dim x As Long
Dim iFields As Long
Dim iFileNum As Long

' Open the original CSV file
Dim reader As StreamReader = _
New StreamReader("C:\DATA\d2.txt")

If bHasHeaders Then
' Get the field names
Line Input #iFileNum, Trash
Else
For x = 1 To iColumns
Trash = Trash & String$(5, Chr$(64 + x)) & sDelimiter
Next
Trash = Left$(Trash, Len(Trash) - Len(sDelimiter))
End If
aFieldNames = Split(Trash, sDelimiter)
iFields = UBound(aFieldNames) ' Counter

' Add field names to Recordset.
For x = 0 To iFields
oRS.Fields.Append(aFieldNames(x), adVarChar, 100) ' 100 characters length. add more if neccessary.
Next

' Open Recordset (must do after adding columns)
oRS.Open()

' Get the field values for the record
x = 0
Do While Not EOF(iFileNum)
Line Input #iFileNum, Trash ' Grab whole record
aRecords = Split(Trash, sDelimiter) ' Break it into individual fields
oRS.AddNew()
For x = 0 To UBound(aRecords)
oRS(x) = aRecords(x) ' Stuff values into recordset
Next
oRS.Update() ' Commit
Loop
Close #iFileNum

' Sorting criteria. If there are no headers, then we need to convert
' the index-based order list to the temporary field names we made up.
If Not bHasHeaders Then
Erase aRecords
aRecords = Split(sOrderBy, ",")
sOrderBy = aFieldNames(aRecords(0) - 1)
For x = 1 To UBound(aRecords)
sOrderBy = sOrderBy & "," & aFieldNames(aRecords(x) - 1)
Next
End If
oRS.Sort = sOrderBy

' Write out new CSV file
iFileNum = FreeFile
Open sTargetFile For Output As #iFileNum

' Recombine the header and write it out.
If bHasHeaders Then
Trash = ""
For x = 0 To iFields
Trash = Trash & aFieldNames(x) & sDelimiter
Next
Trash = Left$(Trash, Len(Trash) - Len(sDelimiter))
Print #iFileNum, Trash
End If

' Recombine the records into rows and write them.
Do While Not oRS.EOF
Trash = ""
For x = 0 To iFields
Trash = Trash & oRS(x).Value & sDelimiter
Next
Trash = Left$(Trash, Len(Trash) - Len(sDelimiter))
Print #iFileNum, Trash
oRS.MoveNext()
Loop

Hell: ' Cleanup
SortCSVFileA = Err.Number ' If any..
On Error Resume Next
Close #iFileNum
Erase aRecords
Erase aFieldNames
oRS.Cose() : oRS = Nothing
End Function

[hopalongcassidy]

The code you posted, apart from having a number of syntax errors does not really do what I understand you to want done (according to your first post). It reads a CSV file into a record set and sorts the records in the record set. But the architecture is all wrong. It doesn't provide any flexability. Furthermore, when the function returns all of the data is lost. You can't do anything with it.

I suggest you try the approach I described. You might want to enhance the class I described by adding a "Recordset" property that could be initialized in an init(rs as Recordset) subroutine.
You also might want to add a sort(keys as String) subroutine that would sort the recordset.

Take another look at the architecture I described. I think it will give you what you want.

Hoppy

[Diguelo]

Ive made a similar program up already for striping info out of registry keys and files for a game I paly.

The basic idea is:-

use FileSystemObject in your prog

create output file.
open yourtextfile for input to the program
open Newtextfile for output from the program

[
routine to clear out all the parts not needed, or find the parts needed.
You say you want columns 4 and 6 stripped out, so use a DO WHILE NOT EOF#1, LOOP
Then its pretty simple, you already have a search string in there to use,
the characters "," (use the chr codes to make the search string rather than typing it).
Use a Line Input to get each line,
then use FoundAt to find the start of each
search string (",") .
Then its a simple matter of deleting the parts of the line not needed by making the
search string the start of the next line, i used right(x,y) after id got the line length.
When you have the right piece of data next output it to the buildfile, then do the next
column.
]

close all

Your new buildfile should now contain the data in each line for alternate columns 4 and 6
each on new lines, open the file and read it back into list boxes if you wish.

Beauty of this is it leaves your original file unchanged for further reference.

Hope this helps.