Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

Split columns that have delimiters in either excel or access

Hi all,  I have a problem where I have a csv file that has data separated by semi-colons.  Easy to import into excel or access and split you may think.  However, my data is formatted as follows:-

0100     Resident;Resident2     Title;Title2

Where the spaces (TAB) is the column delimiter and ; is the delimiter within the next column.  Finding it difficult to explain but I have a line where the first column contains the reference number for that property, but then the second column contains TWO Names split by the semi-colon, the third column contains those people's Title's and so on.  There are always the same amount of entries in the remaining columns as in the second column, so every resident will have a title etc, however the FIRST column only has the ONE reference number entry!!

I want to split/view the data so this it would read as follows:-

0100     Resident     Title
0100     Resident2   Title2

Hope this makes sense!  And hope it's possible!  Have attached sample CSV file.

I'm  no programmer but am pretty good with excel/access if someone could point me in the right direction!

Cheers
Test.csv
0
howsonhome
Asked:
howsonhome
  • 8
  • 3
  • 3
1 Solution
 
patrickabCommented:
howsonhome,

In Excel, and in the File Open dialogue box select Text as the file type, find the file and then yYou need to use different text file import settings. Choose the 'delimited' option, press 'Next' and select 'commas' and 'semi-colon' as the delimiters, press OK. That will separate the data into sensible columns - see attached file.

Patrick


Test-csv-01.xls
0
 
howsonhomeAuthor Commented:
Hi Sorry that's not what is required.  I'm finding it difficult to make clear what I need so have done a manual version in an excel spreadsheet.  The first sheet is the ORIGINAL data, (the second sheet is where I can TRANSPOSE data into columns but not sure if this is any use anyway), and the last sheet is actually what I REQUIRE which I have just typed in manually.  So I'm trying to get the ORIGINAL sheet to look like the REQUIRED sheet without having to do it manually as I actually have around 16,000 records!

Many thanks.
Test2.xls
0
 
Rory ArchibaldCommented:
Try this - run LoadFile2
Sub LoadFile2()
    Const ForReading As Integer = 1
    Dim varFileName, avarData, varData
    Dim lngRow As Long, lngColumnCount As Long, lngIndex As Long, lngRecCount As Long
    Dim x As Long, y As Long
    Dim fso As Object, tsrStream1 As Object
    Dim shtNew As Worksheet
    
    Set shtNew = ActiveWorkbook.Sheets.Add
    
    varFileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Select source file")
    
    If TypeName(varFileName) = "Boolean" Then Exit Sub
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    lngRow = 1
    Set tsrStream1 = fso.OpenTextFile(varFileName, ForReading, False)
    avarData = Split(tsrStream1.ReadAll, vbCrLf)
    tsrStream1.Close
    Application.ScreenUpdating = False
    For lngIndex = LBound(avarData) To UBound(avarData)
        If Len(avarData(lngIndex)) > 0 Then
            varData = GetRowData(CStr(avarData(lngIndex)))
            lngRecCount = UBound(varData, 1)
            lngColumnCount = UBound(varData, 2)
            shtNew.Cells(lngRow, "A").Resize(lngRecCount, lngColumnCount).Value = varData
            lngRow = lngRow + lngRecCount
        End If
    Next lngIndex
    Application.ScreenUpdating = True
End Sub
Function GetRowData(strIn As String) As Variant
    Dim varData, varRecs
    Dim avarOutput()
    Dim lngRecCount As Long
    Dim x As Long, y As Long
    
    varData = Split(strIn, ",")
    ' get rec count from second field
    varRecs = Split(varData(1), ";")
    lngRecCount = 0
    For x = LBound(varRecs) To UBound(varRecs)
        If Len(varRecs(x)) > 0 Then lngRecCount = lngRecCount + 1
    Next x
    If lngRecCount = 0 Then lngRecCount = 1
    ReDim avarOutput(1 To lngRecCount, 1 To UBound(varData) + 1)
    
    ' now iterate the row data
    For x = LBound(varData) To UBound(varData)
        If InStr(varData(x), ";") > 0 Then
            varRecs = Split(varData(x), ";")
            For y = 1 To lngRecCount
                avarOutput(y, x + 1) = varRecs(y - 1)
            Next y
        Else
            For y = 1 To lngRecCount
                avarOutput(y, x + 1) = varData(x)
            Next y
        End If
    Next x
    GetRowData = avarOutput
