Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB.Net Data Import

Posted on 2012-09-06
9
Medium Priority
?
489 Views
Last Modified: 2012-09-21
Hi,

i am trying to write some code which reads line by line of a text file (CSV) and trys to import them. Here is my 2 issues which i am trying to deal with.

Some fields have a ' in it like Names Walther's etc how do you gracefully handle ths ?
Also some fields have a comma in the field but then the field is enclosed by " how can i check for that ? If i just set my Spliter to , it will see the , in the field as another seperator.
0
Comment
Question by:AlexPonnath
  • 3
  • 3
  • 3
9 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38374600
Instead of splitting you can use ADO.Net using text driver and treat the csv/text file as a table and get the contents in to a datatable.
check this article for a sample, read the section "Using the Jet Engine"

http://www.codeproject.com/Articles/27802/Using-OleDb-to-Import-Text-Files-tab-CSV-custom
0
 

Author Comment

by:AlexPonnath
ID: 38374610
I dont want to use ADO.net since this function might be called for some data which will not talk to db. So thats not a solution
0
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.

 

Author Comment

by:AlexPonnath
ID: 38374692
First of i am capable of using google myself so i have seen both of these before... i am using the streamreader already

 Dim fr As StreamReader = Nothing
 Dim FileString As String = ""
 Dim LineItemsArr() As String
 Dim FilePath As String = Trim(Me.txtSelectedFile.Text)

 fr = New System.IO.StreamReader(FilePath)
             While fr.Peek <> -1
                FileString = fr.ReadLine.Trim
                If String.IsNullOrEmpty(FileString) Then Continue While 'Empty Line              
                LineItemsArr = FileString.Split(",")
     
              End While


So what i am looking for is code  / reference to code which will handle the  folowing Line

Mc Donald's, 123, "MC Donald's, LLC"
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38374709
I agree: I think the TextFieldParser is a much better idea for this task. I posted a comment showing usage here:

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Studio_.NET_2005/Q_26899233.html#a35176629

Be sure to set the HasFieldsEnclosedInQuotes property to True!
0
 
LVL 39

Expert Comment

by:appari
ID: 38374717
the link i posted is not using streamreader, rather using TextFieldParser class.
read my comment again.
0
 

Author Comment

by:AlexPonnath
ID: 38374747
guys i am trying to find a solution for my problem, and the solution should not mean to get rid of streamreader. Based on the links i can not see how i can use streamreader andthe TextFieldParser class together
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38374775
You said:

Some fields have a ' in it like Names Walther's etc how do you gracefully handle ths ?
Then what we have confirmed is about as graceful as you can get. Yes, you can achieve your goal by continuing on with the StreamReader and writing some logic to account for the commas and quotations, but what you are effectively doing is reinventing a parser. Why do that? From the code you posted, I cannot see what benefit you hope to gain by sticking with a StreamReader. If there is some specific reason why you need a StreamReader, then perhaps you would share that reason with us.

Based on the links i can not see how i can use streamreader andthe TextFieldParser class together
Well, there is an overload for the constructor which takes in a TextReader object (from which StreamReader derives).

e.g

Dim tfp As New TextFieldParser(fr)

Open in new window

0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 1500 total points
ID: 38374805
Here is your current logic using TFP:

Imports Microsoft.VisualBasic.FileIO

Module Module1
    Sub Main()
        Dim FileString As String = ""
        Dim LineItemsArr() As String
        Dim FilePath As String = Trim(Me.txtSelectedFile.Text)

        Using tfp As New TextFieldParser(FilePath)
            tfp.HasFieldsEnclosedInQuotes = True
            tfp.TextFieldType = FieldType.Delimited
            tfp.Delimiters = New String() {","}

            While Not tfp.EndOfData
                LineItemsArr = tfp.ReadFields()
            End While
        End Using
    End Sub
End Module

Open in new window


...and here is one way you could approach the problem using just the regular SR:

Imports System.IO

Module Module1
    Sub Main()
        Dim fr As StreamReader = Nothing
        Dim FileString As String = ""
        Dim LineItemsArr() As String
        Dim FilePath As String = Trim("input.txt")

        fr = New System.IO.StreamReader(FilePath)

        While fr.Peek <> -1
            FileString = fr.ReadLine.Trim
            If String.IsNullOrEmpty(FileString) Then Continue While 'Empty Line

            LineItemsArr = GetFields(FileString)

        End While

        For Each s In LineItemsArr
            Console.WriteLine(s)
        Next

    End Sub

    Function GetFields(ByVal line As String) As String()
        Dim result As New List(Of String)

        For i As Integer = 0 To line.Length - 1
            Dim start As Integer = i

            If line(i) = """" Then
                start += 1

                Do While i < line.Length
                    i += 1

                    If i < line.Length AndAlso line(i) = """" Then
                        Exit Do
                    End If
                Loop
            Else
                While i < line.Length AndAlso line(i) <> ","c
                    i += 1
                End While
            End If

            result.Add(line.Substring(start, i - start))
        Next

        Return result.ToArray()
    End Function
End Module

Open in new window


Note that the above (StreamReader version) is not fully tested, but should give you an idea of what you are asking. The benefit of the top code (TextFieldParser) is that given a line like this:

this,is,the,"sentence, that, I, should"also,split,on',the,comma

...the parser will catch that "also" is not properly inserted into that string. It is outside of the double-quotations, but inside of a comma. Is it supposed to be inside the double-quotes, or is it supposed to be its own field (i.e. preceded by a comma). The TFP will throw an exception on that line. The bottom code I posted does not catch this.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Six Sigma Control Plans
Starting up a Project

581 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