Strip out tab characters in a text field 2

[imterpsfan3]

I have a text field that delimits using a tab character. For example, the field will have one data item, a tab character, another data item, another tab character.

What I want to do is iterate through the field character by character and when I get to a tab character I add the data between the tab characters to another field.

I've tried to use something like this to no avail:

For i = 1 To Len(text1)
   if Mid$(Text1,i,1) = vbTab Then
     'do some processing
   end if
Next

 

[gmmastros]

You could replace the tab character with nothing.

Ex.

Dim strWithoutTabs as string
dim strWithTabs as string

strWithTabs = "a" & vbtab & "B" & vbtab & "C"
strWithoutTabs = replace(strWithTabs, vbTab, "")



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Golom]

If there are always at least 2 tab characters

myOtherField = Split ( strWithTabs, vbTab )(1)

 

[imterpsfan3]

This is what the data in a row looks like:
Project[2.2] tab Project[2.3] tab Project[2.4]

What I need to do is make a row for each project so that it looks like this:
Project[2.2]
Project[2.3]
Project[2.4]

There are two approaches I can see:
1- use a SQL Server Cursor to iterate through each row and strip out the project and insert into a new row

2- use code in VB/ADO to iterate through each row and strip out the project and insert a new row

I know this can be done, just not sure how.

 

[gmmastros]

Do you need to write this data back to the database, or do you need to show the data to the user?

If you need to save this data back to the database, then I would suggest you write a stored procedure to do the work for you.

If you need to show the data to the user on seperate lines, then I would do the work in VB.

In VB, you could replace the tab character with a carriage return/line feed.

strFormated = replace(strData, vbTab, vbCrLf)

If you need to manipulate the data, then you can use the split function that Golom used. The split function will return an array representing your data.

dim arData() as String
dim i as long

arData = split(strData, vbTab)
For i = lbound(arData) to ubound(arData)
debug.pring arData(i)
Next

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[imterpsfan3]

Thank both of you for your help. The Split function seems to be the answer for what I need to do. I actually discovered that the character was a line feed instead of a tab, so I use vbLf.

I'm going to do this with code, but I'd also like to do this via a stored procedure. The stored procedure method should be faster.

In order to do all the processing in a stored proc, I'd have to use a CURSOR right? Because essentially I'm having to loop through each record, parse the data then insert records into another table, move to the next record, etc...

 

[gmmastros]

You would not need to use a cursor to get the results from a SQL Server stored procedure. There are other things you can do instead that will give you better performance.

To decide on the best approach on the database side, you'll need to know a little more about the data. For example, will there always be 3 data sections (i.e. 2 vbLf's) in your data?

I have a 'splitter' stored procedure that can create multiple records from a single value. If you share some sample data and expected results, we may be able to help better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[imterpsfan3]

This is how I resolved the issue in code using VB/ADO:

Private Sub lblUpdateProjectVersions_Click()
    
    'Setup error handling
    On Error GoTo lblUpdateProjectVersions_Click_Err
    
    Set objRec = New ADODB.Recordset
    Set objCmd = New ADODB.Command
    
    objRec.Open "qryNewAISProjects", objConn _
        , adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
        
    
    
    
    'Iterate through the recordset one row at a time
    Do Until objRec.EOF
        'Step 1: Parse the Projects field into a array for futher
        'processing
        
        strData = objRec("Projects")
        
        arData = Split(strData, vbLf)
        
        'Step 2: Loop through the array item by item, inserting
        'each item into a separate row in tblProjectVersions
        For i = LBound(arData) To UBound(arData)
             With objCmd
                .ActiveConnection = objConn
                .CommandType = adCmdStoredProc
                .CommandText = "qryInsert_ProjectVersion"
                .Execute , Array(objRec(0), arData(i)), adExecuteNoRecords
             End With
            
           
        Next
        
        objRec.MoveNext
    Loop
   
    objRec.Close
    MsgBox "All done!"
lblUpdateProjectVersions_Click_Exit:
    Set objCmd = Nothing
    Set objRec = Nothing
    Exit Sub
    
lblUpdateProjectVersions_Click_Err:
    'Alert the user that an error occurred
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume lblUpdateProjectVersions_Click_Exit
    
End Sub

 

[gmmastros]

That's a nice bit of code. How long does it take to run?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[imterpsfan3]

The code took 5 seconds to run. I had to loop through 200 records, parse out the data with the split function on each row. Then loop through each item in the array and append the data to another table, which amounted to 650 insertions.

There is probably a faster, more efficient way to do this, but this is what I came up with.

 

[imterpsfan3]

Actually, I found that the reason it took the 5 seconds was because the table was indexed. When doing mass insertions, I like to take out the index. Without the index, it took 1 second to run.

 

[gmmastros]

If you are satisfied with the performance, then I will simply say, "Thanks for the star".

If you want to try speeding this up a little, let me know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vbSun]

George, can I see what you are thinking to speed this up?

------------------------------------------
The faulty interface lies between the chair and the keyboard.

 

[gmmastros]

I have a sql server UDF (user defined function) that can take a delimited string and convert it in to records. This can be run from within a stored procedure in Microsoft SQL Server. This method allows all of the work to be done on the server. Using the ADO method, all the data needs to be copied from the server to the client, parsed apart, and then sent back through the network to be stored in the database. By doing all the work on the server, you save on network time.

I didn't suggest this earlier because I am a firm believer that you should not optimize code for efficiency at the time of writing it. You should first make the code functionally accurate (so it does what it is supposed to). Then, you find the bottlenecks in the app and work on improving execution time.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[imterpsfan3]

The code I wrote worked for my purposes because I wasn't dealing with huge amounts of data. I welcome a SQL Server solution with open arms. If you can provide a code sample that would be great. I think one should always try to scale any code or application to its highest degree.