• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

Inserting data into mySQL Server

Hi,

I'm a newbie at developing databases. I do have some experience with VBA.

Project description:
-----------------------------------------
We have a workbook which contains spreadsheets named after each project and each sheet contains data about the tests performed in the laboratory on samples.

Its a flat table containing all the data we have.

I have created a relational database with multiple tables using MySQL Workbench. Its empty and we need to populate this database with the data contained in the spreadsheet line by line.

Later we will be developing a web application possibly using PHP which will show the dashboard to view this data.

I need guidance to go in the right direction - connection from VBA to MySQL, SQL script to add data line by line etc

Any kind of help will be very much appreciated. If needed I can provide the SQLScript generated from MySQL Workbench and the Spreadsheet

System Description:
-------------------------------------------
Windows 7 Ultimate 64bit
Office 2010 Professional Plus 32bit
MySQL 5.5 32bit
MySQL ODBC Connector 5.1


Regards,
salamay
0
salamay
Asked:
salamay
  • 12
  • 6
  • 5
  • +1
3 Solutions
 
Hugh McCurdyCommented:
I would read this page (and the following pages) if you haven't done so thus far.    http://www.w3schools.com/php/php_mysql_intro.asp
0
 
salamayAuthor Commented:
Thanks hmccurdy!
For now I want to start with VBA and then later work on web development with php.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Ray PaseurCommented:
Can you please post an example of the workbooks you are using?  Thanks, ~Ray
0
 
leonstrykerCommented:
hmccurdy - nice link. Although I would not bother with a Recordset object at all and do this instead

        For rowCursor = 2 To 11
            strSQL = "INSERT INTO tutorial (author, title, price) " & _
                    "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
                    "'" & esc(.Cells(rowCursor, 2)) & "', " & _
                    esc(.Cells(rowCursor, 3)) & ")"
            oConn.Execute strSQL
        Next

Add this one http://carlprothman.net/Default.aspx?tabid=81, and its all anyone should really need to insert data into any database.

Leon
0
 
salamayAuthor Commented:
Thanks all,

http://www.heritage-tech.net/908/inserting-data-into-mysql-from-excel-using-vba/
did the job, although i'm a little confused.

I have attached the macrobook and the sql script to create the db. What i want to do is everytime when the user updates a project by adding rows, which relate to tests performed on samples, the db should be updated by a macro. but if the insertdata subroutine is run everytime data is added to excel, it creates duplicate entries in the table which is not what i want. Maybe I should first query the db and somehow identify what has already been populated and whats not. How can I do that?

Secondly does it make sense to add the data to the db line by line for each project sheet?
The user doesnt like the idea of dropdowns and multiple interactions and wants this data to be inserted in the db with least interaction.

Master-Results.xlsm
scalSQLscript.sql
0
 
Hugh McCurdyCommented:
MySQL has a replace option that can avoid duplicates as long as you have a primary key or one of the fields is marked UNIQUE.

More information at   http://dev.mysql.com/doc/refman/5.0/en/replace.html
0
 
salamayAuthor Commented:
Ok so that works

Regarding inserting all the data. Should I add the data line by line?
or first create Arrays with unique names and then add them to the tables?
0
 
Hugh McCurdyCommented:
I don't know which is better.  How important is speed of operation vs speed of development?  

In any event, I'll defer the optimization question to someone who actually knows that answer.
0
 
salamayAuthor Commented:
right now speed of development is most important
 
I was trying to implement this code to add records line by line but there is something wrong with my rs.open statement and i get an automation error at line 22

should i get rid of adOpenDynamic

