Announcement

Collapse
No announcement yet.

Extract data from Excel for MySQL

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract data from Excel for MySQL

    Greetings all,
    I am looking for some help. I have a lot of data in an Excel Spreadsheet regarding my the servers that I manage. It gets there from an automated process that i run about once a month. It provides sort of a Dashboard for my boss on the servers that i am currently managing.

    For my personal use, to make things easier for me, I want to extract portions of this data for import into a MySQL DB that i maintain.

    I have the basic info that will need to be prefaced on each line of the SQL script such as:
    Code:
     INSERT INTO 'servers' ('Server_Name'......) VALUES ('THIS IS WHAT I WANT TO GENERATE')...
    As you can see, I need to know how to get the data out of Excel. I don't need all of the fields, but i have the names of the fields that i need.

    Can someone point me in the right direction?

    Thanks much

    app

  • #2
    Re: Extract data from Excel for MySQL

    Roughly in VB Script:

    Set oXL = Wscript.CreateObject("Excel.Application")
    oXL.Visible = True
    oXL.DisplayAlerts = False
    Set oBook = oXL.Workbooks.Open("C:\Scripts\Checklist.xls")
    Set oWS = oXL.ActiveWorkbook.Worksheets(1)
    For intRow = 1 to <max number of rows>
    Set strSQL = "INSERT INTO 'servers' (<list of fields>) VALUES ("
    strSQL = strSQL & oWS.Cells(intRow,1) & ", " 'get value in Col A
    strSQL = strSQL & oWS.Cells(intRow,2) & ", " 'get value in Col B
    etc....
    strSQL = strSQL & ")"

    then open a dB connection and send strSQL

    Next intRow
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Extract data from Excel for MySQL

      Originally posted by apperrault View Post
      Greetings all,
      I am looking for some help. I have a lot of data in an Excel Spreadsheet regarding my the servers that I manage. It gets there from an automated process that i run about once a month. It provides sort of a Dashboard for my boss on the servers that i am currently managing.

      For my personal use, to make things easier for me, I want to extract portions of this data for import into a MySQL DB that i maintain.

      I have the basic info that will need to be prefaced on each line of the SQL script such as:
      Code:
       INSERT INTO 'servers' ('Server_Name'......) VALUES ('THIS IS WHAT I WANT TO GENERATE')...
      As you can see, I need to know how to get the data out of Excel. I don't need all of the fields, but i have the names of the fields that i need.

      Can someone point me in the right direction?

      Thanks much

      app

      From where does this info come, in other words, from where and how does the data get in to the excel doc, maybe you could create an second automated process which writes the data directly to the database instead of first to excel and from there to the database.

      Or you could create an ODBC database connection on youre comp and use VBA in excel to connect to the database and write the records to the database

      And finally you could write an stored procedure in mySql that reads the data from the excel sheet.

      Comment


      • #4
        Re: Extract data from Excel for MySQL

        Originally posted by Ossian View Post
        Roughly in VB Script:

        Set oXL = Wscript.CreateObject("Excel.Application")
        oXL.Visible = True
        oXL.DisplayAlerts = False
        Set oBook = oXL.Workbooks.Open("C:\Scripts\Checklist.xls")
        Set oWS = oXL.ActiveWorkbook.Worksheets(1)
        For intRow = 1 to <max number of rows>
        Set strSQL = "INSERT INTO 'servers' (<list of fields>) VALUES ("
        strSQL = strSQL & oWS.Cells(intRow,1) & ", " 'get value in Col A
        strSQL = strSQL & oWS.Cells(intRow,2) & ", " 'get value in Col B
        etc....
        strSQL = strSQL & ")"

        then open a dB connection and send strSQL

        Next intRow
        This method will probably fail if there are any duplicate records, for this to work properly and fail safe, you need to write an Stored procedure in the MySQL db which writes the records to the DB an also handels error's (like duplicate records).

        If duplicate records are an must and can't be avoided, create an extra collumn in the MySQL table in which the stored procedure writes an unique id, that way you can still index the table and don't have to worry about duplicate records.

        Comment

        Working...
        X