Export Excel file to SQL

[JX]

I'm currently doing a project where it require client to
export excel spreadsheets to SQL. the application that is
using the excel spreadsheet exported is a web-based
application. Is it possible to do that? Any suggestion or
recommendation is greatly appreciated. thank you

 

[Guest]

Go to Enterprise Manager.
Browse to your database in the left pane (object browser).
Right click your database.
All Tasks --> Import Data.
Next.
Set your datasource to Microsoft Excel 97-2000 or whichever Excel version you have.
Click the ... button next to the File Name text box and browse to your Excel sheet.
Make sure your destination is Microsoft OLE DB Provider for SQL Server and the Server is pointed to the correct Server.
Choose your authentication method.
Choose your database you want to import to in the dropdown.
Next.
Copy table(s) and view(s) from the source datasource if you want all rows and worksheets.
Next.
Click the checkbox next to each worksheet you want to import. I believe it imports each one as a separate table.
You can rename the table here: [database].[dbo].[Sheet1$] - ex) change [Sheet1$] to [tableName]
You can preview your table at this point.
Click next , Finish or Run immediately or whatever and now you have a SQL Server table from your Excel sheet.
Be wary, I often find the first row is not imported, so you may need to do an insert in Query Analyzer. You may also want to copy this data into a temp table, create a new table with the corresponding columns data types defined and copy the temp table into this new table to format your data.