How to convert EXCEL table to SQL query

October 4, 2015

Surely you have encountered problem with converting EXCEL document to database. It is no problem to insert few records to database via any GUI client. But what if Excel document contains thousands rows and tens of columns?

There numerous tutorials how to achieve this by Exporting EXCEL document to CSV format and then importing CSV into database via GUI database client (for MySQL there is PHPMyAdmin and for Oracle there is SQL developer). Yes indeed, this way you can convert most Excel documents into database very easily and fast. While this method works fine with plain copy & paste table cells into database, you will encounter problem when you have to process data before insertion. CSV import will not solve this.

Let’s have this table (first records is on second row in EXCEL document):

customer_id first_name second_name address house_number
100 Peter Kadlec 5th Avanue 14
101 John Ballock Green lake 25/45
102 Abdul Habib
103 Tom Lurfurson Riverbound street 1050
104 Xiao Tung Peng Polgrad 5423/125

 

In general you just need to concatenate INSERT string with cell values. While doing this we can use conditions and other EXCEL functions to achieve result which would be impossible with CSV import without creating new table.For our test table we can create this EXCEL function to generate SQL queries (we need to merge columns address and house_number into one column):

=”INSERT INTO customers VALUES ( ‘” & A2 & “‘, ‘” & B2 & “‘, ‘” & C2 &”‘, ‘” & D2 &” ” & E2&   “‘);”

Output from this function for every row looks like this ( Don’t forget to run this function on every row):

INSERT INTO customers VALUES ( ‘100’, ‘Peter’, ‘Kadlec’, ‘5th Avanue 14’);
INSERT INTO customers VALUES ( ‘101’, ‘John’, ‘Ballock’, ‘Green lake 25/45’);
INSERT INTO customers VALUES ( ‘102’, ‘Abdul’, ‘Habib’, ‘ ‘);
INSERT INTO customers VALUES ( ‘103’, ‘Tom’, ‘Lurfurson’, ‘Riverbound street 1050’);
INSERT INTO customers VALUES ( ‘104’, ‘Xiao’, ‘Tung Peng’, ‘Polgrad 5423/125’);

You can use generated inserts to fill up your database.


No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *