|
Hi.
Does anyone knows the way to parse (extract) data from Excel and CSV files?
Any help is appreciated.
Thank you,
Alex.
|
|
|
|
|
Here is a link to an article that has plenty of examples. csv
There is always the String.split method or RegEx expressions that you can use to code you own if that fits your needs. The last one I coded up used generics which made the parsing files less work.
|
|
|
|
|
Thanks a lot for reply. Do you have any examples from what you've done?
The reason I ask is I have a little time to learn .NET and I need something quick.
We changed from Java to .NET recently.
And one more thing. I need examples in VB. 
|
|
|
|
|
is your field structure static and will you have to deal with commas inside of the fields? As far as vb or c# I use the latter but use vb.net from time to time so the cross over is very easy. If you are comming out of java the c# syntax is very very similar.
|
|
|
|
|
I know about C# being similar to Java. The problem is that our infrostucture is based on VB. . Here's the deal with files. We will be getting data from our customers in Excel and CVS fromats. Of course different customers will have different data and it's not consistent between them, meaning customer 1 won't be the same as customer 2. I need to create parsers for all customers (around 10), which I understand will be cusomer specific. I'm a bit familiar with VB but never done parsing (extracting) data from Excel and CSV. I need something to start and I'm sure I can impovise.
Thank you a lots.
|
|
|
|
|
We may need to jump offline to not fill up the forum but let me offer some personal insight into what you may find.
I have dealt with this situation before and there is a solution that may save you effort in the future. To start with I used the Strategy pattern to devise the solution which uses a Windows Service that runs on a timer and loads up the parsing assemblies that are specific for the file being dropped by the customers. The assembly finds the file and parses it then returns back to the service.
Sounds like a bunch but the code in the service never changes and you can add, change or remove the assemblies as the customers dictate. This type of functionallity keeps you from having to get in and out of the code every time a customer changes making the code much more durable.
Java is not as friendly to use with Windows Services but .net makes them very easy. If you want to skip the service just use a factory method and spin up the assemblies that parses that type of file.
This is a fair amount of code so prepare.
This class will hold the fields and their values out of the line from the file.
Friend Class Field
Sub New(ByVal fieldNameIn As String, ByVal fieldValueIn As String, ByVal isFoundIn As Boolean)
misFound = isFoundIn
mFieldName = fieldNameIn
mFieldValue = fieldValueIn
End Sub
Private misFound As Boolean
Private mFieldName As String
Private mFieldValue As String
Public Property IsFound()
Get
Return misFound
End Get
Set(ByVal value)
misFound = value
End Set
End Property
Public Property FieldName()
Get
Return mFieldName
End Get
Set(ByVal value)
mFieldName = value
End Set
End Property
Public Property FieldValue()
Get
Return mFieldValue
End Get
Set(ByVal value)
mFieldValue = value
End Set
End Property
End Class
This object will expose a list of lines that holds the parsed fields
Imports System.Collections.Generic
friend Class Line
'''
''' Ctor takes in a simple delimiter
'''
''' <param name="lineIn" />
''' <param name="delimiter" />
''' <remarks>
Sub New(ByVal lineIn As String, ByVal delimiter As Char)
mLine = lineIn
parseFields()
End Sub
Private mLine As String
Private mFieldArray As String()
Private mDelimiter As Char
Private mFields As List(Of Field)
'''
''' public interface for the fields
'''
''' <value>
''' <returns>
''' <remarks>
Public ReadOnly Property Fields()
Get
Return mFields
End Get
End Property
Private Sub parseFields()
' This is where your validation code will go make sure the text is not malformed
mFieldArray = mLine.Split(mDelimiter)
If (mFieldArray.Length > 0) Then
For Each fld As String In mFieldArray
mFields.Add(New Field("give it a name", fld, True))
Next
End If
End Sub
End Class
This is the public interface that takes in the file name and loads the data
Imports System.Collections.Generic
Imports System.IO
Public Class parser
Sub New(ByVal filepath As String, ByVal isFirstLineFieldNames As Boolean)
gListOfLines = New List(Of Line)
readFileAndLoadLines(isFirstLineFieldNames)
End Sub
Private gListOfLines As List(Of Line)
Private mFilePath As String
Private mFieldNames As String
Private mFileStream As IO.StreamReader
'''
''' public exposure of the list of lines which exposes the fields
'''
''' <value>
''' <returns>
''' <remarks>
Public ReadOnly Property ListOfLines()
Get
Return gListOfLines
End Get
End Property
Private Sub readFileAndLoadLines(ByVal firstLineFieldNames As Boolean)
mFileStream = New StreamReader(mFilePath)
' Strip out the field names which you can use later if you want
If (firstLineFieldNames) Then
mFieldNames = mFileStream.ReadLine()
End If
While mFileStream.Peek() > 0
'read through each line of the file and load it up into the generic list
gListOfLines.Add(New Line(mFileStream.ReadLine(), ","))
End While
End Sub
End Class
This is a very simple example of parsing a csv without error handling or any type of validation so take it as a starting point. As I mentioned at the top I would build a group of dll's that are specific for each of you customers and use reflection to load the correct dll for the file.
Hope it helps.
|
|
|
|
|
Thank you very much my friend. I will look into this. Do you have any idea what I should use for xls files? I tried to use code that reads line by line but got a mess back (and I know why). If you want to get off line you can alway email me usign the link.
Thanks a lot and Happy New Year.
|
|
|
|
|
Perhaps your bestbet would to buy a Visual basic learners guide from the dummies series or other.
Alternatively, http://msdn.microsoft.com/vstudio/express/vb/learning/default.aspx offers two seperate video series that should get you started on the basics. However, both of these options will take some time.
The other option is trying to stumble your way through it, which would take much longer.
Posted by The ANZAC
|
|
|
|
|
Hi,
I am using VB 2005. My application has many data bound controls. The connection is stored in the app.config file.
I want the application to start with a default connection string and while during the runtime, the user can click on a button and change the connection string without exiting the application.
I would really appreciate any sort of help.
I have already something working but this requires the application to close and restart. ( If i dont close and restart the application dosent change the connection string) . Here is the code
locatedatabase.vb
Imports System.Xml
Public Class LocateDatabase
Private Const AuthenticationTypeWindows As Integer = 1
Private Const AuthenticationTypeSQL As Integer = 2
Dim ProgramTitle As String = "BDM Security"
Public Function PromptUser() As String
' ----- Prompt the user for database connection details. Return a
' valid ADO.NET connection string for SQL Server, or a blank
' string if the user clicks Cancel.
Dim newConnection As String
' ----- Prompt the user.
Try
Me.ShowDialog()
Catch ex As Exception
End Try
' ----- Build the new connection string.
If (Me.DialogResult = Windows.Forms.DialogResult.OK) Then
newConnection = "Data Source=" & Trim(server.Text) & _
";Initial Catalog=" & Trim(database_name.Text)
If authentication.SelectedIndex = 0 Then
' ----- Use Windows security.
newConnection &= ";Integrated Security=true"
Else
' ----- Use SQL Server security.
newConnection &= ";User ID=" & Trim(user_id.Text) & _
";Password=" & Trim(password.Text)
End If
Return newConnection
Else
Return ""
End If
End Function
Private Sub LocateDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' ----- Prepare the form.
Dim counter As Integer
Dim connectionString As String
Dim oneKey As String
Dim oneValue As String
On Error Resume Next
' ----- Load in the existing data.
connectionString = My.Settings.con & ""
For counter = 1 To CountSubStr(connectionString, ";") + 1
' ----- Each comma-delimited part has the format "key=value".
oneKey = GetSubStr(connectionString, ";", counter)
oneValue = Trim(GetSubStr(oneKey, "=", 2))
oneKey = Replace(UCase(Trim(GetSubStr(oneKey, "=", 1))), " ", "")
' ----- Process each part.
Select Case oneKey
Case "DATASOURCE"
' ----- Show the server host.
server.Text = oneValue
Case "INITIALCATALOG"
' ----- Show the default database name.
database_name.Text = oneValue
Case "INTEGRATEDSECURITY"
' ----- Only check for "true". False is assumed.
If (UCase(oneValue) = "TRUE") Then _
authentication.SelectedIndex = 0
Case "USERID"
' ----- A user ID forces SQL authentication.
authentication.SelectedIndex = 1
user_id.Text = oneValue
Case "PASSWORD"
' ----- A password forces SQL authentication.
authentication.SelectedIndex = 1
password.Text = oneValue
End Select
Next counter
End Sub
Private Sub RecordDatabase_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles database_name.Enter
' ----- Highlight the entire text.
database_name.SelectAll()
End Sub
Private Sub RecordPassword_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles password.Enter
' ----- Highlight the entire text.
password.SelectAll()
End Sub
Private Sub RecordServer_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles server.Enter
' ----- Highlight the entire text.
server.SelectAll()
End Sub
Private Sub RecordUser_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles user_id.Enter
' ----- Highlight the entire text.
user_id.SelectAll()
End Sub
Private Sub RecordAuthentication_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles authentication.SelectedIndexChanged
' ----- Enable dependent fields as needed.
If authentication.SelectedIndex = 0 Then
' ----- Windows authentication requires no user/password.
Label4.Enabled = False
user_id.Enabled = False
Label5.Enabled = False
password.Enabled = False
Else
' ----- Requires specific SQL Server user and password.
Label4.Enabled = True
user_id.Enabled = True
Label5.Enabled = True
password.Enabled = True
End If
End Sub
Private Sub ActOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ActOK.Click
'Clear the previous connection string
Dim con_Z As String
con_Z = My.Settings.con
My.Settings.con = ""
If (ValidateFormData() = False) Then Return
Me.DialogResult = Windows.Forms.DialogResult.OK
If Form1.ConnectDatabase() = False Then
My.Settings.con = con_Z
Me.Close()
Exit Sub
End If
Form1.Close()
Me.Close()
End Sub
Public Function CountSubStr(ByVal mainText As String, ByVal subText As String) As Integer
' ----- Return a count of the number of times that a subText occurs in
' a string (mainText).
Dim totalTimes As Integer
Dim startPos As Integer
Dim foundPos As Integer
totalTimes = 0
startPos = 1
' ----- Keep searching until we don't find it no more!
Do
' ----- Search for the subText.
foundPos = InStr(startPos, mainText, subText)
If (foundPos = 0) Then Exit Do
totalTimes = totalTimes + 1
' ----- Move to just after the occurrence.
startPos = foundPos + Len(subText)
Loop
' ----- Return the count.
Return totalTimes
End Function
Public Function GetSubStr(ByVal origString As String, ByVal delim As String, _
ByVal whichField As Integer) As String
' ----- Extracts a delimited string from another larger string.
Dim stringParts() As String
' ----- Handle some errors.
If (whichField < 0) Then Return ""
If (Len(origString) < 1) Then Return ""
If (Len(delim) = 0) Then Return ""
' ----- Break the string up into delimited parts.
stringParts = Split(origString, delim)
' ----- See if the part we want exists and return it.
If (whichField > UBound(stringParts) + 1) Then Return "" _
Else Return stringParts(whichField - 1)
End Function
Private Function ValidateFormData() As Boolean
' ----- Check the supplied data, and return True if it is all valid.
On Error Resume Next
' ----- Server name is required.
If (Trim(server.Text) = "") Then
MsgBox("The Server or Host name is required.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation, ProgramTitle)
server.Focus()
Return False
End If
' ----- Database name is required.
If (Trim(database_name.Text) = "") Then
MsgBox("The Database Name is required.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation, ProgramTitle)
database_name.Focus()
Return False
End If
' ----- For SQL Server authentication, the User ID is required.
If authentication.SelectedIndex = 1 Then
If (Trim(user_id.Text) = "") Then
MsgBox("The User Name is required for SQL Server authentication.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation, ProgramTitle)
user_id.Focus()
Return False
End If
End If
' ----- Success.
Return True
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub unit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles unit.Click
server.Text = "SQLDEV2\SQL_DEV2"
database_name.Text = "fgb01q_unit"
authentication.SelectedIndex = 0
user_id.Text = ""
password.Text = ""
End Sub
Private Sub demo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles demo.Click
server.Text = "BUF53460"
database_name.Text = "fgb01q_demo"
authentication.SelectedIndex = 1
user_id.Text = "sa"
password.Text = "blues"
End Sub
Private Sub demo1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles demo1.Click
server.Text = "BUF53460"
database_name.Text = "fgb01q_demo1"
authentication.SelectedIndex = 1
user_id.Text = "sa"
password.Text = "blues"
End Sub
End Class
Main Form (Form1)
Private Sub ChangeDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChangeDatabase.Click
LocateDatabase.Show()
End Sub
Public Function ConnectDatabase() As Boolean
' ----- Connect to the database. Return True on success.
Dim connectionString As String
Dim configChanged As Boolean
Dim ProgramTitle As String = "BDM Security "
' ----- Initialize.
HoldTransaction = Nothing
configChanged = False
' ----- Obtain the connection string.
If (Trim(My.Settings.con & "") = "") Then
' ----- Inform the user about the need to configure the database.
If (MsgBox("The application will exit for the new connection. Please restart the application. Would you like to proceed?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question, ProgramTitle) _
<> MsgBoxResult.Yes) Then Return False
' ----- Prompt for the new connection details.
connectionString = LocateDatabase.PromptUser()
If (connectionString = "") Then Return False
configChanged = True
Else
connectionString = My.Settings.con
End If
TryConnectingAgain:
' ----- Attempt to open the database.
Try
DB = New SqlClient.SqlConnection(connectionString)
DB.Open()
Catch ex As Exception
' ----- Some database failure.
MsgBox("Database Connection Error ")
' ----- Perhaps it is just a configuration issue.
If (MsgBox("The connection to the database may have failed due to " & _
"invalid configuration settings. Would you like to change the " & _
"database configuration at this time?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question, ProgramTitle) _
<> MsgBoxResult.Yes) Then Return False
' ----- Prompt for new details.
connectionString = LocateDatabase.PromptUser()
If (connectionString = "") Then Return False
configChanged = True
GoTo TryConnectingAgain
End Try
' ----- Save the udpated configuration if needed.
If (configChanged = True) Then _
My.Settings.con = connectionString
' ----- Success.
Return True
End Function
End Class
Sankalp Pande
sankalppande@gmail.com
|
|
|
|
|
Anyone know of a way to remove the DBNull default option from an Infragistics combo box?
Thanks.
|
|
|
|
|
How i add .dll reference to SmartDevice project?
Example code can help thenks.
|
|
|
|
|
Hi All,
I think I confused the issue when I last posted this so I am trying again.
I need to refresh a combobox list (in VB .net) when a new item as been added to the SQL database. I cannot find any example of how this is achieved and it is very different from VB6 which I am used to. I am still trying to get to grips with .net!
I have a sub which calls a form so the user can add details of an item, and when it returns from the form (which inserts the item in the database) I need to update the list in the combo box, which is bound to a datasource on the form.
I would be most grateful for any assistance
David Loring
David Loring
!! Keep Music Live !!
|
|
|
|
|
It's done the exact same way to got the data into the ComboBox into the first place. Reload the data from the SQL source and rebind to the combobox.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Hi Dave
I thought I had done this...code below...but it does not seem to refresh
Private Sub cmdAddDistrict_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdAddDistrict.Click
'Adds a new district to the list
Dim frmRD As New RegDistrict_Add
Using frmRD
frmRD.ShowDialog()
CountyForRegDistrictBindingSource.ResetBindings(False)
cboRegDistricts.Refresh()
End Using
End Sub
What am I doing wrong??
David Loring
!! Keep Music Live !!
|
|
|
|
|
You called ResetBindings. You did NOT re-fetch the data from the database.
Calling Refresh on the combobox just redraws the image of the combobox. It doesn't refresh the data in it.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
OK, Thanks Dave. I had thought the re-set bindings did that. I will have another go at it.
David Loring
!! Keep Music Live !!
|
|
|
|
|
Hello!
I have a question about what can I do to see count of records for each page in Crystal Report?
I fill my page with Data Table.
Thank you befor you answer
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Nazila
|
|
|
|
|
i have a web service which transfer data from server to client and back and forth.
The performance is reasonable but i wan to increase the performance by sending data more efficently and speedily.
How can i achiecve that?
|
|
|
|
|
With Web Services, you don't have much of a choice. Since everything needs to be encoded into an XML format, essentially a text file, your only options are to not send as much data or send it acrossed a faster network connection.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
But i have read that creating web service with SOAP extension can improve performance.
|
|
|
|
|
How much data are you sending back to the caller?
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
Data size can vary from few kb's to mb's
|
|
|
|
|
Well, you could try to compress the data comming back. I don't know how you're going to do it over the entire SOAP message though. My first guess would be to compress the data before it gets into the message.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
you can also compress the stream which will give you a performance boost if it's a large amount of data...
|
|
|
|
|
Can you give me an example.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