For Each sht In Sheets
                If sht.Index > 1 Then
                    'Populate Projects
                    strSQL = "REPLACE INTO projects (project_id, project_name) " & _
                            "VALUES ('" & esc((sht.Index - 1)) & "', '" & esc((sht.Name)) & "') "
                    rs.Open strSQL, oConn, adOpenDynamic
                    
                    lastRow = sht.Range("A10").End(xlDown).Row
                    
                    'Push data in the current sheet
                    For rowno = 10 To lastRow
                    
                        testType = sht.Cells(rowno, 18)
                        
                        Select Case testType
                        
                            Case "Triax"
                                'Populate Wells
                                strSQL = "REPLACE INTO projects (well_name, projects_project_id) " & _
                                    "VALUES ('" & esc(sht.Cells(rowno, 4)) & "',  " & _
                                    esc((sht.Index - 1)) & ")"
                                rs.Open strSQL, oConn, adOpenDynamic
                            
                            Case "USS"
                            
                            Case "-"
                                'Do Nothing
                            Case ""
                                'Do Nothing
                        End Select
                        
                    Next

Open in new window

0
 
salamayAuthor Commented:
Is creating multiple recordsets the answer?

such as
rsProjects.open  &
rsWells.open
?

I still get an error doing so
0
 
Hugh McCurdyCommented:
I'm not familiar with that language so my ability to help with it is very limited.

Is there an rs.Close function?  If so, maybe it will help.  Otherwise, you'll need someone who knows that language.
0
 
Hugh McCurdyCommented:
Do you get a specific error message?  Knowing what it says can help.
0
 
salamayAuthor Commented:
run-time error -2147217887(80040e21)
Automation error
0
 
leonstrykerCommented:
REPLACE is not a recognized word. You will need to use INSERT. As I mentioned earlier, you do not need to use RecordSet object to insert lines, but rather use the connection object instead.

BTW, lastRow = sht.Range("A10").End(xlDown).Row is not the best way to find the last record on your sheet. It will fail if there is a blank cell in column A at some point, or if the column is blank after row 10, it will return the last row of the sheet and you will be insert blank records a million times or so.

For Each sht In Sheets
                If sht.Index > 1 Then
                    'Populate Projects
                    strSQL = "INSERT INTO projects (project_id, project_name) " & _
                            "VALUES ('" & esc((sht.Index - 1)) & "', '" & esc((sht.Name)) & "') "
                    cnConn.Execute strSQL
                   
                    lastRow = sht.Range("A10").End(xlDown).Row
                   
                    'Push data in the current sheet
                    For rowno = 10 To lastRow
                        testType = sht.Cells(rowno, 18)
                        Select Case testType
                            Case "Triax"
                                'Populate Wells
                                strSQL = "INSERT INTO projects (well_name, projects_project_id) " & _
                                    "VALUES ('" & esc(sht.Cells(rowno, 4)) & "',  " & _
                                    esc((sht.Index - 1)) & ")"
                                cnConn.Execute strSQL
                           
                            Case "USS"
                           
                            Case "-"
                                'Do Nothing
                            Case ""
                                'Do Nothing
                        End Select
                       
                    Next
0
 
leonstrykerCommented:
>Secondly does it make sense to add the data to the db line by line for each project sheet?

Depends on the number of records. There are several options for BULK INSERTS, but if youa re talking about inserting a few hundred records they are not worth the effort. One of the advantage of doing single record inserts is that you can validate each record before saving it to a database.

Leon
0
 
salamayAuthor Commented:
my bad
line 22 should contain wells as table and not projects.

that is good but the when the wells table is populated and the main for loop moves to nxt sht i get an overflow error

runtime error '6'
overflow
0
 
salamayAuthor Commented:
leon

1. I have replaced the RELACE statement with INSERT IGNORE as per your comment and it actually give me what i want.

2. Whats the alternative to lastRow = sht.Range("A10").End(xlDown).Row

3. Each project might have about hundred sample records so for now line by line is probably the easiest option for me.

The following code works for the first sheet but when it moves to the next I dont know why I'm getting an overflow!

Thanks
0
 
salamayAuthor Commented:

