• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 932
  • Last Modified:

csv comma delimited text file html vbscript replace function help

Below are a few sample lines from my csv file.
5000201,12/02/1950,Z,MED,"8,45.00","5,455.84",925,MO ADJ,"-177.25",12/18/2009
1230954,11/02/1956,E,MED,"938.25","-6,378.00",775,MD ADJ,"-1,302.99",10/12/2008

Some of my numeric fields are dollar amounts, they are all enclosed in double quotes.  

I know how to loop through each line in the CSV file but need help with the below:

I know Replace(m_line_on,"""","") would remove the double quotes but how to remove the commas ONLY from items in double quotes on each line as I loop through them?  I need all the other commas in to seperate the fields but the others between double quotes gone.

Regards,
Torrey

0
Torrey Bates
Asked:
Torrey Bates
  • 3
  • 2
  • 2
1 Solution
 
utter77Commented:
Hi.

This is a little tricky since you don't know if "," delimits items or not. You have to compare items in the string with the next item.
I have no environment to test this in right now but try this.

string line = @"5000201,12/02/1950,Z,MED,"8,45.00","5,455.84",925,MO ADJ,"-177.25",12/18/2009";
string newLine = "";

string[] lineItems = Split(line, ",");
for (int i = 0; i<lineItems.Length; i++)
{
  if(lineItems[i].startswith("\"") and not lineItems[i].endswith("\"") and lineItems[i+1].endswith("\"") and not lineItems[i+1].startswith("\") //these two are one.
  {
    newLine += lineItems[i] + " " lineItems[i+1];
    i++; //increment counter since two are one
  }
  else
  {
    newLine += lineItems[i];
  }
  if(i < lineItems.Length) newLine += ","; //adds a "," if it's not the last one
}

Hope this works.

/MAtt
0
 
utter77Commented:
Hi again.

I tried mine in evironment and a lot of syntax errors.. sorry. And i see you probably use VB.

I'll write a new one in environment. bbs.
0
 
Torrey BatesPreidentAuthor Commented:
Yes, the provided code from an experts must be vbscript since I'm using it on an asp web page.

Would it be easier if I just dump each value to an array where:

m_split_array(0) = 5000201
m_split_array(1) = 12/02/1950
m_split_array(2) = Z
m_split_array(3) = MED
m_split_array(4) = "8,45.00"
m_split_array(5) = "5,455.84"
m_split_array(6) = 925
m_split_array(7) = MO ADJ
m_split_array(8) = "-177.25"
m_split_array(9) = 12/18/2009

I was going to do that after I had the comma removed but I don't have a problem with removing the comma after the value is in the array.  Of course I don't know what adjustment to make for the Split(m_line_on, ",")  with those extra commas but maybe it could skip commas in double quotes somehow?

Regards,
Torrey
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RobSampsonCommented:
Hi, the only way that I can think of doing this is to iterate through each line, finding every quote character, and if a comma exists after every odd numbered quote, remove it.  Something like this might work for each line.

strLine = "5000201,12/02/1950,Z,MED,""8,45.00"",""5,455.84"",925,MO ADJ,""-177.25"",12/18/2009"
intQuotes = 0
strNewLine = ""
For intChr = 1 To Len(strLine)
      strChr = Mid(strLine, intChr, 1)
      If strChr = """" Then
            intQuotes = intQuotes + 1
            strNewLine = strNewLine & strChr
      ElseIf strChr = "," Then
            ' Check if the amount of quotes is even, if so, keep the comma
            If intQuotes Mod 2 = 0 Then strNewLine = strNewLine & strChr
      Else
            strNewLine = strNewLine & strChr
      End If
Next
MsgBox strLine & VbCrLf & "has become" & VbCrLf & strNewLine




Regards,

Rob.
0
 
utter77Commented:
Hi again.

Hade som problems with if last one was "as,df" or not but i solved it with a dummy.

This works now:

    Sub Main()
        'Dim line As String = "5000201,12/02/1950,Z,MED,""8,45.00"",""5,455.84"",925,MO ADJ,""-177.25"",12/18/2009"
        Dim line As String = """as,df"",""as,df"",1234,""as,df"",1234,""as,df"""

        line += ",DUMMY"

        Dim newLine As String = ""

        Console.WriteLine(line)

        Dim lineItems() As String = Split(line, ",")
        For i As Integer = 0 To lineItems.Length - 2
            If lineItems(i).StartsWith("""") And Not lineItems(i).EndsWith("""") And lineItems(i + 1).EndsWith("""") And Not lineItems(i + 1).StartsWith("""") Then
                newLine += lineItems(i) + " " + lineItems(i + 1)
                i += 1
            Else
                newLine += lineItems(i)
            End If

            If i < lineItems.Length - 2 Then
                newLine += ","
            End If
        Next
        newLine.Replace(",DUMMY", "")


        Console.WriteLine(newLine)
        Console.ReadLine()

    End Sub
0
 
Torrey BatesPreidentAuthor Commented:
First posted working solution, short and complete, full sample provided, thanks!

Regards,
Torrey
0
 
RobSampsonCommented:
Thanks for the grade.

Regards,

Rob.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now