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?

  • saving image in sql data base how?
  • Java connection to a SQL Server Database: Login failed for user 'sa'
  • Excel read-only and Microsoft Query/ADO Query
  • using an Android app to post entries to SQL Server
  • MS Access passthrough select query causing page lock in SQL Server
  • Assign a login to a user created without login (SQL Server)
  • Exception DATABASE1.MDF' cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported
  • Linq To Sql: Exception “String must be exactly one character long”
  • Database Error: There is no row at position 0
  • C# equivalent of the IsNull() function in SQL Server
  • Retrieving Return value from stored procedure
  • Proper way to null check in Expression-Tree lambda
  • 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.