[RESOLVED] [Excel] Need to read Excel file and generate SQL Insert statements

[MMock]

It's been *years* since I've worked with VBA. So I have some basic questions. I got as far as creating an empty module. How do I define the entry point to my program? In other words, if I write instructions, what function name do I put them in and how do I call that function?

I am trying to find a tutorial but either I am finding specific questions or stuff that is too basic, like datatypes and how to create a comment. So I apologize for the very simple question. If you want a hard one, read on.

The task I have to accomplish is that I have an Excel file and I need to read each record. Depending on if a column contains a value or not, I need to create sql insert statements where one of the values I'll be inserting is the value in the column. One records in the Excel file could result in 15 sql inserts. So I was going to read each Excel file record, create an array of inserts, then process the array and use ADO to insert them. This is a one-shot program so it doesn't have to be pretty, but it has to be correct. So any suggestions you have I'd be grateful for.

Thanks.

[westconn1]

how do I call that function?

you can put your code into an existing excel object event, or call some other procedure from an existing event, or add some sort of button or other control, either as a toolbar (ribbon) button, or activex button on a worksheet, then use the click event of that button, else just run the procedure within the IDE

use ADO to insert them

while ADO does work with excel it can sometimes cause problems, you could probably insert rows without ADO, though you have not specified if the inserts are into excel or some other database

see http://support.microsoft.com/kb/257819 for help with ADO connection strings and other things to watch out for

Depending on if a column contains a value or not

you can probably create a recordset, using SQL, containing just the rows that contain the value, instead of looping all the rows

[MMock]

you have not specified if the inserts are into excel or some other database

I meant to title the post "...and generate SQL Server Insert statements". Sorry about that.

I also don't believe I was clear explaining what my Excel file means. I have a whole "section" called Fabrication/Season. Every possible fabric is listed across columns. So if the column Wool has a value in it, I have to create a record for Wool, and the season - spring and summer and/or fall and winter. Then I have a column for Cotton. And so on for every fabric you can imagine. So if my one Excel record indicates Wool, S/S, F/W and Cotton S/S, F/W that is four records that need to get created in the database.

[westconn1]

I meant to title the post "...and generate SQL Server Insert statements". Sorry about that.

no real difference to the code, some minor differences to sql statements, different connection strings

check out connectionstrings.com for correct sql server connection string

[Zvoni]

Just to understand you correctly:
You have an Excel-File with two rows (!)
First row being the "Header" (containing the fabrics across columns), and the second row with the seasons (s/s - f/w)

I'd rather transpose this sheet to have two columns with the seasons, and in the rows the fabrics, but since it's a one-shot-thing it's basically just a loop across the columns (resp. rows if you transpose it) with a nested If-then-Else checking if there's an entry in the cell.

As for your entry-point question: I'd be careful with putting such code into an Excel-Object since you said you're not familiar with VBA.
Just insert a normal code-module in your project-explorer, write "Public Sub MyEntryPoint" as the first line, and the editor will create the body of the function.
By using a normal code-module you can assign it to a button, ribbon (whatever) as pete pointed out

Public Sub MyEntryPoint()
'Dim Your Variables
'Here your Loop
End Sub

As for your SQL-Statements: Why not write the SQL's on the server-side with Parameters?

[MMock]

My Excel file has 1280 rows. I am processing row #'s 6 - 909. The file has columns going out to DJ. Fabric and season are paired up, so for example columns AS and AT are Canvas, AS being S/S Canvas and AT being F/W Canvas. And so on through Wool.

I believe I've answered my own entry-point question. In said Excel file if I click Macros, it lists the procedures in my module and I can select main().

So I am at the point where I've processed my Excel file and I have an array of values to be inserted into my sql table. These are strings and are the second half of the INSERT statement, since the first half is fixed (insert into table (col1, col2, etc)). Now I want to iterate through my array and execute the inserts. I guess the best way is a bulk insert. Do I have to do some other processing on the array, like write it out to a file, in order to easily do a bulk insert?

Thanks.

[Zvoni]

As for your bulk insert: i don't see any need to write it out into a file.

The question remains: Do you want to build/concatenate your SQL-String (fixed part and variable part) on the fly through code?
Going through the array by loop it's easier to do each insert for itself, but that may take longer, on the other hand it seems easier to catch any errors.
It's your decision, but since it's a one-shot operation i'd rather do each insert/value for itself, and not by bulk

[MMock]

You are absolutely right. It was designed all along to just iterate through the final array. If it takes an hour to execute no one will care. It'll be me using it, not a real user.

Thanks.

[MMock]

To post the rest of the solution, here is my code that inserts from the array I built. The id in the table is not auto-increment, so I do that here.

Sub RunDbInserts()
    
    Dim iId As Integer
    Dim i As Integer
    
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Set cn = New ADODB.Connection
    Set cmd = New ADODB.Command

    Dim sConnString As String
    'Create connection string
    sConnString = "Provider=sqloledb; Server=svr; Database=db; Integrated Security=SSPI;"
    'Open connection and execute
    cn.Open sConnString
    
    iId = 4656

    With cmd
      .ActiveConnection = cn
      .CommandType = adCmdText
      ' .CommandText = "INSERT INTO TBL (col1, col2) VALUES ('val', 'val');"
      For i = 0 To idxInserts - 1
        .CommandText = sqlInsert & Replace(aInsertData(i), "sId", CStr(iId))
        iId = iId + 1
        .Execute
      Next i
    End With

End Sub

Thanks for the help guiding me through this.