String Replace

[felanor]

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

[SadBunny]

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.

[llipschutz]

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.

[OldCoder]

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!

[OldCoder]

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.

[OldCoder]

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.