How to export data from excel file and import into to sql database?

Is there a method to do this ? I have like 6000 rows in excel, and it would take months to import it into the sql database.

For example there are : Column A and Column B in excel and I'd like to import it into Column A and Column B into an sql table.

Thanks!

Answers


Here is an approach through VBA that I used for a book database. Not too tough as long as you can establish a connection to the DB. Obviously you will have to tweek it a bit for it to work with your database and your data. In particular you will have to adjust the sql string to connect you your database (change "test.yourdatabase")

Sub addBook(title As String, author As String, link As String, foundBy As String)
  ' for this code to work, you need a reference to the MS Activex
  ' data access objects
  ' Tools|References... microsoft activex data objects 2.8 (or newer)

  Dim cn As New Connection
  Dim cs As String
  Dim un as String
  Dim pw as String
  Dim sql As String


  cs = "DRIVER=SQL Server;SERVER=websql.org,5901;DATABASE=websql" 'connection string 

  un = username 'this is the username for the database

  pw = password 'this is the password for the database account

  cn.Open cs, Range("un").Value, Range("pw").Value


  sql = "insert into test.yourdatabase(searchtitle, searchAuthor, link, foundBy, foundTime) values(" & _
  "'<TITLE>', " & _
  "'<AUTHOR>', " & _
  "'<LINK>', " & _
  "'<FOUNDBY>', " & _
  "getdate());"

  'replace statements are for correcting any ' that occure in the titles or names

  sql = Replace(sql, "<TITLE>", Replace(title, "'", "''"))
  sql = Replace(sql, "<AUTHOR>", Replace(author, "'", "''"))
  sql = Replace(sql, "<LINK>", Replace(link, "'", "''"))
  sql = Replace(sql, "<FOUNDBY>", Replace(foundBy, "'", "''"))

  'Debug.Print sql

 cn.Execute sql

  cn.Close
End Sub

Need Your Help

What is Scala equivalent of Java's static block?

java scala static-block

What is Scala equivalent of Java's static block ?

Receive a Jagged Array in VBA (VB6)

vb.net vba vb6 arrays

I currently have a VB.NET dll that returns a jagged array of double. This is the declaration:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.