Help with cleaning data before passing to mySQL

Author Message
DaveA

  • Total Posts : 3
  • Scores: 0
  • Reward points : 0
  • Joined: 1/25/2012
  • Status: offline
Help with cleaning data before passing to mySQL Wednesday, January 25, 2012 12:25 AM (permalink)
0
[Helpful answer received] / [List Solutions Only]
Hello all,

I'm having some issues cleaning data before passing to mySQL in excel.  Basically, i need to replace 4 characters with a value.  I need # to be 4, < to be 3, > 2 and * to be 1.

I've managed to fudge my way to creating a form that submits from excel to mySQL (with lots of googling), but as such my VB skills are poor, sorry!

I have this function in there to clean the spaces, but if I try and modify it to check for 4 values, it will duplicate the data 4 times into the same table field, once for each pass of checking it seems.  I tried multiple replace without the trim command (as i believe this is used mainly for cleaning the spaces),

Function esc(txt As String)    esc = Trim(Replace(txt, "'", "\'"))

Any idea on what I'd use to clean this data so the strSQL it passes doesn't duplicate data?  Can you replace multiple characters in a single statement?

This is how the esc function is applied ('" & esc(.Cells(rowCursor, 1)) etc etc, through 48 fields.
<message edited by DaveA on Wednesday, January 25, 2012 12:34 AM>
 
#1
    Wakawaka

    • Total Posts : 456
    • Scores: 23
    • Reward points : 0
    • Joined: 8/27/2009
    • Status: offline
    Re:Help with cleaning data before passing to mySQL Wednesday, January 25, 2012 1:00 AM (permalink)
    0
    You could use the "Replace" method.  This method allows you to specify a string, the string to search for, and the string to replace it with.  The method will then return the new string.
     
    #2
      DaveA

      • Total Posts : 3
      • Scores: 0
      • Reward points : 0
      • Joined: 1/25/2012
      • Status: offline
      Re:Help with cleaning data before passing to mySQL Wednesday, January 25, 2012 1:23 AM (permalink)
      0
      Hello,
       
      I tried this (maybe not correctly though)
       
      replace (txt, "#", "4") works for the one character.
       
      When I try it with a single line replace on a cell containing '#', it will return the data '4'
       
      Replace ((txt, "#", "4"),(txt, "<", "3"),(txt, ">", "2"),(txt, "*", "1"))
      This didn't work as i'd hoped.
       
      esc = Replace (txt, "#", "4") & _
      Replace (txt, "<", "3") & _
      Replace (txt, ">", "2") & _
      Replace (txt, "*", "1")
       
      when i try it with multiple replace lines, it will return '4###' (via MsgBox strSQL) against the same data, one entry for each replace.
       
      As I'm guessing formatting (sorry again...) there's something I'm missing here.  Any ideas?
       
      #3
        59cobalt

        • Total Posts : 972
        • Scores: 91
        • Reward points : 0
        • Joined: 7/17/2011
        • Status: online
        Re:Help with cleaning data before passing to mySQL Wednesday, January 25, 2012 2:14 AM (permalink)
        5
        [This post was marked as helpful]
        DaveA
        When I try it with a single line replace on a cell containing '#', it will return the data '4'

        Replace ((txt, "#", "4"),(txt, "<", "3"),(txt, ">", "2"),(txt, "*", "1"))
        This didn't work as i'd hoped.
        It certainly didn't, because that's obviously not the correct way to use the Replace() function.

        DaveA
        esc = Replace (txt, "#", "4") & _
        Replace (txt, "<", "3") & _
        Replace (txt, ">", "2") & _
        Replace (txt, "*", "1")

        when i try it with multiple replace lines, it will return '4###' (via MsgBox strSQL) against the same data, one entry for each replace.
        Sure it does, because your instruction concatenates the results of 4 different replacement operations on the same source string.

        While it's correct that you have to make multiple replacements, you need to do them sequentially, with each operation working on the results of the previous one. Like this: 
        esc = Replace(Replace(Replace(Replace(txt, "#", "4"), "<", "3"), ">", "2"), "*", "1")
        Or (more readable) like this: 
        esc = Replace(txt, "#", "4")
esc = Replace(esc, "<", "3")
esc = Replace(esc, ">", "2")
esc = Replace(esc, "*", "1")

        <message edited by 59cobalt on Wednesday, January 25, 2012 2:17 AM>
         
        #4
          DaveA

          • Total Posts : 3
          • Scores: 0
          • Reward points : 0
          • Joined: 1/25/2012
          • Status: offline
          Re:Help with cleaning data before passing to mySQL Wednesday, January 25, 2012 2:31 AM (permalink)
          0
          Thank you 59cobalt for entertaining one of us annoying DIYers out of our depth/area of expertise, I know it must be frustrating looking at some of the things we attempt, but your help has been invaluable in this.  It's all working perfectly!
           
          I didn't want to attempt doing esc = 4 times, as I thought it would cause the problem I actually experienced, how ironic!

          Thanks again for taking the time to answer me.
          <message edited by DaveA on Wednesday, January 25, 2012 2:32 AM>
           
          #5
            59cobalt

            • Total Posts : 972
            • Scores: 91
            • Reward points : 0
            • Joined: 7/17/2011
            • Status: online
            Re:Help with cleaning data before passing to mySQL Wednesday, January 25, 2012 3:38 AM (permalink)
            0
            You're welcome.
             
            #6

              Online Bookmarks Sharing: Share/Bookmark

              Jump to:

              Current active users

              There are 0 members and 1 guests.

              Icon Legend and Permission

              • New Messages
              • No New Messages
              • Hot Topic w/ New Messages
              • Hot Topic w/o New Messages
              • Locked w/ New Messages
              • Locked w/o New Messages
              • Read Message
              • Post New Thread
              • Reply to message
              • Post New Poll
              • Submit Vote
              • Post reward post
              • Delete my own posts
              • Delete my own threads
              • Rate post

              2000-2012 ASPPlayground.NET Forum Version 3.9