Question

  • Creator
    Topic
  • #2150524

    MS Excel- Can this be done

    Locked

    by jiminpa ·

    Ok gang I’ve got a puzzler for you.

    I was assigned a project and was given an excel spreadsheet with some data on it which I need to convert to a database and web enable it. This part is no problem.

    Here’s the problem. The one column of the spread sheet contain multiple two character codes separated by a forward slash.

    eg. AB/CD/EF

    When I import this spreadsheet into the database I only want one code per record

    eg. AB
    CD
    EF
    otherwise the reports we run against the database will not be acurate. I know I can do a manual cut and paste row by row and make my edits, the question is, is there some automated process I can use to do this for me?

    This will knock days off the project. Help? Thanks.

    Jim

All Answers

  • Author
    Replies
    • #2926246

      Clarifications

      by jiminpa ·

      In reply to MS Excel- Can this be done

      Clarifications

    • #2926239

      It depends

      by router boy ·

      In reply to MS Excel- Can this be done

      It depends on exactly how you are going about getting the data into the DB. If you use the data import function in access you might be able to tweak the custom delimeter function under Get external data -> Import -> advanced options. If this is not option you can script the data import process to automatically seperate the data but that might take a little longer to accomplish.

      • #2926227

        Access is an option

        by jiminpa ·

        In reply to It depends

        but I don’t see advanced options. I stepped through the whole import process but couldn’t find where to tweak my column delimiters. Also, if I was able to make this change would that clone the data in my other fields within that row? That’s going to prove important through this process.

        • #2926216

          Advanced Options

          by router boy ·

          In reply to Access is an option

          Is located under File -> Get External Data at this point you browse to the file you are looking to import. Once the file is selected you will have a an advanced button. You can walk through the process of massaging the data to get the desired output and save the process similar to a macro. You can use a little VB to call the operation each time.

          As for the other option you could use a little code to accomplish that and its fairly simple to write.

    • #2926222

      Script it

      by ic-it ·

      In reply to MS Excel- Can this be done

      use VB or VBA to open the workbook, copy the contents to a new workbook, add a few columns and then extract the 3 sets (from the one cell) into the original and 2 new columns.
      Here are a few (partial 🙁 ) examples to get you started.
      Someone out there could probably knock the script out in minutes (I would need days 😉 ).

      http://preview.tinyurl.com/63uvyb
      http://www.themssforum.com/Excel/copy-part-138086/

    • #2925718

      If you want to split the multiple values into separate columns

      by thumbsup2 ·

      In reply to MS Excel- Can this be done

      You can use the Text to Column feature using the forward slash as the delimiter. Then, each row (record) would have one column for each code. Once the data is separated, import the spreadsheet.

    • #2925621

      Scripted!

      by bizzo ·

      In reply to MS Excel- Can this be done

      Explaination of script, as you will need to amend it:

      Assumptions:
      (1) Only 10 columns are used, if more or less are required, add or removed lines like (lines 26-35)
      strColX = Trim(objWorkSheet…)
      (2) The column with the AA/BB/CC/… is column A. If it’s any other column, then amend the line (Line 37)
      MultiCell = Split(strColA, “/”, -1)
      (3) The input file is called “input.xls”, change line 1
      (4) Output is output.csv and you need to delete the file before running the script as the script will append to the output file.
      (5) Not much in the way of error checking here!

      What it does:
      Takes the spreadsheet and read in each cell on the row.
      Splits column A delimited by ‘/’.
      Writes a line to a CSV file duplicating the other cells whilst going through each of the codes in col A.
      The CSV file can then be loaded into Excel.
      Ta-Da!! Hopefully it will show what you want.

      Here’s the script:

      ——————————————

      strFileNameIn = “input.xls”
      StrFileNameOut = “output.csv”

      Dim MultiCell
      On Error Resume Next

      Set objFSO = CreateObject(“Scripting.FileSystemObject”)
      strCSVFileIn = objFSO.getAbsolutePathName(“.”) & “\” & strFileNameIn
      strCSVFileOut = objFSO.GetAbsolutePathName(“.”) & “\” & strFileNameout

      If objFSO.FileExists(strCSVFileIn) Then
      Set objExcel = CreateObject(“Excel.Application”)
      objExcel.DisplayAlerts = False
      objExcel.Workbooks.Open(strCSVFileIn)
      intWorkSheets = objExcel.ActiveWorkbook.Sheets.Count
      intObjCount = 0

      For i = 1 To intWorkSheets
      Set objWorkSheet = objExcel.ActiveWorkbook.Worksheets(i)

      intRow = 1 ‘Starting Row

      Set objFSO = CreateObject(“Scripting.FileSystemObject”)

      Do until objWorkSheet.Cells(intRow,1).value = “”
      strColA = Trim(objWorkSheet.Cells(intRow,1).Value)
      strColB = Trim(objWorkSheet.Cells(intRow,2).Value)
      strColC = Trim(objWorkSheet.Cells(intRow,3).Value)
      strColD = Trim(objWorkSheet.Cells(intRow,4).Value)
      strColE = Trim(objWorkSheet.Cells(intRow,5).Value)
      strColF = Trim(objWorkSheet.Cells(intRow,6).Value)
      strColG = Trim(objWorkSheet.Cells(intRow,7).Value)
      strColH = Trim(objWorkSheet.Cells(intRow,8).Value)
      strColI = Trim(objWorkSheet.Cells(intRow,9).Value)
      strColJ = Trim(objWorkSheet.Cells(intRow,10).Value)

      MultiCell = Split(strColA, “/”, -1)

      For j = LBound(MultiCell) To UBound(MultiCell)
      Set objOutStream = objFSO.OpenTextFile(strCSVFileOut,8,True)
      Err.Clear
      objOutStream.WriteLine(MultiCell(j) & “,” & strColB & “,” & strColC & “,” & strColD & “,” & strColE & “,” & strColF & “,” & strColG & “,” & strColH & “,” & strColI & “,” & strColJ)
      Next

      Set objOutStream = Nothing
      intRow = intRow + 1
      intObjCount = intObjCount + 1
      Loop
      Next
      Set objWorkBook = Nothing
      Set objWorkSheet = Nothing
      objExcel.Quit
      WScript.Echo “All Done”
      Else
      WScript.Echo strCSVFileIn & ” does not exist.”
      End If

      • #2925915

        Wow!

        by jiminpa ·

        In reply to Scripted!

        That’s more than I could have ever asked for. Thanks! I have written some simple scripts but nothing that complex. That would have taken me months to come up with. I’ll play around with it and tweak it to get what I need out of it.

        You are a gentleman and a scholar. Thanks again.

    • #2926018

      VBA

      by kiran.maan ·

      In reply to MS Excel- Can this be done

      Best solution of such requirement is VBA.

      Try your hands with VB Application queries. Definitely you will win.

Viewing 5 reply threads