End Function

Open in new window

0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
patrickabCommented:
howsonhome,

OK, I understand except that the text file that you provided did not have all that data in it. Can you upload a text file with several sets of data - as shown in the Excel file. If each line has the same number of pieces of data, even if some are blank, we should be able to parse the data and make sense of it. So over to you for that new text file.

Patrick
0
 
patrickabCommented:
Seems rorya has got there in one go. I'll bow out at this stage...
0
 
howsonhomeAuthor Commented:
Hi Rorya, thanks for that, it works against the sample data I uploaded, but not against my full file.  I'll attach a copy of an entire record for you to see all the headings as it is slightly different!

the file Truefile.csv is one where I export the data from a progress database into a csv file.  It has  a delimiter of "}".  If you can amend the code so it does it straight from this then that would be fab!

the Amendedfile.csv is the one I do a bit of work on to repeat the TENANCYREF 8 times in that column and have already split the columns by the "}" delimiter.  It's a drag having to do this beforehand but if it's easier to amend the code using this format than that's great too!

Hope this is clear!

There are nearly 16,000 rows in the original file but as the data is sensitive I can't send you all the rows.  All the columns and headings are there now and I have amended the data to two rows so I'm not breaking any laws!
Many thanks!

Sorry but the data is very sensitive which is why I've been giving examples.
Truefile.csv
Amendedfile.csv
0
 
Rory ArchibaldCommented:
Just change the fifth line in GetRowData from this:
    varData = Split(strIn, ",")


to this:
    varData = Split(strIn, "}")


Regards,
Rory
0
 
howsonhomeAuthor Commented:
I get a 'Subscript out of Range' error on the 16,000 record file but it works on those examples so am going to presume there's an error in the data somewhere in the large file, will take a look!

Apart from that YOU'RE A GENIUS!!

Many thanks,
Debs
0
 
howsonhomeAuthor Commented:
Hi again, the procedure stops at a certain line as in the attached file below but I can't fathom out why, any help would be appreciated!  Thanks


SHEET.txt
0
 
howsonhomeAuthor Commented:
It's the first line the programme encounters that actually has the full 8 entries in some fields if this helps!  Thanks.
0
 
Rory ArchibaldCommented:
You have a mismatch at the end of that record. There are 8 entries (i.e. 7 semicolons) for every field except the last which only has 6. Try this revised version of the GetRowData function:



Function GetRowData(strIn As String) As Variant
    Dim varData, varRecs
    Dim avarOutput()
    Dim lngRecCount As Long
    Dim x As Long, y As Long
    
    varData = Split(strIn, "}")
    ' get rec count from second field
    varRecs = Split(varData(1), ";")
    lngRecCount = 0
    For x = LBound(varRecs) To UBound(varRecs)
        If Len(varRecs(x)) > 0 Then lngRecCount = lngRecCount + 1
    Next x
    If lngRecCount = 0 Then lngRecCount = 1
    ReDim avarOutput(1 To lngRecCount, 1 To UBound(varData) + 1)
    
    ' now iterate the row data
    For x = LBound(varData) To UBound(varData)
        If InStr(varData(x), ";") > 0 Then
            varRecs = Split(varData(x), ";")
            If lngRecCount > UBound(varRecs) + 1 Then lngRecCount = UBound(varRecs) + 1
            For y = 1 To lngRecCount
                avarOutput(y, x + 1) = varRecs(y - 1)
            Next y
        Else
            For y = 1 To lngRecCount
                avarOutput(y, x + 1) = varData(x)
            Next y
        End If
    Next x
    GetRowData = avarOutput
End Function

Open in new window

0
 
howsonhomeAuthor Commented:
I took that line out of the file and run it again, it went much further until it once again encountered a line with the full 8 entries.  Unfortunatly I don't know VB enough to know how to change the programme but this appears to be the problem.  Regards, Deborah
0
 
howsonhomeAuthor Commented:
Sorry, have been ill in hospital, can you reopen this and I'll rate it, thanks
0
 
howsonhomeAuthor Commented:
See above
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 8
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now