This is the situation. I'm able to upload the excel file with no problem. I'm not sending the records into a model. Now here are the issues.
1) Compare the excel files records with current records in a sql server table based on id. If current, just update, if not, just add.
This is the part i'm having the most difficulty.
2) How can I mapp the excel file column headings to the sql server table columns....IF the excel files will have different column names based on who is submitting them. Even though regardless of the Excel file, all the different type of columns names are
all in one table. For example: customer 1 sends a excel file with
name, address,...... customer 2 sends a excel file with
name, address, state, and zip. All those fields names are in the table which consist of...name, address, state, zip, phone, email. How do I map the 2 different excel file column names to the same table. I'm trying to use the models based
of stored procedures but at this rate anything good will do. All info is appreciated. Im using MVC2/C#
1) Compare the excel files records with current records in a sql server table based on id. If current, just update, if not, just add.
Just write a Stored Procdure to check each record of ID in the Excel file in SQL related db——whether it exists or not？And do updating or inserting。
Jon11sin
How can I mapp the excel file column headings to the sql server table columns....IF the excel files will have different column names based on who is submitting them.
Maybe you should read out the two Excel files with two instances of OleDB，and then use OleDbDataAdapter.Fill methods into two DataTables，and then compare which columns they all have as common ones。
Thanks for your reply. Well I have a Stored Procedure right now..which I'm attempting to insert the Excel file data into a temp table for the comparision. I'm using OpenRowSet in the query to grab the data from Excel but I keep getting the error message
of ex = {"The Microsoft Access database engine could not find the object 'OPENROWSET'. Make sure the object exists and that you spell its name and the path name correctly. If 'OPENROWSET' is not a local object, check your network connection or contact the
server ad.... Keep in mind I'm using Sql Server 2008 Express for local development. Not sure if that is the issue.
I don't want to compare the two Excel files. I want to grab all the data from the Excel files but one Excel file may have more columns(fields) than another Excel file. Currently I have all the different columns I anticipate in a table. So if one Excel
file has 4 columns of data than those fields will have the data inserted into them. Then if another Excel file has 7 columns than that data will go to the corresponding fields. But the issue is how do I map the columns to its respective field. Another option
I'm thinking is just have additional fields added to the table if there is no field for a specific column in the Excel file. I hope what I stated makes sense.
I'm not doing this manually. This is being programmed in an MVC2 web application where a customer can upload the excel file. Then the file is read by column name and compared to a field in a database. If that field isn't present, I may want to add that
column as a new field in the table or in a additional table. But the task I'm stuck right now is the getting the excel file data in a tempt table for the comparison. I would like to do all of this in a stored procedure.
My addition：My first reply means that you can fetch all the columns' names by using Table.Columns and do comparation。If there's a column missing，you can just call Tables.Columns.Add……to add a new column inside。
Ok. I will give that a try. I'm trying to pull the Excel data into a temp table but for some reason the query i'm using in a stored procedure isn't working.
SELECT * INTO #mytemptable FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0,Excel 8.0;Database="+ Server.MapPath("../Uploads/")
+ ",
SELECT * FROM [Sheet2$]",conn); But keep getting errors so I will try the method you indicated above and then attempt to create a temp table based on the results.
hi..please try this..u can pass path as a parameter..to Sp
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
A.Venkatesan
Microsoft Certified Professional
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact venkatmca008@gmail.com
Ok. I was finally able to import Excel Data into a temp table. It was permission issues that I had to deal with. So I'm at the point where I'm comparing the columns of the current table and the temp table as the column mapping process. Which appears
to be the fork in the road as to what possible method I should use.
Jon11sin
Member
184 Points
166 Posts
Upload Excel Files to SQl Server database
Mar 01, 2012 06:40 PM|LINK
This is the situation. I'm able to upload the excel file with no problem. I'm not sending the records into a model. Now here are the issues.
1) Compare the excel files records with current records in a sql server table based on id. If current, just update, if not, just add.
This is the part i'm having the most difficulty.
2) How can I mapp the excel file column headings to the sql server table columns....IF the excel files will have different column names based on who is submitting them. Even though regardless of the Excel file, all the different type of columns names are all in one table. For example: customer 1 sends a excel file with name, address,...... customer 2 sends a excel file with name, address, state, and zip. All those fields names are in the table which consist of...name, address, state, zip, phone, email. How do I map the 2 different excel file column names to the same table. I'm trying to use the models based of stored procedures but at this rate anything good will do. All info is appreciated. Im using MVC2/C#
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Upload Excel Files to SQl Server database
Mar 03, 2012 12:32 AM|LINK
Just write a Stored Procdure to check each record of ID in the Excel file in SQL related db——whether it exists or not？And do updating or inserting。
Maybe you should read out the two Excel files with two instances of OleDB，and then use OleDbDataAdapter.Fill methods into two DataTables，and then compare which columns they all have as common ones。
Reguards！
Jon11sin
Member
184 Points
166 Posts
Re: Upload Excel Files to SQl Server database
Mar 05, 2012 01:40 PM|LINK
Decker,
Thanks for your reply. Well I have a Stored Procedure right now..which I'm attempting to insert the Excel file data into a temp table for the comparision. I'm using OpenRowSet in the query to grab the data from Excel but I keep getting the error message of ex = {"The Microsoft Access database engine could not find the object 'OPENROWSET'. Make sure the object exists and that you spell its name and the path name correctly. If 'OPENROWSET' is not a local object, check your network connection or contact the server ad.... Keep in mind I'm using Sql Server 2008 Express for local development. Not sure if that is the issue.
I don't want to compare the two Excel files. I want to grab all the data from the Excel files but one Excel file may have more columns(fields) than another Excel file. Currently I have all the different columns I anticipate in a table. So if one Excel file has 4 columns of data than those fields will have the data inserted into them. Then if another Excel file has 7 columns than that data will go to the corresponding fields. But the issue is how do I map the columns to its respective field. Another option I'm thinking is just have additional fields added to the table if there is no field for a specific column in the Excel file. I hope what I stated makes sense.
vetrivelan R...
Member
82 Points
42 Posts
Re: Upload Excel Files to SQl Server database
Mar 05, 2012 01:54 PM|LINK
You can import the Data from excel to Sql server tables by using All Task -- Import data, then you can comare with your original table..
Jon11sin
Member
184 Points
166 Posts
Re: Upload Excel Files to SQl Server database
Mar 05, 2012 02:39 PM|LINK
I'm not doing this manually. This is being programmed in an MVC2 web application where a customer can upload the excel file. Then the file is read by column name and compared to a field in a database. If that field isn't present, I may want to add that column as a new field in the table or in a additional table. But the task I'm stuck right now is the getting the excel file data in a tempt table for the comparison. I would like to do all of this in a stored procedure.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Upload Excel Files to SQl Server database
Mar 06, 2012 12:19 AM|LINK
My addition：My first reply means that you can fetch all the columns' names by using Table.Columns and do comparation。If there's a column missing，you can just call Tables.Columns.Add……to add a new column inside。
Reguards！
Jon11sin
Member
184 Points
166 Posts
Re: Upload Excel Files to SQl Server database
Mar 06, 2012 02:21 PM|LINK
Ok. I will give that a try. I'm trying to pull the Excel data into a temp table but for some reason the query i'm using in a stored procedure isn't working.
SELECT * INTO #mytemptable FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0,Excel 8.0;Database="+ Server.MapPath("../Uploads/") + ", SELECT * FROM [Sheet2$]",conn); But keep getting errors so I will try the method you indicated above and then attempt to create a temp table based on the results.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Upload Excel Files to SQl Server database
Mar 07, 2012 12:21 AM|LINK
What error？
venkatmca008
Participant
1810 Points
341 Posts
Re: Upload Excel Files to SQl Server database
Mar 07, 2012 12:37 AM|LINK
hi..please try this..u can pass path as a parameter..to Sp
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])Microsoft Certified Professional
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact venkatmca008@gmail.com
Jon11sin
Member
184 Points
166 Posts
Re: Upload Excel Files to SQl Server database
Mar 07, 2012 02:08 PM|LINK
Ok. I was finally able to import Excel Data into a temp table. It was permission issues that I had to deal with. So I'm at the point where I'm comparing the columns of the current table and the temp table as the column mapping process. Which appears to be the fork in the road as to what possible method I should use.