1

Are there any available tools that make importing data from an Excel sheet (or even Access) to SQL Azure easy? I could write a C# script (and I'm assuming that the best available right now) but I thought I'd check in case there were other tools out there. I have as simple Excel sheet to import but its tens-of-thousands of records and would rather do it with an automated tool.

Help?

Unknown Coder
  • 6,625
  • 20
  • 79
  • 129
  • According to the [documentation](http://msdn.microsoft.com/en-us/library/windowsazure/ee730904.aspx), SSIS and bcp are both supported. Have you looked at them already? – Pondlife Mar 18 '13 at 18:15
  • Arent those both console based tools? I guess I was hoping for something with a GUI – Unknown Coder Mar 18 '13 at 18:19
  • bcp is a command-line tool, SSIS packages are developed in Visual Studio. Whether or not they're "easy" to use depends on who's using them; they aren't intended to be end-user tools. – Pondlife Mar 18 '13 at 18:24
  • Are you running Visual Studio? – QFDev Mar 19 '13 at 12:52
  • Yes I do and I'm pretty sure I also have SSIS installed. I will have to play with it some more but I dont recall it as being the most stable plugin I have in VS – Unknown Coder Mar 19 '13 at 16:23

2 Answers2

6

You can also use SQL Server Import & Export Data (32 bit) to transfer data from an excel file to a table on sql azure. It is extremely fast. Make sure when you choose a destination, it is .Net Framework Data Provider for SqlServer. You will have to set Data Source, User ID, Password & Initial Catalog property as well.

user2585299
  • 873
  • 2
  • 19
  • 41
4

Option 1, Copy and Paste

This a very crude solution but I have used it before, admittedly with smaller volumes of data. Simply select the rows and columns in Excel, making sure they correlate with your database table schema. Copy to the Clipboard. Open up a connection to your database in Visual Studio select the last empty row and paste.

Like I said, a very crude solution but if it works and saves you time that's what matters. Try with a small selection of rows first and don't forget to exclude any auto-incrementing columns. If you convert to CSV first you will nuke any Excel formatting that may not import into your table schema. I have to be honest I don't know how this method will perform with larger datasets, obviously it will depend on the data in the Excel document, number of columns etc. You may need to move the data in chunks but it could well be quicker than other methods.

Option 2, CSV to DataTable

The second method requires some coding but will give you more control over how the data is mapped to the data table. It basically involves converting the document to a CSV from Excel, reading into a DataTable object looping the rows and inserting to the SQL Database table.

Dim dt As DataTable = New DataTable("myData")
Dim DataFile As FileInfo = New FileInfo("c:\myspreadsheet.csv")

Dim MyConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & DataFile.Directory.FullName & "';Extended Properties='text;HDR=NO';Jet OLEDB:Engine Type=96;")
Dim oledataAdapter As OleDbDataAdapter
oledataAdapter = New OleDbDataAdapter("SELECT * FROM [" & DataFile.Name & "]", MyConnection)

oledataAdapter.Fill(dt) 'Bind the csv to the data table

'LOOP AND INSERT HERE...
For Each DataRowObj As DataRow In dt.Rows

Next
QFDev
  • 8,668
  • 14
  • 58
  • 85