?
Solved

csv comma delimited text file html vbscript replace function help

Posted on 2010-01-11
7
Medium Priority
?
916 Views
Last Modified: 2012-06-27
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
Comment
Question by:Torrey Bates
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 4

Expert Comment

by:utter77
ID: 26290941
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
 
LVL 4

Expert Comment

by:utter77
ID: 26290963
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
 
LVL 2

Author Comment

by:Torrey Bates
ID: 26291116
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 65

Accepted Solution

by:
RobSampson earned 1000 total points
ID: 26291209
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
 
LVL 4

Expert Comment

by:utter77
ID: 26291555
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
 
LVL 2

Author Closing Comment

by:Torrey Bates
ID: 31675956
First posted working solution, short and complete, full sample provided, thanks!

Regards,
Torrey
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 26297476
Thanks for the grade.

Regards,

Rob.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this tutorial viewers will learn how to position items using CSS's three positioning types Create a new HTML document with an internal stylesheet.: Create another div in CSS and name it Absolute : Type "position:absolute;" and "top:10px; left:50p…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question