none
Not able to get all values from excel in a dataset RRS feed

  • Question

  • Hi,

    I've created a small utility to validate the excel file, read data and copy to another excel. The first stage is creating a dataset. While doing so I found that the values above 1677196772 are considered as NULL in the data set.

    I'm using ASP.Net and VB.Net as code behind language. Used System.Data namespace and connection string as "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<folderpath>\;Extended Properties="text;HDR=YES;FMT=Delimited";"

    command string is "SELECT * FROM [filename.csv]"

    Any help why these values are skipped while creating dataset?

    Regards,

    - JL
    Tuesday, October 13, 2009 3:40 PM

Answers

  • I would suspect that the driver is selecting a data type (e.g. 16-bit Integer or Short) for your data that cannot accomodate that large of a number. You may want to consider using a schema.ini file to define the data type for your columns.

    http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Monday, October 19, 2009 2:01 AM
    Tuesday, October 13, 2009 5:41 PM
  • I'm not aware of a workaround other than using a schema.ini file. You could also try using pure .NET code with the TextFieldParser class:

            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\List.txt")
    
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(",")
    
            Dim CurrentRow As String()
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
                    'Dim CurrentField As String
                    Console.WriteLine(CurrentRow(0).ToString)
                    Console.WriteLine(CurrentRow(1).ToString)
                    Console.WriteLine(CurrentRow(2).ToString)
                    Console.WriteLine(CurrentRow(3).ToString)
                    'For Each CurrentField In CurrentRow
                    ' Console.Write(CurrentField & Space(1))
                    'Next
                Catch ex As _
                Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & _
                    "is not valid and will be skipped.")
                End Try
                Console.WriteLine()
            End While
            TextFileReader.Dispose()

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Monday, October 19, 2009 2:01 AM
    Wednesday, October 14, 2009 1:08 PM

All replies

  • I would suspect that the driver is selecting a data type (e.g. 16-bit Integer or Short) for your data that cannot accomodate that large of a number. You may want to consider using a schema.ini file to define the data type for your columns.

    http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Monday, October 19, 2009 2:01 AM
    Tuesday, October 13, 2009 5:41 PM
  • Thanks Paul... The issue looks like considering Int32.

    I can overwrite this setting by defining in schema.ini but the pboelm is I have to provide CSV file name which is dynamnic. There is a way to provide dynami CSV names by creating dynami schema.ini... But not sure how will it impact in our environment where more than 100 users uploading files (different names) from different region.

    Can I change this setting in registry or in the connection string to force to use Long instead of Int32?

    Regards,
    - JL
    Wednesday, October 14, 2009 8:41 AM
  • I'm not aware of a workaround other than using a schema.ini file. You could also try using pure .NET code with the TextFieldParser class:

            Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Documents and Settings\...\My Documents\My Database\Text\List.txt")
    
            TextFileReader.TextFieldType = FileIO.FieldType.Delimited
            TextFileReader.SetDelimiters(",")
    
            Dim CurrentRow As String()
            While Not TextFileReader.EndOfData
                Try
                    CurrentRow = TextFileReader.ReadFields()
                    'Dim CurrentField As String
                    Console.WriteLine(CurrentRow(0).ToString)
                    Console.WriteLine(CurrentRow(1).ToString)
                    Console.WriteLine(CurrentRow(2).ToString)
                    Console.WriteLine(CurrentRow(3).ToString)
                    'For Each CurrentField In CurrentRow
                    ' Console.Write(CurrentField & Space(1))
                    'Next
                Catch ex As _
                Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & _
                    "is not valid and will be skipped.")
                End Try
                Console.WriteLine()
            End While
            TextFileReader.Dispose()

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Monday, October 19, 2009 2:01 AM
    Wednesday, October 14, 2009 1:08 PM