Excel: Generate a SQL-Query from huge Excel

Question

Hi guys,

I have a huge excel with thousands of rows and I need to generate an sql query to insert the data into a sql server database.

The format of the excel is the following

1 | string1 | another string
    string2
    string3
2 | AAA AAA | ZZZZZZZ
    BB BBBB
    CCCC CC

The first column is a row counter, it doesn't matter. The second column is a group of strings separated by break-lines The third column is a string that has to be associated to each of the strings on column 2.

So I need to generate the following queries:

INSERT INTO SomeTable VALUES ('string1', 'another string')
INSERT INTO SomeTable VALUES ('string2', 'another string')
INSERT INTO SomeTable VALUES ('string3', 'another string')
INSERT INTO SomeTable VALUES ('AAA AAA', 'ZZZZZZZ')
INSERT INTO SomeTable VALUES ('BB BBBB', 'ZZZZZZZ')
INSERT INTO SomeTable VALUES ('CCCC CC', 'ZZZZZZZ')

Is it clear what I need to do?

Unfortunately my excel habilities are so poor so I can't figure a way to do this.

Any help?

Thank you so much!

Answer 1

What kinds of DB do you plan to use?

Are you familiar with the tool 'TOAD'?

Answer 2

First create a linkedServer to the Excel worksheet in question

declare @server varchar(100),@dropLogins varchar(20)
select @server='myExcelBook',@dropLogins='droplogins'

Exec sp_dropServer @server,@droplogins=@dropLogins    


    EXEC sp_addlinkedserver @server,
       'ACE 12.0',
       'Microsoft.ACE.OLEDB.12.0',
       'c:\MyExcelBooks\myExcelBool.xls',
       NULL,
       'Excel 12.0'

exec sp_linkedServers

ACE 12.0,Excel 12.0 and Microsoft.ACE.Oledb.12.0 are provider names for Excel 2007 If you have different version of excel you have to replace those literals with correct ones. It is also required that the excel is installed on your database server.

Once you have done above.. you can treat each worksheet in the Workbook as a Table

so inserting data to SomeTable goes like this

Insert SomeTable(column1,Column2,Column3)
Select Column1,Column2+Column3,Column3 from myExcelbook..Sheet1$

Note that the $ sign is to be appended to Sheet that you are refering.

After adding the link server, you can see the Worksheets / and the Ranges under the Server Objects / LinkedServers / myExcelBook/Catalogs/Defautlt/Tables

Answer 3

Here you go,

Assuming the number is in column A, and the two strings in columns B and C respectively

Put this in cell F2

=IF(C2="",C1,C2)

and this in cell I2

="INSERT INTO SomeTable VALUES('" & B2 & "','" & F2 & "')"

then copy the formulas up and down in columns F and I.

Hopefully you should see how it works