export to datatable problem with datatype

[rami boubaker]

Hi,

i have problem with datatype , column values are date but in thedata table their datatype are string
i have used
Dim customersTable As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
and also
Dim customersTable As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.DetectColumnTypes)
always date are string in the datatable.

[Abirami Varadharajan]

Hi Rami, 

Thank you for contacting Syncfusion support. 

We are unable to reproduce the issue. We have prepared simple sample and shared for your reference which can be downloaded from the following link. 

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIO_Sample-1826719693.zip  

It is recommended to upgrade to our latest release version 16.1.0.37 with more bug fixes and enhancements which is available for download under below link. 

Download Link:  

https://www.syncfusion.com/forums/137209/essential-studio-2018-volume-1-service-pack-release-v16-1-0-37-available-for-download  

 

If the issue still exists, kindly modify the above sample to reproduce the issue and share us the issue reproducing sample, which will be helpful for us to provide prompt solution at the earliest. 

Regards, 

Abirami. 

[rami boubaker]

i want to create a table from the excel file (excel file has header).
the problem is that the type is always string for all , the table don't give the right format for date type.

this is my code :

Dim table As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)

sqlsc = "CREATE TABLE " + tableName + "([Id] [int] IDENTITY(1,1) NOT NULL ,"

        Dim i As Integer = 0

        While i < table.Columns.Count
            Dim columname = Regex.Replace(table.Columns(i).ColumnName.ToUpper, "[^A-Za-z0-9\-/]", "")
            sqlsc += vbLf & " [" + columname + "] "
            Dim columnType As String = table.Columns(i).DataType.ToString()


            If table.Columns(i).AutoIncrement Then
                sqlsc &= " IDENTITY(" + table.Columns(i).AutoIncrementSeed.ToString() + "," + table.Columns(i).AutoIncrementStep.ToString() + ") "
            End If

            Select Case columnType
                Case "System.Int32"
                    sqlsc &= " int "
                Case "System.Int64"
                    sqlsc &= " bigint "
                Case "System.Int16"
                    sqlsc &= " smallint"
                Case "System.Byte"
                    sqlsc &= " tinyint"
                Case "System.Decimal"
                    sqlsc &= " decimal "
                Case "System.DateTime"
                    sqlsc &= " datetime "
                Case "System.String"

                    sqlsc += String.Format(" nvarchar({0}) ", If(table.Columns(i).MaxLength = -1, "max", table.Columns(i).MaxLength.ToString()))
            End Select
            'End If
            sqlsc &= ","
            System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
        End While

[Abirami Varadharajan]

Hi Rami, 

Thank you for updating us. 

It is recommended to use both DetectColumnTypes and ColumnNames Enumeration of ExcelExportDataTableOptions while Exporting data table to return the proper datatype of the datatable. Please refer to below code to achieve the same. 

Code Example: 

Dim table As DataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.DetectColumnTypes Or ExcelExportDataTableOptions.ColumnNames) 

Kindly try this and let us know if this helps at your end. 

Regards, 

Abirami.