checking the fieldname in the table
-
Hi, I have one table in excel and I would like to send the data from excel to sql server. The excel data are not fixed. The columns name varies depending on the user. so let say if i have one table in excel named StuentInfo. Then the columns in that table may includes the field like ID, studentID,student ID like that along with other detail info. For example table field may be studentID studentName studentDetails or ID studentName studentDetails or ID student_Id studentName studentDetails so since ID can be studentID,ID or student_Id, i would need to check which of them the table contains. Is it anyway I can check the columnName in the table ? In one of the table, there are two Ids viz ID and student_Id. In that case, we are considering student_Id as the correct one? Is there any query expression to check the column name of the table?
suchita
-
Hi, I have one table in excel and I would like to send the data from excel to sql server. The excel data are not fixed. The columns name varies depending on the user. so let say if i have one table in excel named StuentInfo. Then the columns in that table may includes the field like ID, studentID,student ID like that along with other detail info. For example table field may be studentID studentName studentDetails or ID studentName studentDetails or ID student_Id studentName studentDetails so since ID can be studentID,ID or student_Id, i would need to check which of them the table contains. Is it anyway I can check the columnName in the table ? In one of the table, there are two Ids viz ID and student_Id. In that case, we are considering student_Id as the correct one? Is there any query expression to check the column name of the table?
suchita
I'd use an EnumTransmogrifier<T> [^]. Make an enum like:
enum ColumnName
{
[System.ComponentModel.DescriptionAttribute("ID")]
[System.ComponentModel.DescriptionAttribute("student_Id")]
studentIDetc.
studentNameetc.
studentDetails
}Then, when you open a file, parse the actual column names to a logical column names and cache the indices. Slick as snot.
-
Hi, I have one table in excel and I would like to send the data from excel to sql server. The excel data are not fixed. The columns name varies depending on the user. so let say if i have one table in excel named StuentInfo. Then the columns in that table may includes the field like ID, studentID,student ID like that along with other detail info. For example table field may be studentID studentName studentDetails or ID studentName studentDetails or ID student_Id studentName studentDetails so since ID can be studentID,ID or student_Id, i would need to check which of them the table contains. Is it anyway I can check the columnName in the table ? In one of the table, there are two Ids viz ID and student_Id. In that case, we are considering student_Id as the correct one? Is there any query expression to check the column name of the table?
suchita
Hi Suchita, You can get column names in SQl Server using the following query: --This will give you all the column names in the customer table Select COLUMN_NAME From .INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Customer' Hope this solves your problem. Thanks, Kaifee
-
Hi Suchita, You can get column names in SQl Server using the following query: --This will give you all the column names in the customer table Select COLUMN_NAME From .INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Customer' Hope this solves your problem. Thanks, Kaifee
Will that work with Excel? Using a DataReader is simpler and works with any ADO.net-compatible database.
-
Hi, I have one table in excel and I would like to send the data from excel to sql server. The excel data are not fixed. The columns name varies depending on the user. so let say if i have one table in excel named StuentInfo. Then the columns in that table may includes the field like ID, studentID,student ID like that along with other detail info. For example table field may be studentID studentName studentDetails or ID studentName studentDetails or ID student_Id studentName studentDetails so since ID can be studentID,ID or student_Id, i would need to check which of them the table contains. Is it anyway I can check the columnName in the table ? In one of the table, there are two Ids viz ID and student_Id. In that case, we are considering student_Id as the correct one? Is there any query expression to check the column name of the table?
suchita
Have you thought about using the openrowset functionality of SQL Server? and letting SQL check to see if the values in the spreadsheet are in the database? rather than Excel doing the work! MSDN: Openrowset[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
Hi Suchita, You can get column names in SQl Server using the following query: --This will give you all the column names in the customer table Select COLUMN_NAME From .INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Customer' Hope this solves your problem. Thanks, Kaifee
-
Have you thought about using the openrowset functionality of SQL Server? and letting SQL check to see if the values in the spreadsheet are in the database? rather than Excel doing the work! MSDN: Openrowset[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch