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.InitialDir ectory = My.Computer.FileSystem.Spe cialDirect ories.MyDo cuments
OpenFileDialog1.FileName = ""
OpenFileDialog1.Multiselec t = False
OpenFileDialog1.Filter = "Comma-seperated Values|*.csv"
OpenFileDialog1.FilterInde x = 2
OpenFileDialog1.RestoreDir ectory = True
If (OpenFileDialog1.ShowDialo g() = 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(fNam e) = True Then
Dim objReader As New System.IO.StreamReader(fNa me)
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
Can you help below is the basic import i use:
dgBR.Rows.Clear()
Dim fName As String = ""
OpenFileDialog1.InitialDir
OpenFileDialog1.FileName = ""
OpenFileDialog1.Multiselec
OpenFileDialog1.Filter = "Comma-seperated Values|*.csv"
OpenFileDialog1.FilterInde
OpenFileDialog1.RestoreDir
If (OpenFileDialog1.ShowDialo
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(fNam
Dim objReader As New System.IO.StreamReader(fNa
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
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
ASKER
Let me check
ASKER
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.
ASKER
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)
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 TextFieldParser Class.
ASKER
ste5an, thanks for that. I will take a look an get back to you
thanks
thanks
ASKER
Eric I will take a look at the links and get back to you
thank
thank
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant thanks. let me check this out.
ASKER
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.