For Each sht In Sheets
                If sht.Index > 1 Then
                    'Populate Projects
                    strSQL = "INSERT IGNORE INTO projects (project_id, project_name) " & _
                            "VALUES ('" & esc((sht.Index - 1)) & "', '" & esc((sht.Name)) & "')"
                    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
                
                    lastRow = sht.Range("A10").End(xlDown).Row
                    'Push data in the current sheet
                    For rowno = 10 To lastRow
                    
                        testType = sht.Cells(rowno, 18)
                        
                        Select Case testType
                        
                            Case "Triax"
                            
                                'Populate Wells
                                strSQL = "INSERT IGNORE INTO wells (well_name, projects_project_id) " & _
                                    "VALUES ('" & esc(sht.Cells(rowno, 4)) & "',  " & _
                                    esc((sht.Index - 1)) & ")"
                                rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
                                
                                'Populate Cores
                                'strSQL = "INSERT IGNORE INTO cores (well_name, projects_project_id) " & _
                                    "VALUES ('" & esc(sht.Cells(rowno, 4)) & "',  " & _
                                    esc((sht.Index - 1)) & ")"
                                'rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
                            
                            Case "USS"
                            
                            Case "-"
                                'Do Nothing
                            Case ""
                                'Do Nothing
                        End Select
                        
                    Next
                    'Create Array of Cores
                End If
            Next sht

Open in new window

0
 
leonstrykerCommented:
You can use the attached function to retrieve the last cell in a worksheet. In regard to your error, out put the strSQL statement into the Immediate window ( or you can use Debug.Print strSQL to do the same thing before you save to the database.

and i still think you should do

oConn.Execute strSQL

instead of

rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

Leon

Public Function TrueLastCell(Optional ByVal sSheet As String, _
                    Optional ByVal sBook As String) As Range
Dim oSheet As Worksheet
Dim oLastRow As Range
Dim oLastColumn As Range
    On Error GoTo ErrorHandler
    '/ set worksheet object
    sBook = IIf(sBook = "", ThisWorkbook.Name, sBook)
    sSheet = IIf(sSheet = "", ActiveSheet.Name, sSheet)
    Set oSheet = Workbooks(sBook).Worksheets(sSheet)
    '/ find the last row and last column
    Set oLastRow = oSheet.Cells.Find("*", oSheet.Cells(1048576, 16384), _
     xlFormulas, xlPart, xlByRows, xlPrevious, False)
    Set oLastColumn = oSheet.Cells.Find("*", oSheet.Cells(1048576, 16384), _
     xlFormulas, xlPart, xlByColumns, xlPrevious, False)
    '/ return the address
    Set TrueLastCell = oSheet.Cells(oLastRow.Row, oLastColumn.Column)
    GoTo Cleanup
    Exit Function
ErrorHandler:
    '/ nothing found on the sheet (it is completely blank)
    Set TrueLastCell = Range("$A$1")
Cleanup:
    Set oLastColumn = Nothing
    Set oLastRow = Nothing
    Set oSheet = Nothing
End Function

Open in new window

0
 
salamayAuthor Commented:
Thanks Leon, oConn.Execute strSQL helps me debug query errors unlike rs.open
Very much Appreciated.

Since I'm adding information line by line and using INSERT IGNORE to avoid duplicates, my primary keys are jumping forward. something like

1
3
7
6
9

and so on....

I'm not sure if that's what I want. If there are 10 projects and 10 wells no matter how i add them they should be 1 to 10. Should i programatically update these keys using VBA? Or is there a Query command which can do that?
 



0
 
leonstrykerCommented:
> my primary keys are jumping forward

How is your primary key defined? Why do you care if it jumps?
0
 
salamayAuthor Commented:
1.The Project table has a column named project_id which is defined as a primary key, not null and auto increment.

2.Im not sure maybe as im not thinking as a database designer. So if it doesnt matter, adding this id in another table as a foriegn key would just be fine?
0
 
salamayAuthor Commented:
sorry missed it

Leon's comment on 10/14/11 12:46 PM, ID: 36970777 was the one i meant to accept as a solution
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 12
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now