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.



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;,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>', " & _

  '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

End Sub

If you are using MySQL, it provides a direct way to export data into the database:

If you are using SQLite/PostGres, there is a commercial offering from Devart to do the same:

Need Your Help

how to work with strings and integers as bit strings in python?

python artificial-intelligence bit-manipulation genetic-algorithm data-conversion

I'm developing a Genetic Algorithm in python were chromosomes are composed of strings and integers. To apply the genetic operations, I want to convert these groups of integers and strings into bit

How to determine the size of an allocated C buffer?

c malloc buffer buffer-overflow

I have a buffer and want to do a test to see if the buffer has sufficient capacity I.e. find number of elements I can add to the buffer.