Storage

Ask something
Question Locked

MS Excel- Can this be done

0 helpful
14 replies

by jiminpa

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

14 replies

by router boy

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.

by jiminpa

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.

by router boy

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.

by thumbsup2

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.

by Member 22873932

I was hoping someone would mention this, it is much easier this way, you just need to know that the tool exist:)

by thumbsup2

... especially to prepare spreadsheet data to be imported into databases. :D

by Member 22873932

I was hoping someone would mention this, it is much easier this way, you just need to know that the tool exist:)

by jiminpa

Text to Column is pretty close to what I want. The big thing I need to be able to do is take the contents of the A column and if it has data delimited by a / to spilt it off into a separate row and bring a copy of the data in column B-S with it.

by thumbsup2

But, you could certainly use Text to Column to split the data out, then insert a blank row below the one you need, highlight and copy down (ctrl+D). You'll need to move some of the data that has been split out over to the appropriate column for each row added and eventually get it all to where you could delete the extra columns.

by bizzo

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

by jiminpa

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.

by kiran.maan

Best solution of such requirement is VBA.

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

This discussion is closed to new replies.