Link to home
Start Free TrialLog in
Avatar of ROB MCCONNELL
ROB MCCONNELL

asked on

CSV imports VB.net

I have a VB.net application that is dealing with clients bank accounts, I am trying to import a csv bank statement but obviously every statement is different.So to just look at a Comma Separated Value is great but there are also some banks IE that put commas in their descriptions.

Can you help below is the basic import i use:

 dgBR.Rows.Clear()
        Dim fName As String = ""
        OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        OpenFileDialog1.FileName = ""
        OpenFileDialog1.Multiselect = False
        OpenFileDialog1.Filter = "Comma-seperated Values|*.csv"
        OpenFileDialog1.FilterIndex = 2
        OpenFileDialog1.RestoreDirectory = True
        If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            fName = OpenFileDialog1.FileName
        End If
        Dim TextLine As String = ""
        Dim SplitLine() As String
        dgBR.ColumnCount = 9
        dgBR.Columns(0).Name = ""
        dgBR.Columns(1).Name = ""
        dgBR.Columns(2).Name = ""
        dgBR.Columns(3).Name = ""
        dgBR.Columns(4).Name = ""
        dgBR.Columns(5).Name = ""
        dgBR.Columns(6).Name = ""
        dgBR.Columns(7).Name = ""
        dgBR.Columns(8).Name = ""
        Dim Allocate As New DataGridViewButtonColumn
        dgBR.Columns.Add(Allocate)
        If System.IO.File.Exists(fName) = True Then
            Dim objReader As New System.IO.StreamReader(fName)
            Do While objReader.Peek() <> -1
                TextLine = objReader.ReadLine()
                If TextLine <> "" Then
                    SplitLine = Split(TextLine, ",")
                    Dim str As String = TextLine
                    Dim startName As Integer
                    Dim endName As Integer
                    Dim name As String = ""
                    startName = str.IndexOf("""")
                    endName = str.IndexOf("""", If(startName > 0, startName, 0))
                    If (endName > startName) Then
                    name = str.Substring(startName, endName)
                End If
                    Me.dgBR.Rows.Add(SplitLine)
                End If
            Loop
        Else
            MsgBox("File Does Not Exist")
        End If
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

usually, when a value has a comma, the field is surrounded with double-quotes. Is that your case? Otherwise, you will have a lot of issues finding a workaround to that situation
Avatar of ROB MCCONNELL
ROB MCCONNELL

ASKER

Let me check
Attached are the same entries only opened with Excel and Notepad.  It looks like they are all surrounded by Quote mark, so yes you are correct what is next?
When manually parsing CSV of any kind, you need a binary/character reading process, cause the line terminator is not necessarily CRLF and on the other side, CSV with column (text) delimiters allow CRLF as content.
Sorry didn't click upload
NotepadCapture.JPG
CSVCapture.JPG
From what I see, your fields are surrounded with double-quotes.

Your code is looking for commas without checking for any other delimiters.

You should give a try to the TextFieldParser (https://www.emoreau.com/Entries/Articles/2010/05/Do-you-know-the-TextFieldParser.aspx) or the FileHelpers (https://www.emoreau.com/Entries/Articles/2011/11/Using-the-FileHelpers-Library.aspx)
As you're using VB.NET, there is a built-in tool for that: The Text​Field​Parser Class.
ste5an,  thanks for that.  I will take a look an get back to you

thanks
Eric I will take a look at the links and get back to you

thank
I know that the data is also in Double Quotes but how to I look for double quotes in the splitline
The Split function alone won't search for double quotes. This is why you should consider a library like the TextFieldParser or the FileHelpers.
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Brilliant thanks. let me check this out.
Thank you for your input.  It made my life so much easier
Just a thought.  You might ask your bank if they provide banking transactions or statements in some other format.  For example, our bank offers transaction data to customers in BAI2, CSV, Quickbooks, Quicken, PDF, and a variety of other formats.  Might be a way of avoiding these headaches all together.