[RESOLVED] Newbie - CSV to Dataset

[megatrontheman]

Hi I am learning .net and i am trying to read csv to dataset and display the results in grid which it working fine. But i want to use the values. I know its a dumb question but gotta start somewhere :|


Sample CSV
=============
"Name,Mobile"
"Jaaaaa Naaaa Al-Muuuuy,7000108"
"Jbbbbb Nbbbb Al-Mbbb,7444108"
======================
and keeps going on for 700 more lines.

*Note: if i Ctrl + H the csv and remove the "" they show up in two columns. I think there is Regex to remove the "".

I can see these in dataset just fine but in just one column. So i know i have to use split with "," but i cant seems to find the right syntax... here's the code below :-

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=c:\"
 
        Dim objConn As New OleDbConnection(sConnectionString)
 
        objConn.Open()
 
        Dim objCmdSelect As New OleDbCommand("SELECT * FROM tel.csv", objConn)
 
        Dim objAdapter1 As New OleDbDataAdapter
 
        objAdapter1.SelectCommand = objCmdSelect
 
        Dim objDataset1 As New DataSet
 
        objAdapter1.Fill(objDataset1, "PhoneBook")
 
        DataGridView1.DataSource = objDataset1.Tables(0).DefaultView
 
        objConn.Close()
 
    End Sub

Now end of the day i basically need two varible of same person i.e Name & Tel like (name) and (tel) which i can call in a software. .i.e

client.QueryString.Add("name", name)
client.QueryString.Add("to", tel)

I am trying something like this to split which is not working :-

For i = 0 To objDataset1.Tables(0).Rows.Count - 1
            Dim cResult As String = objDataset1.Tables(0).Rows(i).Item(0)
            Dim cleanResult = Split(cResult, ",")
            MsgBox(cleanResult)
        Next i

Can anyone point me to rite direction.

[jmcilhinney]

If you want to use ADO.NET then you'll need to pre-process the data to remove those quotes because it's not a proper CSV file with those. You could do this:

Dim filePath As String = '...
Dim lines As String() = IO.File.ReadAllLines(filePath)
 
For index As Integer = 0 To lines.GetUpperBound(0)
    lines(index) = lines(index).Trim("""")
Next
 
IO.File.WriteAllLines(filePath, lines)

That's going to save the data back to the same file without the leading and trailing quotes. You can then use ADO.NET to read the data straight into a DataTable.

[Mickroy]

It's not proper CSV data... throw quotes around the values or take them off.

If you must work with the format you spoke about, you may be able to get the custom functionality from the Microsoft Text Driver. It allows you to specify a schema.ini file that describes the format of the text file to the driver.

If you want to split a single column, the split function returns an array.

Dim MyValues() As String = cResult.Split(","c)

Debug.Print("Name = " & MyValues(0))
Debug.Print("Tel = " & MyValues(1))

[megatrontheman]

@jmcilhinney

Thanks for providing the code to trim off the quotes and as suggested i am processing the trimming before doing the ado.net.

I do agree that its not a proper csv... its coming that way when i am exporting it from sql server. I can directly pull it from the sql server which i am doing to do afterward just wanted to keep both options open just in case to import from csv and sql directly.

@Mickroy

Thank you for providing the split function i have tried it and it works. However, after using the trim process its not required but i will keep it just in case i might not have write access to csv file.

Here's the code which is working fine except for a glitch :-

Imports System.Data
Imports System.IO
Imports System.Data.OleDb

Public Class Form1

    Dim phlocation As String
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Check if the file location is empty 
        If String.IsNullOrEmpty(phlocation) = True Then
            MsgBox("CSV location is not provided. Please provide its location!")
            'If its not empty then go ahead with next phase
        Else
            'Divide the dir and filename for ado.net connection and cmd strings.
            Dim fileDir = Path.GetDirectoryName(phlocation)
            Dim fileNam = Path.GetFileName(phlocation)

            'Remove the quotes "" from the csv if any before ado.net process. Thanks jmcilhinney
            Dim lines As String() = IO.File.ReadAllLines(phlocation)
            For index As Integer = 0 To lines.GetUpperBound(0)
                lines(index) = lines(index).Trim("""")
            Next
            IO.File.WriteAllLines(phlocation, lines)

            'Process ado.net for csv

            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fileDir & ""
            Dim objConn As New OleDbConnection(sConnectionString)
            objConn.Open()

            Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & fileNam & "", objConn)

            Dim objAdapter1 As New OleDbDataAdapter
            objAdapter1.SelectCommand = objCmdSelect

            Dim objDataset1 As New DataSet
            objAdapter1.Fill(objDataset1, "PhoneBook")


            DataGridView1.DataSource = objDataset1.Tables(0).DefaultView

            For i = 0 To objDataset1.Tables(0).Rows.Count - 1
                Dim nameSMS As String = objDataset1.Tables(0).Rows(i).Item(0)
                Dim telSMS As String = objDataset1.Tables(0).Rows(i).Item(1)
                'Geting this error if tel is empty "Conversion from type 'DBNull' to type 'String' is not valid."
                MsgBox(nameSMS & " " & telSMS)

                'Use the below code if csv is not striped of quotes """
                'Dim MyValues() As String = cResult.Split(","c)
                ' Debug.Print("Name = " & MyValues(0))
                ' Debug.Print("Tel = " & MyValues(1))
            Next i

            objConn.Close()
        End If

    End Sub

    Private Sub ImportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportToolStripMenuItem.Click
        OpenFileDialog1.Filter = "CSV Files (*.csv)|*.csv"
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            phlocation = (OpenFileDialog1.FileName)
        End If
    End Sub
