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

Double Quotes in Flat File

Hi everyone:

I do have CSV file which looks like that.

MEM#,NAME,SSN,DOB,GENDER,GROUP,LOB,REGION,PCP,PLAN,MEM EFF DT,ORIG BEG COV DT,MEM TERM DT,MEM LAST UPD,CAP RUN DT,CAP TYPE,AGE,GRP EFF DT,GRP ANNIV DT,CSC/PMPM AMT,CAP MONTH,REGION NAME,PCP NAME,BENEFIT PLAN DESC,FAMILY CODE,PHYW AMT,HOSP AMT,HOSPR AMT,OOA AMT,RX AMT,POS AMT,POS DISC AMT,IPA AMT,INCENTIVE AMT,PHYS GROSS,PHY WITHHOLD,PHY STOP LOSS,PHYS NET,HOSP GROSS,HOSP WITHHOLD,HOSP STOP LOSS,HOSP NET,RX RESERVE,OUT OF AREA,PHYS STOP LOSS FACTOR,HOSP STOP LOSS FACTOR,PHY WITHHOLD FACTOR,HOSP WITHHOLD FACTOR,PCP DISCOUNT,PCP ADD RESERVE,HOSP ADD RESERVE,RX ADD RESERVE
"1000008*01","RAMIREZ, PEDRO","333-33-3364 ","12-29-1933","M","C12898A*CC5","L01","116","0528","CC53M","04-01-2003","12-01-2070","","04-18-2070","02-01-2004","CC","36","12-01-2000","12-01-2004","00.54","02-01-2004","MEDICAL GROUP","WOO, MD ROBERT","CALCHOICE 5M-RX2","6B","0.00","00.00","0.90","0.50","00.00","0.00","0.00","0.00","0.00","00.96","0.85","0.00","00
.11","00.62","0.00","0.00","00.62","0.89","0.52","0.00","0.00","0.00","0.00","","","",""

Code below reads and dumps data into SQL. It works OK. I do have one problem, double quotes are inserted into SQL and I don't want to have it.  How to get reed of  double quotes?

Dim sField1 As String
        Dim sField2 As String
        Dim sField3 As String
        Dim sField4 As String
        Dim sField5 As String
        Dim sField6 As String
        Dim sField7 As String
        Dim sField8 As String
        Dim sField9 As String
        Dim sField10 As String
        Dim sField11 As String
        Dim sField12 As String
        Dim sField13 As String
        Dim sField14 As String
        Dim sField15 As String
        Dim sField16 As String
        Dim sField17 As String
        Dim sField18 As String
        Dim sField19 As String
        Dim sField20 As String
        Dim sField21 As String
        Dim sField22 As String
        Dim sField23 As String
        Dim sField24 As String
        Dim sField25 As String
        Dim sField26 As String
        Dim sField27 As String
        Dim sField28 As String
        Dim sField29 As String
        Dim sField30 As String
        Dim sField31 As String
        Dim sField32 As String
        Dim sField33 As String
        Dim sField34 As String
        Dim sField35 As String
        Dim sField36 As String
        Dim sField37 As String
        Dim sField38 As String
        Dim sField39 As String
        Dim oSQLConn As SqlConnection
        Dim myStreamReader As StreamReader
        Dim myReadFile As FileStream
        Dim oSQLCmd As SqlCommand = New SqlCommand
        Dim sSQL As String
        Dim conSQL As SqlConnection

        conSQL = ConnectionDB.GetConnection
        conSQL.Open()

        Dim Command As New SqlCommand("SelectUniversal", conSQL)

        Command.CommandType = CommandType.StoredProcedure

        Dim daNewPCP As New SqlDataAdapter(Command)
        Dim MyDataSet As New DataSet

        MyDataSet.Clear()
        MyDataSet.Tables.Clear()

        lstbDisplay.Items.Clear()
        DataGrid1.DataSource = Nothing
        DataGrid1.DataBindings.Clear()
        DataGrid1.Refresh()

        myReadFile = New FileStream(fileSource, FileMode.Open)
        myStreamReader = New StreamReader(myReadFile)

        Dim sEntry As String = myStreamReader.ReadLine()
        Dim readContents As String = myStreamReader.ReadLine()

        lstbDisplay.Items.Clear()

        Do While Not sEntry Is Nothing

            sEntry = Replace(Trim(Mid(sEntry, 1, 400)), "'", "")

            Dim str() As String = sEntry.Split(",")
            sField1 = str(0)
            sField2 = str(1)
            sField3 = str(2)
            sField4 = str(3)
            sField5 = str(4)
            sField6 = str(5)
            sField7 = str(6)
            sField8 = str(7)
            sField9 = str(8)
            sField10 = str(9)
            sField11 = str(10)
            sField12 = str(11)
            sField13 = str(12)
            sField14 = str(13)
            sField15 = str(14)
            sField16 = str(15)
            sField17 = str(16)
            sField18 = str(17)
            sField19 = str(18)
            sField20 = str(19)
            sField21 = str(20)
            sField22 = str(21)
            sField23 = str(22)
            sField24 = str(23)
            sField25 = str(24)
            sField26 = str(25)
            sField27 = str(26)
            sField28 = str(27)
            sField29 = str(28)
            sField30 = str(29)
            sField31 = str(30)
            sField32 = str(31)
            sField33 = str(32)
            sField34 = str(33)
            sField35 = str(34)
            sField36 = str(35)
            sField37 = str(36)
            sField38 = str(37)
            sField39 = str(38)
            sField40 = str(39)

            lstbDisplay.Items.Add(sEntry)

            sSQL = "INSERT INTO PL_UniversalFixed([1],[2],[3],[4], [5], [6], [7], [8], [9], [10], " _
                    & "[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], " _
                    & "[21], [22], [23], [24], [25], [26], [27], [28], [29], [30],  " _
                    & "[31], [32], [33], [34], [35], [36], [37], [38], [39]) VALUES ('" & sField1 & "', " _
                    & "'" & sField2 & sField3 & "', '" & sField4 & "', '" & sField5 & "', " _
                    & "'" & sField6 & "', '" & sField7 & "', '" & sField8 & "', '" & sField9 & "', " _
                    & "'" & sField10 & "','" & sField11 & "', '" & sField12 & "', '" & sField13 & "', " _
                    & "'" & sField14 & "','" & sField15 & "', '" & sField16 & "', '" & sField17 & "', " _
                    & "'" & sField18 & "','" & sField19 & "', '" & sField20 & "', '" & sField21 & "', " _
                    & "'" & sField22 & "','" & sField23 & "', '" & sField24 & "', '" & sField25 & "', " _
                    & "'" & sField26 & "','" & sField27 & "', '" & sField28 & "', '" & sField29 & "', " _
                    & "'" & sField30 & "','" & sField31 & "', '" & sField32 & "', '" & sField33 & "', " _
                    & "'" & sField34 & "','" & sField35 & "', '" & sField36 & "', '" & sField37 & "', " _
                    & "'" & sField38 & "','" & sField39 & "', '" & sField40 & "')"

            Cursor.Current = Cursors.WaitCursor
            oSQLCmd.CommandText = sSQL
            oSQLCmd.ExecuteNonQuery()
            sEntry = myStreamReader.ReadLine

        Loop

        conSQL.Close()

