Data Import – Excel to SQL Azure?

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?

  • SQL Server : same query running multiple times with different execution time
  • SQL Server 2005 How can I set up an audit table that records the column name updated?
  • Connect android app to SQL Server
  • I have an issue trying to UNION All in SQL Server 2008
  • Multi threading C# application with SQL Server database calls
  • time format and string format
  • Deserialized object from Entity Framework request contains badly escaped html entities
  • ADO.NET SQL Server Performance bottleneck
  • Asp.net c# SQL Server Update query not working for all columns
  • Using a SQL Table instead of a Dictionary Reference in C#
  • How to fix error “Named Pipes Provider, error: 40 - Could not open a connection to SQL Server”
  • DbGeography make circle with center and radius
  • 2 Solutions collect form web for “Data Import – Excel to SQL Azure?”

    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
    

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.