Sign in to follow this  
Followers 0
felanor

String Replace

6 posts in this topic

Hi everyone,

I have a feeling this will be a simple fix, but, alas, it escapes me at the moment. Heres the situation.

I have successfully scraped some data off of a website and placed into the following CSV format. However, I need to remove the text to the right of the "|" on each line. If anyone could help, that would be great!

45.00,QHBLI.X,171.50|2153
50.00,QHBLJ.X,138.10|1117
55.00,QHBLK.X,134.90|3119
60.00,QHBLL.X,158.30|26208
65.00,QHBLM.X,140.00|1237
70.00,QHBLN.X,122.00|2211
75.00,QHBLO.X,130.70|4423
80.00,QHBLP.X,108.30|11,052
85.00,QHBLQ.X,119.00|3196
90.00,QHBLR.X,119.20|11,465
95.00,QHBLS.X,109.80|122,311
100.00,QHBLT.X,117.00|101,780
105.00,QHBLA.X,107.30|12509
110.00,QHBLB.X,95.70|111,026

~Felanor

Share this post


Link to post
Share on other sites



Hi everyone,

I have a feeling this will be a simple fix, but, alas, it escapes me at the moment. Heres the situation.

I have successfully scraped some data off of a website and placed into the following CSV format. However, I need to remove the text to the right of the "|" on each line. If anyone could help, that would be great!

45.00,QHBLI.X,171.50|2153
50.00,QHBLJ.X,138.10|1117
55.00,QHBLK.X,134.90|3119
60.00,QHBLL.X,158.30|26208
65.00,QHBLM.X,140.00|1237
70.00,QHBLN.X,122.00|2211
75.00,QHBLO.X,130.70|4423
80.00,QHBLP.X,108.30|11,052
85.00,QHBLQ.X,119.00|3196
90.00,QHBLR.X,119.20|11,465
95.00,QHBLS.X,109.80|122,311
100.00,QHBLT.X,117.00|101,780
105.00,QHBLA.X,107.30|12509
110.00,QHBLB.X,95.70|111,026

~Felanor

Untested, but something like this should work. $result = StringLeft($input,StringInStr($input,"|")). You might want to subtract 1 from the StringInStr result if you also want to remove the pipe sign itself.


Roses are FF0000, violets are 0000FF... All my base are belong to you.

Share this post


Link to post
Share on other sites

StringRegExp($_STRING,(.*?)(?:\|.*?))

Where $_STRING is your string. This will return an array where each line of the array will be a line of your string, minus the "|" character, and anything following it. Similar to using the StringSplit command. Works well if read from another file.

Share this post


Link to post
Share on other sites

Hi everyone,

I have a feeling this will be a simple fix, but, alas, it escapes me at the moment. Heres the situation.

I have successfully scraped some data off of a website and placed into the following CSV format. However, I need to remove the text to the right of the "|" on each line. If anyone could help, that would be great!

45.00,QHBLI.X,171.50|2153
50.00,QHBLJ.X,138.10|1117
55.00,QHBLK.X,134.90|3119
60.00,QHBLL.X,158.30|26208
65.00,QHBLM.X,140.00|1237
70.00,QHBLN.X,122.00|2211
75.00,QHBLO.X,130.70|4423
80.00,QHBLP.X,108.30|11,052
85.00,QHBLQ.X,119.00|3196
90.00,QHBLR.X,119.20|11,465
95.00,QHBLS.X,109.80|122,311
100.00,QHBLT.X,117.00|101,780
105.00,QHBLA.X,107.30|12509
110.00,QHBLB.X,95.70|111,026

~Felanor

As the two replies before me pointed out, StringInStr and StringSplit are good tools for what you want. I would implement it like this, (if the information is within a file and is formated like the shown example, line feed at end of the line):

$csv=FileRead("data.csv")                           ; Where "data.csv"is your DATA file
$a=StringSplit($csv, @CRLF, 1)
$NumOfEntries=$a[0] + 1
Dim $b[$NumOfEntries]
For $t = 1 to $NumOfEntries - 1
    If $a[$t]<>"" Then
        $b[$t]=StringMid($a[$t],1,StringInStr($a[$t],"|")-1)
        MsgBox(0,"",$b[$t])                                 ; Just for viewing the results
    endif
Next

I've tested this with your information & it seems to work fine. Hope this is helpful. Cheers!


"Intelligence is the ability to adapt to change."                                      - Stephen Hawking                                        "...not the ability to exploit others."                                                  - OldCoder

Share this post


Link to post
Share on other sites

BTW, if you are not getting the results you want, the line feeds may be just a carriage return or just a line feed. Try replacing line two of my script with one of the following:

$a=StringSplit($csv, @CR) ; for single carriage return

$a=StringSplit($csv, @LF) ; for single line feed

Good luck. :P


"Intelligence is the ability to adapt to change."                                      - Stephen Hawking                                        "...not the ability to exploit others."                                                  - OldCoder

Share this post


Link to post
Share on other sites

OK, a final note. If you wanted to save the data in a new file, here's the same script with a save feature. I wasn't sure of your scripting abilities at this point and thought I might want to update my earlier script.

Here it is:

$csv=FileRead("data.csv")   ; Where "data.csv"is your DATA file
$a=StringSplit($csv, @CRLF, 1)
$NumOfEntries=$a[0] + 1
Dim $b[$NumOfEntries]
$newdata=FileOpen("newdata.csv", 2) ; Where "newdata.csv"is the file name for your NEW DATA file
For $t = 1 to $NumOfEntries - 1
    If $a[$t]<>"" Then
        FileWriteLine($newdata,StringMid($a[$t],1,StringInStr($a[$t],"|")-1))
        
    endif
Next
FileClose($newdata)

Again, I've tested this and it does seem to work fine.


"Intelligence is the ability to adapt to change."                                      - Stephen Hawking                                        "...not the ability to exploit others."                                                  - OldCoder

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0