RGDS
Sam
0
BOOBIC
Asked:
BOOBIC
  • 5
  • 3
1 Solution
 
gregoryyoungCommented:
you would use

Field1.Replace("""", "")

for each field ... this will remove them.
0
 
gregoryyoungCommented:
or better ...

Dim str() As String = sEntry.Split(",")

for i = 0 to str.Length - 1
    str(i) = str(i).Replace("""", "")
next
0
 
gregoryyoungCommented:
just out of curiosity ... you know that according to the CSV standard you have to look at the " " when parsing (in case there is a , in the data)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
BOOBICAuthor Commented:
Hi gregoryyoung:

That was very quick. It works like a charm. I'm newbi to parsing text file. Your 100 points have been posted for you.

RGDS
Sam
0
 
gregoryyoungCommented:
some notes that may help you ... because it looks like you have the right idea but may get into trouble with some fringe cases.

The CSV File Format


Each record is one line   ...but
A record separator may consist of a line feed (ASCII/LF=0x0A), or a carriage return and line feed pair (ASCII/CRLF=0x0D 0x0A).
...but: fields may contain embedded line-breaks (see below) so a record may span more than one line.

Fields are separated with commas.
Example John,Doe,120 any st.,"Anytown, WW",08123

Leading and trailing space-characters adjacent to comma field separators are ignored.
So   John  ,   Doe  ,... resolves to "John" and "Doe", etc. Space characters can be spaces, or tabs.

Fields with embedded commas must be delimited with double-quote characters.
In the above example. "Anytown, WW" had to be delimited in double quotes because it had an embedded comma.

Fields that contain double quote characters must be surounded by double-quotes, and the embedded double-quotes must each be represented by a pair of consecutive double quotes.
So, John "Da Man" Doe would convert to "John ""Da Man""",Doe, 120 any st.,...

A field that contains embedded line-breaks must be surounded by double-quotes
So:

  Field 1: Conference room 1  
  Field 2:
    John,
    Please bring the M. Mathers file for review  
    -J.L.
  Field 3: 10/18/2002
  ...
would convert to:

  Conference room 1, "John,  
  Please bring the M. Mathers file for review  
  -J.L.
  ",10/18/2002,...
Note that this is a single CSV record, even though it takes up more than one line in the CSV file. This works because the line breaks are embedded inside the double quotes of the field.


Fields with leading or trailing spaces must be delimited with double-quote characters.
So to preserve the leading and trailing spaces around the last name above: John ,"   Doe   ",...
Usage note: Some applications will insist on helping you by removing leading and trailing spaces from all fields regardless of whether the CSV used quotes to preserve them. They may also insist on removing leading zeros from all fields regardless of whether you need them. One such application is Excel. :-(

Fields may always be delimited with double quotes.
The delimiters will always be discarded.
Implementation note: When importing CSV, do not reach down a layer and try to use the quotes to impart type information to fields. Also, when exporting CSV, you may want to be defensive of apps that improperly try to do this. Though, to be honest, I have not found any examples of applications that try to do this. If you have encountered any apps that attempt to use the quotes to glean type information from CSV files (like assuming quoted fields are strings even if they are numeric), please let me know about it.

The first record in a CSV file may be a header record containing column (field) names
There is no mechanism for automatically discerning if the first record is a header row, so in the general case, this will have to be provided by an outside process (such as prompting the user). The header row is encoded just like any other CSV record in accordance with the rules above. A header row for the multi-line example above, might be:
  Location, Notes, "Start Date", ...

0
 
BOOBICAuthor Commented:
Wouu...

That's what I wanted to know. I already encountered "FirstName, LastName" embedded comma, but in my case I have to disregard "FirstName, LastName" at all. In my script posted earlier I concatenated "FirstName, LastName" in order to follow my specs.
Gregoryyoung, thank you for your material. Very helpfull stuff.

Sincerely,

Sam
0
 
gregoryyoungCommented:
heres another method of doing it that may save you some time and headaches in the future. http://www.sadeveloper.net/viewarticle.aspx?articleID=99
0
 
BOOBICAuthor Commented:
Thank you very much.

RGDS
Sam
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

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