1

I wrote a code using c#- Visual 2008- that takes:

1- an excel sheet.

2- Opens a connection to Excel

3- Reads the Column names using the OleDbDataReader object and GetTableSchema method

4- Added the Columns names in an array

5- Created a Table that has the same Column names as the EXcel sheet ( i used CREATE table
command)

6-Then once i have the table created in SQL , i loop over the excel rows an add the data into sql using Insert command.

Now My problem is:

In the " create table" command , i have to specify the DATATYPE for the column !i.e.

CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )

HOW should i solve this problem? Are the datatypes that excel columns can be, the same as the datatypes in SQL server? is there some kind of mapping ? please help me out. thank you

Jagmag
  • 10,283
  • 1
  • 34
  • 58
PeacefulSoul
  • 13
  • 2
  • Is making all columns NVarchar not ok? Do you need the datatypes to match the Actual type of the data in excel? – Jagmag Nov 04 '10 at 09:39
  • You can as much as possible to try find closest data type in SQL server for Excel Spreadsheet data. It will be easier for you to export and if required import from SQL DB. – PradeepGB Nov 04 '10 at 09:41
  • thanks for ur reply, well my goal is to have datatypes to match the Actual type of the data in excel. i cant use the import button. i need to write a code for it – PeacefulSoul Nov 04 '10 at 09:47

3 Answers3

1

OleDbDataReader.GetSchemaTable will give you what the underlying datatype is from the spreadsheet. This (quote):

Maps to the .NET Framework type of the column.

You then need to map that to the appropriate SqlDbType. Check out this other answer for ideas on how to do that.

The other point I wanted to raise as a side point, was to consider bulk loading the data from excel into SQL Server instead of "read a row / insert a row". I don't know what data volumes you're talking about, but that could speed the process up.

Community
  • 1
  • 1
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • Hello, Well the first link did not open. and the second is not when im looking for. i want to map Excel Column DataTypes To Sql Data Types. :) – PeacefulSoul Nov 04 '10 at 10:10
  • @PeacefulSoul - sorry, link fixed. You should be able to do what you want from this as GetSchemaTable will return the type of the column which maps to the .NET type - from this .NET type, you can then get to the equivalent SqlDbType per that second link. – AdaTheDev Nov 04 '10 at 10:25
0

I believe you can use. sql_variant data type if you dont know what kind it is going to be? i'd take a look at that.

http://msdn.microsoft.com/en-us/library/ms173829.aspx

  • i have the excel columns, and as Yves said , i can use this: DataTable schema = rdr.GetSchemaTable(); foreach (DataRow row in schema.Rows) { foreach (DataColumn col in schema.Columns) Console.WriteLine(col.DataType); } to get the column datatypes. but my question remains: Do i have to do some kind of matching between the data types i get from excel and the Datatypes that i should actually add in the Sql database? – PeacefulSoul Nov 04 '10 at 09:51
  • http://msdn.microsoft.com/en-us/library/ms712640%28VS.85%29.aspx this describes how the different excel data types are mapped to SQL and how you want to go about matching them. Is that kinda what u were looking for? –  Nov 04 '10 at 09:56
0

You can figure out the data type of the column using GetTableSchema()...

              DataTable  schema  =  rdr.GetSchemaTable();   

              foreach  (DataRow  row  in  schema.Rows)     
              {   
                    foreach  (DataColumn  col  in  schema.Columns)   
                          Console.WriteLine(col.DataType);   
              }
Yves M.
  • 3,330
  • 14
  • 12
  • Thank you for your reply. Are all the DataTypes of Excel Found in Sql ? So if i add the col.DataType in my Create Command, it will work fine ? – PeacefulSoul Nov 04 '10 at 09:43
  • You need to translate the types into the sql types. But the types are mainly float, integer, datetime and varchar anyways. – Yves M. Nov 04 '10 at 10:02
  • exactly what i want. but how do i do this ? how do i know which Excel type is translated to which SqlDatatype ? – PeacefulSoul Nov 04 '10 at 10:11