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;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
If you are using MySQL, it provides a direct way to export data into the database: https://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-export.html
If you are using SQLite/PostGres, there is a commercial offering from Devart to do the same: