Import Excel sheet into Gridview and SQL
-
I am needing to import excel data into my program. I am using Visual Studio 2019 web development and my code behind runs VB. I would like to load data into gridview and from there into sql. I have a lot of work to do to clean up the data before my final product is ready. So I am wiring a routing to clean up the data. Anyone have an idea. I have been searching online but most of the options does not work. If you have the answer please also provide me with the imports statements I need.
-
I am needing to import excel data into my program. I am using Visual Studio 2019 web development and my code behind runs VB. I would like to load data into gridview and from there into sql. I have a lot of work to do to clean up the data before my final product is ready. So I am wiring a routing to clean up the data. Anyone have an idea. I have been searching online but most of the options does not work. If you have the answer please also provide me with the imports statements I need.
It is not clear what your actual problem is, but importing from Excel is fairly painless in .NET. You just need to use the Microsoft.Office.Interop.Excel Namespace | Microsoft Docs[^]. Google will find you sample code. [edit] As @RichardDeeming points out below, you cannot do this in a web application. But you could do it offline if that is an option. [/edit]
-
It is not clear what your actual problem is, but importing from Excel is fairly painless in .NET. You just need to use the Microsoft.Office.Interop.Excel Namespace | Microsoft Docs[^]. Google will find you sample code. [edit] As @RichardDeeming points out below, you cannot do this in a web application. But you could do it offline if that is an option. [/edit]
You can't use Excel interop in a web application. :)
Considerations for server-side Automation of Office[^]
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I am needing to import excel data into my program. I am using Visual Studio 2019 web development and my code behind runs VB. I would like to load data into gridview and from there into sql. I have a lot of work to do to clean up the data before my final product is ready. So I am wiring a routing to clean up the data. Anyone have an idea. I have been searching online but most of the options does not work. If you have the answer please also provide me with the imports statements I need.
Michael Hinkle wrote:
web development
You won't be able to use Excel automation to do this:
Considerations for server-side Automation of Office[^]
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
Instead, use a library which supports reading Excel files without having Excel installed. For Excel 2007 files
(*.xlsx), any of the following should work:- ClosedXML[^];
- ExcelDataReader[^];
- The OpenXML SDK[^];
If you need to support old-format files
(*.xls), then you can use NPOI[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You can't use Excel interop in a web application. :)
Considerations for server-side Automation of Office[^]
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
It is not clear what your actual problem is, but importing from Excel is fairly painless in .NET. You just need to use the Microsoft.Office.Interop.Excel Namespace | Microsoft Docs[^]. Google will find you sample code. [edit] As @RichardDeeming points out below, you cannot do this in a web application. But you could do it offline if that is an option. [/edit]
THe only purpose in me doing this is to get my data in to sql so that I can start cleaning the old data up (It is a mess), and making the needed changes. The only time I would use this is a few times when I need to reupload data.
-
I am needing to import excel data into my program. I am using Visual Studio 2019 web development and my code behind runs VB. I would like to load data into gridview and from there into sql. I have a lot of work to do to clean up the data before my final product is ready. So I am wiring a routing to clean up the data. Anyone have an idea. I have been searching online but most of the options does not work. If you have the answer please also provide me with the imports statements I need.
Assuming SQL means SQl server the why not do the clean up in SSMS. My standard method was: Import the data manually into SQL Server. I would load the data into a new table accepting whatever garbage is in the excel file. All data should be converted to strings on the way in. Create and empty destination table matching your final destination (this is so you can repeat the process) Write a stored procedure that: loops each column and cleans and formats the data as required and inserts it into the temp table. When you are confident the process works then change the destination to the final destination table. If this works consistently between loads you can then turns the entire process into a Job which can then be launched by your application. This method does require reasonable T-SQL skills but would also be an excellent learning exercise.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
I am needing to import excel data into my program. I am using Visual Studio 2019 web development and my code behind runs VB. I would like to load data into gridview and from there into sql. I have a lot of work to do to clean up the data before my final product is ready. So I am wiring a routing to clean up the data. Anyone have an idea. I have been searching online but most of the options does not work. If you have the answer please also provide me with the imports statements I need.
Here is something that I use in my web app ... 1) Define a connection string pointing to the Excel file
String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties='Excel 12.0; HDR=Yes; IMEX=1;'", sFilename)
- You can access some schema information from the Excel doc like this ...
DTschema = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
- Then get to the actual data in the spreadsheet like this ...
Using dbCmd As New OleDbCommand("SELECT * FROM [" & sSheet & "]", dbConn)
Using dbAdapter As New OleDbDataAdapter(dbCmd)
dbAdapter.Fill(DT)
End UsingIt may not be pretty, but you get the data from the spreadsheet into a datatable which can then be bound to a Gridview. See if it works for you. :thumbsup:
-
I am needing to import excel data into my program. I am using Visual Studio 2019 web development and my code behind runs VB. I would like to load data into gridview and from there into sql. I have a lot of work to do to clean up the data before my final product is ready. So I am wiring a routing to clean up the data. Anyone have an idea. I have been searching online but most of the options does not work. If you have the answer please also provide me with the imports statements I need.
