How do import data from a csv file? 2

[deb18]

I'm looking to parse a csv file that I read in with webrequest. SOme of the fields have commas in them so it is not sufficient to split the data with a comma delimiter.
The fields with commas in them are surrounded by quotes. The others are not. How can I read these values in?

 

[Custom24]

Hi
This is my preferred method for dealing with csv files - I've checked and it correctly handles commas which are not delimiters, if they are inside double quotes.

http://support.microsoft.com/default.aspx?scid=KB;en-us;q262537

Mark

 

[PankajBanga]

Visit my sample .NET TextData Provider on the link given below

[li]This sample allows you to select the delimiter in the text file.[/li]

[li]Identifies the text qualifier if any.[/li]

[li]Provides an option of including first line as field names in DataGrid.[/li]

[li]It makes use of StreamReader constructor for reading and transfering data from a text file into .NET data structure. [/li]

http://www.gotdotnet.com/Community/...mpleGuid=5E5C517B-5039-4246-95A9-667B446928BF

I have few more samples there; they might be helpful for you. Just click on my name “PankajBanga” right next to Uploaded by. (you will need a .NET passport to sign in)

===Please visit FAQs to view my contributions.===

 

[deb18]

pankajbanga,
The code seems to do exactly what I needed it to but my problem continues to be that it does not take into account commas within the field contents. Any ideas?
Thanks!

 

[abds]

Try tossing the file into Excel. It reads csv as a native format.
Phil

 

[PankajBanga]

You need to have a standard format for your CSV files. You can separate field values with a space "12 Smith St" rather than commas "12, Smith St" and then split using comma delimiter.

While working with Access, it’s always best to use double quotes (&quot rather than single quotes (') around your fields as you might get single quotes (') in your fields. For example O'Neil, O'Callaghan etc.

===Please visit FAQs to view my contributions.===

 

[abds]

Deb18,
Where does the csv come from? Is it USN standard or a homebrew?
There are utilities around to handle standard csv.
Phil

 

[deb18]

It is standard.

 

[abds]

try this

http://www.freevbcode.com/ShowCode.Asp?ID=3110

hth
Phil

 

[RealQuiet]

Deb, I came across this problem in a project a few weeks ago. I didn't really find much that fit my needs, so I wrote a function to do this. It takes a string, text qualifier, and delimiter as arguments and returns a string array. There's a little bit of logic in it, but it basically reads through the line character by character and has a boolean variable that keeps track of whether you are inside the text qualifier or not and ignores the delimiter if you are inside. Also, it allows for things like "" to signify an actual double quote. For example
"field1","field2", "8"" board"
would return an array with:
field1
field2
8" board
The code may not be perfect, but if you haven't found a better solution yet, I can post what I have for you.

 

[deb18]

Thank you all for your responses.

I was able to find a split function at

http://www.1vbstreet.com/vb/scripts/ShowCodeAsText.asp?txtCodeId=11993&lngWId=1

that takes into account the commas within the fields but it does not take into account quotes within the quotes.

Realquiet, It sounds like your function does do that. Would you be able to post it please?

Thanks,
Deb

 

[RealQuiet]

Here's the function.
A sample call would be:
strLine = objSplit.SplitLine(strTemp, ",", """&quot
where strTemp is the current line string, comma is the delimiter, a double quote is the text qualifier, and strLine is a string array.
Hope this helps

Public Function SplitLine(ByVal Line As String, ByVal Delimiter As String, ByVal TextQualifier As String) As String()
Dim arrNewLine As New ArrayList()
Dim chCurrent As Char
Dim intPos As Integer
Dim strTemp As String
Dim strNewLine As String()
Dim bolInsideQualifier As Boolean

For intPos = 0 To (Line.Length - 1)
chCurrent = Line.Substring(intPos, 1)
Select Case True
Case chCurrent = Delimiter.Substring(0, 1) And Not bolInsideQualifier
'Check for single or multi-character delimiters
If Line.Substring(intPos, Len(Delimiter)) = Delimiter Then
intPos += Len(Delimiter) - 1 'Increment position For multi-character delimiters
arrNewLine.Add(strTemp)
strTemp = ""
Else
strTemp &= chCurrent
End If

Case bolInsideQualifier And chCurrent = TextQualifier
If intPos < (Line.Length - 1) Then
'Check to see if it's a double qualifier eg. &quot;&quot;
If Line.Substring(intPos + 1, 1) = TextQualifier Then
strTemp &= chCurrent
intPos += 1
Else
'Toggle the Qualifier
bolInsideQualifier = False
End If
Else
'Toggle the Qualifier
bolInsideQualifier = False
End If
Case Not bolInsideQualifier And chCurrent = TextQualifier
bolInsideQualifier = True
Case Else
strTemp &= chCurrent
End Select
Next
'Add last field to arraylist
arrNewLine.Add(strTemp)
ReDim strNewLine(arrNewLine.Count - 1)
'Convert To String Array
For intPos = 0 To arrNewLine.Count - 1
strNewLine(intPos) = arrNewLine.Item(intPos)
Next
Return strNewLine
End Function

 

[deb18]

Thank you very much realquiet. The function did exactly what I needed it to!