End Class

I am getting this error "Conversion from type 'DBNull' to type 'String' is not valid" when a telephone is empty... is there any functional i can use to mark them and put it in a text file? like if "telSMS" is empty write on new text file and keep processing the loop?

            For i = 0 To objDataset1.Tables(0).Rows.Count - 1
                Dim nameSMS As String = objDataset1.Tables(0).Rows(i).Item(0)
                Dim telSMS As String = objDataset1.Tables(0).Rows(i).Item(1)
                'Geting this error if tel is empty "Conversion from type 'DBNull' to type 'String' is not valid."
                MsgBox(nameSMS & " " & telSMS)
            Next i

Thats the code block i am refering to.

[.paul.]

try this:

Dim nameSMS As String = objDataset1.Tables(0).Rows(i).Item(0).tostring
Dim telSMS As String = objDataset1.Tables(0).Rows(i).Item(1).tostring

[megatrontheman]

@paul

Thanks that works for keeping the loop going.

I still need to get the users without telephone numbers on to error text file.

I came up with the below code which is working but not looping through...

For i = 0 To objDataset1.Tables(0).Rows.Count - 1
                Dim nameSMS As String = objDataset1.Tables(0).Rows(i).Item(0)
                Dim telSMS As String = objDataset1.Tables(0).Rows(i).Item(1).ToString
                If IsDBNull(telSMS) OrElse telSMS = "" Then
                    Dim errorTel = "c:\errorTellusers.txt"
                    Dim sw As StreamWriter = File.CreateText(errorTel)
                    sw.WriteLine(nameSMS)
                    sw.Close()
                End If
            Next i

If you see IsDBNull then u can see its inside the loop or maybe i am missing something? it is only adding the last person in the txt file with no telephone number...

Any ideas?

p.s: I have added rep to all the posters here since all the people who contributed really helped me out. Thanks

[.paul.]

If  telSMS = "" Then 
    Dim errorTel = "c:\errorTellusers.txt" 
    Dim sw As StreamWriter = File.CreateText(errorTel) 
    sw.WriteLine(nameSMS) 
    sw.Close() 
else
'telSMS is not nothing
End If

[megatrontheman]

@paul
Actually that wasnt my problem but i used your method to make a list of successful entries.

I just figured out the problem. It wasnt the loop.. loop was working fine and the checking as well but "Dim sw As StreamWriter = File.CreateText" was creating the file again and again on every DBNull or "" it found. Thats why it was only showig the last person with no telephone number.

So i did small checking if the file is already there then append text to it and if its not there create it. This way if the file is not there it will create and next time it loops and finds the file it will append to it.

Below is the code.

For i = 0 To objDataset1.Tables(0).Rows.Count - 1
                Dim nameSMS As String = objDataset1.Tables(0).Rows(i).Item(0)
                Dim telSMS As String = objDataset1.Tables(0).Rows(i).Item(1).ToString
 
                If telSMS = "" Then
                    Dim errorTel = "c:\errorTellusers.txt"
                    If System.IO.File.Exists(errorTel) = True Then
                        Dim sw As StreamWriter = File.AppendText(errorTel)
                        sw.WriteLine(nameSMS)
                        sw.Close()
                    Else
                        Dim sw As StreamWriter = File.CreateText(errorTel)
                        sw.WriteLine(nameSMS)
                        sw.Close()
                    End If
                Else
                    Dim successTel = "c:\sucessTel.txt"
                    If System.IO.File.Exists(successTel) = True Then
                        Dim sw As StreamWriter = File.AppendText(successTel)
                        sw.WriteLine(nameSMS)
                        sw.Close()
                    Else
                        Dim sw As StreamWriter = File.CreateText(successTel)
                        sw.WriteLine(nameSMS)
                        sw.Close()
                    End If
                End If
            Next i

[.paul.]

lucky you know what you're doing. i missed that

[megatrontheman]

thank you all for helping me. I have finally done it other than one issue where telSMS has two numbers with / i.e 999999/7777777 i know it doesnt even make sense to input a number like that but the software we are using for ERP its for some reason allowing input like that. Will have to talk to them. I will split to discard the 2nd number.

I am marking this post resovled and will post the code once its final who knows it might come useful for someone like me out there

Once again thank you all.