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

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

Attachments
"Universal      ","General                        ","System Wide                    ","NumCompatibilityFields         ","Integer        ",NULL,"Number of version fields to compare for release compatibility  ","2                              ","2                              ","0                              ","4                              ",0,NULL
"Universal      ","General                        ","System Wide                    ","LoadWeightFactorCPCI           ","Integer        ",NULL,"Load weight factor for CPCI CCMs                               ","1                              ","1                              ","1                              ","1                              ",0,NULL
"Universal      ","General                        ","System Wide                    ","LoadWeightFactorAXMP           ","Integer        ",NULL,"Load weight factor for AXMP CCMs                               ","1                              ","1                              ","1                              ","1                              ",0,NULL
"Universal      ","General                        ","System Wide                    ","LoadWeightFactorNETRA          ","Integer        ",NULL,"Load weight factor for NETRA CCMs                              ","6                              ","6                              ","1                              ","8                              ",0,NULL
"Universal      ","General                        ","System Wide                    ","UseNetCCNPowerUp               ","Char           ",32,"Indicates where NetCCN will be used to power up cards          ","0                              ","0                              ",NULL,NULL,0,NULL
"Universal      ","General                        ","System Wide                    ","WEMDocVersion                  ","Char           ",NULL,"WEM User Guide Version                                         ","4.0.0.1                        ",NULL,NULL,NULL,0,NULL
"Universal      ","General                        ","System Wide                    ","MonitoringTicket               ","Char           ",55,"Ticket Used for Monitoring                                     ","1X9z%$7jlk=pa43wYp85643f       ","1X9z%$7jlk=pa43wYp85643f       ",NULL,NULL,0,NULL
"Universal      ","General                        ","System Wide                    ","ServiceProviderId              ","Integer        ",NULL,"1-TMobile, 2-AWS, 3-Cingular, 4-NextTel, 7-CMCC, 18-UNICOM     ","0                              ","1                              ",NULL,NULL,0,NULL
"Universal      ","General                        ","System Wide                    ","MaxNumOfEmsUsers               ","Integer        ",NULL,"Max Number of provisioned EMS users in the system              ","40                             ","40                             ","1                              ","200                            ",1,NULL
"Universal      ","General                        ","System Wide                    ","MaxSimultEmsUsers              ","Integer        ",NULL,"Max Number of simultanous EMS login users                      ","40                             ","40                             ","1                              ","50                             ",0,NULL
"Universal      ","General                        ","System Wide                    ","TerminalServerIpAddr           ","Char           ",NULL,"Ip Address of Terminal Server                                  ","0.0.0.0                        ",NULL,NULL,NULL,0,NULL
"Universal      ","General                        ","System Wide                    ","TrafficControlFlag             ","Integer        ",NULL,"Flag to Enable/Disable Traffic Control Mechanism               ","0                              ","0                              ","0                              ","1                              ",1,"SGW_ISUP_ID WS_MNP_ID CS_SP_ID SGW_TCAP_ID INAP_SVR_ID LCS_ID CISS_ID SMS_ID MM_MM_ID                                           "
"Universal      ","General                        ","System Wide                    ","TraceEnabledFlag               ","Integer        ",NULL,"Flag to Enable/Disable Traces in the system                    ","0                              ","0                              ","0                              ","1                              ",1,"SGW_ISUP_ID CS_SP_ID CS_FM_ID OAM_MT_ID CP_CALLM_ID MM_MM_ID SGW_BICC_ID                                                        "
"Universal      ","General                        ","System Wide                    ","ResourceUsageHigh              ","Integer        ",NULL,"Resource Usage High Threshold                                  ","85                             ","85                             ","0                              ","100                            ",0,NULL
"Universal      ","General                        ","System Wide                    ","ResourceUsageNormal            ","Integer        ",NULL,"Resource Usage Normal Threshold                                ","70                             ","70                             ","0                              ","100                            ",0,NULL
"Universal      ","General                        ","System Wide                    ","SS7_Network_Id                 ","Integer        ",4,"Default SS7 Network Id; 0 is not allowed                       ","6                              ","1                              ","1                              ","255                            ",1,"MM_MM_ID CP_CALLM_ID SMS_ID CISS_ID LCS_ID INAP_SVR_ID CS_SP_ID MM_IS41_ID SMS_IS41_ID SPM_IS41_ID                              "
"Universal      ","General                        ","System Wide                    ","LIServerType                   ","Integer        ",4,"LI Type(CALEA=1-USA,A1357_SORM=2-Europe/Russia,A1357=3-China)  ","1                              ","1                              ","1                              ","255                            ",0,NULL
"Universal      ","General                        ","System Wide                    ","VirtualMscVlrFlag              ","Integer        ",4,"Virtual MSC VLR flag                                           ","0                              ","0                              ","0                              ","1                              ",0,NULL
"Universal      ","General                        ","System Wide                    ","egcp_dscp                      ","Integer        ",NULL,"DS codepoint for control plane EGCP traffic                    ","0                              ","0                              ","0                              ","63                             ",1,"BS_DM_ID                                                                                                                        "
"Universal      ","General                        ","System Wide                    ","mp_dscp                        ","Integer        ",NULL,"DS codepoint for management plane traffic except SNMP interface","0                              ","0                              ","0                              ","63                             ",1,"OAM_CFG_ID OAM_AMA_ID OAM_PFM_ID CS_TFTPD_ID                                                                                    "
"Universal      ","General                        ","System Wide                    ","sshpath                        ","Char           ",32,"ssh Path in Solaris 10                                         ","/usr/bin/ssh                   ","/usr/bin/ssh                   ",NULL,NULL,0,NULL
"Universal      ","General                        ","System Wide                    ","CMNIndicator                   ","Integer        ",NULL,"Call Mediate Net Indicator                                     ","0                              ","0                              ","0                              ","255                            ",0,NULL
"Universal      ","General                        ","System Wide                    ","Mtp3M3UASamePC                 ","Integer        ",NULL,"Indicator of MTP3 M3UA with same point code (0: off 1:on)      ","0                              ","0                              ","0                              ","255                            ",0,NULL
"CPSNodes       ","Base Platform                  ","Program Manager                ","Olcl1ThrtlUpThrshld            ","Integer        ",NULL,"OLCL 1 Throttle Up Threshold                                   ","40                             ","40                             ","0                              ","100                            ",1,NULL
"CPSNodes       ","Base Platform                  ","Program Manager                ","Olcl2ThrtlUpThrshld            ","Integer        ",NULL,"OLCL 2 Throttle Up Threshold                                   ","40                             ","40                             ","0                              ","100                            ",1,NULL

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

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

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

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.

This article has been dead for over six months. Start a new discussion instead.