-1

I've done some googling about my issue but I can't seem to find anything that works. I have a .csv (it is formatted just like an excel sheet, with just strings) that I am trying to import into a dataset, but only one of the columns is importing successfully. Everything else is DBNull.

Here is my dataset:

dset.Tables.Add(New DataTable With {.TableName = "ImportedData"})
dset.Tables("ImportDataTable").Columns.Add("ID", GetType(String))
dset.Tables("ImportDataTable").Columns.Add("Name", GetType(String))
dset.Tables("ImportDataTable").Columns.Add("Type", GetType(String))
dset.Tables("ImportDataTable").Columns.Add("Misc1", GetType(String))
dset.Tables("ImportDataTable").Columns.Add("Misc2", GetType(String))
dset.Tables("ImportDataTable").Columns.Add("Misc3", GetType(String))
dset.Tables("ImportDataTable").Columns.Add("Misc4", GetType(String))

And here is my import code:

Dim ImportFileCSV As New OpenFileDialog
With ImportFileCSV 
    .Title = "Import Overview"
    .Filter = "CSV (*.csv)|*.csv"
End With

If ImportFileCSV .ShowDialog = DialogResult.OK Then
    Dim ImportPath As String = ImportDetailedOverview.FileName
    Dim ImportDirectoryPath As String = Path.GetDirectoryName(ImportPath) & "\"
    Dim ImportFileName As String = Path.GetFileName(ImportPath)

    Using MyConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & ImportDirectoryPath & ";Extended Properties=""Text;HDR=YES;""")
        Using MyCommand As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & ImportFileName, MyConnection)
            MyConnection.Open()
            MyCommand.Fill(dset.Tables("ImportedData"))
            MyConnection.Close()
        End Using
    End Using

Does anyone have any insight?





Edit:

Okay, so as per Ahmed, I am using the GenericParser: Below code to select the file via OpenFileDialog and then to add the returned table to dset.Tables("ImportDataTable")

  If ImportDetailedOverview.ShowDialog = DialogResult.OK Then
            Dim ImportPath As String = ImportDetailedOverview.FileName
            dset.Tables("ImportDataTable").Merge(ParseCSV(ImportPath))




I don't get any errors. It seems to finish successfully, but everything except Misc3 is blank. Through the power of binding sources, I am displaying that information to confirm:

            CType(fBindingSource.Current, DataRowView)("Name") = drow("Name").ToString
            MsgBox(drow("BuildingName").ToString)
            CType(fBindingSource.Current, DataRowView)("Type") = drow("Type").ToString
            CType(fBindingSource.Current, DataRowView)("Misc1") = drow("Misc1").ToString
            CType(fBindingSource.Current, DataRowView)("Misc2") = drow("Misc2").ToString
            CType(fBindingSource.Current, DataRowView)("Misc3") = drow("Misc3").ToString
            CType(fBindingSource.Current, DataRowView)("Misc4") = drow("Misc4").ToString

The CSV is very simple, it's an Excel CSV, so for example: col1 has and ID field (numeric only) col2 has a name field (string) so on and so forth.

lolikols
  • 87
  • 1
  • 5
  • 24
  • https://stackoverflow.com/q/1050112/62576 – Ken White Apr 06 '18 at 16:29
  • I've been using [`GenericParser`](https://www.nuget.org/packages/GenericParser/) for a while and it works great. – 41686d6564 stands w. Palestine Apr 06 '18 at 16:37
  • Hi Ken, I've actually taken a look at that. I went to the link in the answer and from what I can tell, the file is in C# and I'm in vb.net :( sorry. Also I'd like to do this stuff internally without having to get a separate class. The second top answer in that thread didn't help me since it was similar to what I'm already using. – lolikols Apr 06 '18 at 16:37
  • Hey Ahmed, thanks! I suppose I'll give it a go since both you and Ken seem to lean towards that. But...I'm currently coding in VB and that stuff looks to be in C#. How do I adjust for that? – lolikols Apr 06 '18 at 16:38
  • @lolikols unless you're entirely against the idea of using 3rd party libraries, you can simply add this library to your project *using the NuGet package from my comment above* and you can still use it in a VB.NET project. – 41686d6564 stands w. Palestine Apr 06 '18 at 16:39
  • Thanks Ahmed, I really appreciate it! I've wanted to stay away from 3rd party libraries because I've never used them before. I've successfully added the package and now I just have to figure out how to use it. Thank you very much! – lolikols Apr 06 '18 at 16:44
  • @AhmedAbdelhameed if you want to, you can put something as an answer so that I can mark it :) – lolikols Apr 06 '18 at 16:49

1 Answers1

1

Here's a simple example of how to use GenericParser in VB.NET:

First, you need to import the namespace:

Imports GenericParsing

To return a DataTable:

Private Shared Function ParseCSV(filePath As String) As DataTable
    Using parser As New GenericParserAdapter(filePath)
        parser.FirstRowHasHeader = True
        Return parser.GetDataTable()
    End Using
End Function

To return a DataSet:

Private Shared Function ParseCSV(filePath As String) As DataSet
    Using parser As New GenericParserAdapter(filePath)
        parser.FirstRowHasHeader = True
        Return parser.GetDataSet()
    End Using
End Function

References:

41686d6564 stands w. Palestine
  • 19,168
  • 12
  • 41
  • 79
  • Um...really sorry, so since I'm trying to get that stuff into dset.Tables("ImportedData"), I'm going to use that return a DataSet method. So I do ParseCSV(ImportPath) and then...how do I get it into dset.Tables("ImportedData")? – lolikols Apr 06 '18 at 17:00
  • You don't have to create the dataset manually. The `GetDataSet()` method creates it for you, it's that easy :) However, If you can't do that because you have other tables in your dataset, then you can use `GetDataTable()` instead, which will return a DataTable. You can then add this datatable to your dataset using `dset.Tables.Add()`. – 41686d6564 stands w. Palestine Apr 06 '18 at 17:04
  • Hey Ahmed, I'm using the GenericParser, but it still runs into the same issue outlined in my original post. Do you have any ideas? – lolikols Apr 06 '18 at 17:14
  • I can't help you if I'm blind. If you need additional help, please [edit](https://stackoverflow.com/posts/49697214/edit) your question, add your current code, some sample of your csv data, the **exact result** you're getting, and any errors you receive. – 41686d6564 stands w. Palestine Apr 06 '18 at 17:18