Results 1 to 9 of 9

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

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Working from home, social distancing, just like you
    Posts
    4,179

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

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,963

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

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Working from home, social distancing, just like you
    Posts
    4,179

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

    Quote Originally Posted by westconn1 View Post
    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,963

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

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,233

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

    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
    Code:
    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?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Working from home, social distancing, just like you
    Posts
    4,179

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

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,233

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

    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
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Working from home, social distancing, just like you
    Posts
    4,179

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

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  9. #9

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Working from home, social distancing, just like you
    Posts
    4,179

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

    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.

    Code:
    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width