Menu
I have an Excel Spreadsheet that contains all my data that I need to put into an SQL Server database. I am fairly new o ASP.NET and have never had to export from Excel to SQL Server before.
My Excel spreadsheets looks like this
Trade Heading -> ArtID -> BusinessName -> AdStyleCode -> Address -> Suburb
In SQL Server I have created a table named "Listings" which is in this format
intListingID -> intCategoryID -> BusinessName - ArtID -> intAdCode ->Address -> Suburb
What would be the best way to export the data from Excel and then import it into SQLServer 2005.
Thanks...
984
Click on the Databases tab, and drop your Excel file in the Select Table column. This will launch a window that lets you verify your column names. If it's all looking good, give the table a name, and click on Create Table. You're done!
On the SSIS menu, select New connection. In the Add SSIS Connection Manager dialog box, select EXCEL and then Add. Create the connection manager at the same time that you configure the Excel Source or the Excel Destination on the Connection manager page of the Excel Source Editor or of the Excel Destination Editor.
The SQL Spreads Table Creator is a new feature in SQL Spreads 5.0 to create SQL Server tables directly from within Excel and pre-load them with the existing data in your Excel spreadsheet. The Table Creator makes it possible to create SQL Server tables in just a few clicks.
You can do this easily using SSIS, you can refer to these two links for full details.
[EDIT]
If you have Express then you can try the below commands to setup a linked server and get the data
EXEC sp_addlinkedserver ExcelData,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:\MyData.xls', NULL, 'Excel 5.0;'
GO
Then you can select the data into your tables
INSERT INTO Listings ...
SELECT column1 AS intListingID, <put all columns here> FROM ExcelData...Data
GO
For other options check this link
175
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
