none
Create table in SQL Server using OLE DB datatypes RRS feed

  • Question

  • I want to be able to upload Excel spreadsheets to a SQL Server Database. I want to use SqlBulkCopy. The problem is, one of the columns in the spreadsheet, when read by the OleDbDataReader, changes to one of two different data types. When I am there to see it, I can make the adjustments, but I am trying to build an program that will work regardless of how the column is read that particular read. So, I decided to use:

    GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null)

    to get the COLUMN_NAME and DATA_TYPE. This way, not only can I can create the table dynamically in SQL Server with matching data types and I will be certain that the data will upload correctly, I can set up functionality to upload any Excel file, so long as it is in a single folder and registered in my database (create a table where any Excel file with a specific name will upload to a specific table, and once it is uploaded, it will run a specific stored procedure to move the data from the Bulk Copy table to regular data tables). So, the question is, what is the conversion between Ole DB datatypes and SQL Server datatypes? Is there a default conversion table? I found several, but none of them convert OLE DB types to SQL Server types or vice versa.

    https://msdn.microsoft.com/en-us/library/windows/desktop/ms715855(v=vs.85).aspx

    https://msdn.microsoft.com/en-us/library/windows/desktop/ms711251(v=vs.85).aspx

    Neither of these links tells me the conversion. I can manually set up a conversion system, but I was hoping to not have to reinvent the wheel here.

    Monday, January 30, 2017 8:03 PM

Answers

  • Thanks! What I wound up doing was just using the OleDbType enumeration and going through the dropdown list in Visual Studio. I used more generic types than each original type, and this is what I came up with:

    private static string GetSqlDataType(OleDbType oleType)
            {
                switch(oleType)
                {
                    case OleDbType.BigInt:
                    case OleDbType.UnsignedBigInt:
                    case OleDbType.UnsignedInt:
                        return "BigInt";
                    case OleDbType.Binary:
                    case OleDbType.LongVarBinary:
                    case OleDbType.VarBinary:
                        return "binary(max)";
                    case OleDbType.Boolean:
                        return "bit";
                    case OleDbType.BSTR:
                    case OleDbType.Char:
                    case OleDbType.LongVarChar:
                    case OleDbType.LongVarWChar:
                    case OleDbType.VarChar:
                    case OleDbType.VarWChar:
                    case OleDbType.WChar:
                        return "nvarchar(max)";
                    case OleDbType.Currency:
                    case OleDbType.Decimal:
                    case OleDbType.Double:
                    case OleDbType.Numeric:
                    case OleDbType.Single:
                    case OleDbType.VarNumeric:
                        return "float";
                    case OleDbType.Date:
                        return "date";
                    case OleDbType.DBDate:
                    case OleDbType.Filetime:
                        return "datetime";
                    case OleDbType.DBTime:
                        return "time";
                    case OleDbType.Guid:
                        return "uniqueidentifier";
                    case OleDbType.Integer:
                    case OleDbType.SmallInt:
                    case OleDbType.TinyInt:
                    case OleDbType.UnsignedSmallInt:
                    case OleDbType.UnsignedTinyInt:
                        return "int";
                }
                return "";
            }
    I have yet to test this out, but it should be able to handle any datatype it finds from Excel. There were a few types I ignored, though. Like OleDbType.Error. I figure, if I get that datatype, I probably don't want to add that table to the database anyway, and I should probably throw an exception.
    Friday, February 3, 2017 4:01 PM

All replies

  • You might want to use the following tables to do your mapping. I do not know of an automatic method:

    OLE DB Data Type Mappings

    SQL Server Data Type Mappings

    The below link is older but since Excel implements Access data types when using OLEDB it may help:

    https://technet.microsoft.com/en-us/library/cc917602.aspx#EEAA


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, January 31, 2017 2:36 PM
  • Thanks! What I wound up doing was just using the OleDbType enumeration and going through the dropdown list in Visual Studio. I used more generic types than each original type, and this is what I came up with:

    private static string GetSqlDataType(OleDbType oleType)
            {
                switch(oleType)
                {
                    case OleDbType.BigInt:
                    case OleDbType.UnsignedBigInt:
                    case OleDbType.UnsignedInt:
                        return "BigInt";
                    case OleDbType.Binary:
                    case OleDbType.LongVarBinary:
                    case OleDbType.VarBinary:
                        return "binary(max)";
                    case OleDbType.Boolean:
                        return "bit";
                    case OleDbType.BSTR:
                    case OleDbType.Char:
                    case OleDbType.LongVarChar:
                    case OleDbType.LongVarWChar:
                    case OleDbType.VarChar:
                    case OleDbType.VarWChar:
                    case OleDbType.WChar:
                        return "nvarchar(max)";
                    case OleDbType.Currency:
                    case OleDbType.Decimal:
                    case OleDbType.Double:
                    case OleDbType.Numeric:
                    case OleDbType.Single:
                    case OleDbType.VarNumeric:
                        return "float";
                    case OleDbType.Date:
                        return "date";
                    case OleDbType.DBDate:
                    case OleDbType.Filetime:
                        return "datetime";
                    case OleDbType.DBTime:
                        return "time";
                    case OleDbType.Guid:
                        return "uniqueidentifier";
                    case OleDbType.Integer:
                    case OleDbType.SmallInt:
                    case OleDbType.TinyInt:
                    case OleDbType.UnsignedSmallInt:
                    case OleDbType.UnsignedTinyInt:
                        return "int";
                }
                return "";
            }
    I have yet to test this out, but it should be able to handle any datatype it finds from Excel. There were a few types I ignored, though. Like OleDbType.Error. I figure, if I get that datatype, I probably don't want to add that table to the database anyway, and I should probably throw an exception.
    Friday, February 3, 2017 4:01 PM