Solved

Double Quotes in Flat File

Posted on 2004-08-03
8
1,012 Views
Last Modified: 2012-08-14
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
Comment
Question by:BOOBIC
  • 5
  • 3
8 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
you would use

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

for each field ... this will remove them.
0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 100 total points
Comment Utility
or better ...

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

for i = 0 to str.Length - 1
    str(i) = str(i).Replace("""", "")
next
0
 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
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
 

Author Comment

by:BOOBIC
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
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
 

Author Comment

by:BOOBIC
Comment Utility
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
 
LVL 37

Expert Comment

by:gregoryyoung
Comment Utility
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
 

Author Comment

by:BOOBIC
Comment Utility
Thank you very much.

RGDS
Sam
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now