[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

csv on two lines reformat to one line

Posted on 2014-07-15
16
Medium Priority
?
122 Views
Last Modified: 2014-07-17
Can an Expert assist with this please. I have seen similar but no answers actually does what I need


Cat          Holding W/Held COI Tax W/Held B/U Tax    Charges Tax Deducted Type      Admin     Net Amount   Amount to Client
W S H0008 (330) OSCO      ABC HOLDINGS LTD (OF)
Norm          9.4500           0.00           0.00       0.00                         0.00         152.61         USD 152.61

MALAO0001 (999) INDV      MALAOUDI S                                  *** # ***
Norm         12.4200           0.00           0.00       0.00                         0.00         200.57         GBP 117.26

Open in new window


I need to turn the above [using vba and excel] into

Client	                                   Nominal	Orig Ccy	Credit
A B B HOLDINGS LTD (OF)	9.45	152.61	152.61
MALAOUDI S                               12.42        200.57       117.26

Open in new window


is this doable please
Thanks
0
Comment
Question by:Jagwarman
  • 8
  • 7
16 Comments
 
LVL 23

Expert Comment

by:Danny Child
ID: 40196671
wouldn't the easiest way be to have a 2nd sheet with links back to the desired cells in the 1st sheet?
0
 

Author Comment

by:Jagwarman
ID: 40196731
No I have tried that and I have over 5000 rows
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40196996
i'm guessing that the best way to do this will be to open the files for input, and then use the LineInput command to read each line individually.  You may find that although the CSV seems to be broken into two lines, that when you do the lineinput, you get all of the data on a single line.

Then you would need to parse the various elements of each line and write them into the various elements of your spreadsheet.   The following is slightly modified from the Access help on the Line Input # Statement.
Dim TextLine
Dim strArray() as string 
Dim intRow as integer, intCol as integer

Open "C:\yourpath\filename.csv" For Input As #1    ' Open file.
Do While Not EOF(1)    ' Loop until end of file.
    Line Input #1, TextLine    ' Read line into variable.
    Debug.Print TextLine    ' Print to the Immediate window.

    'Get rid of all of the extra spaces in each line
    'this will remove all but a single space between each element of the string
    Do While instr(TextLine, "  ") > 0
        TextLine = Replace(TextLine, "  ", " ")
    Loop

    'Now parse what is left into individual elements
    strArray = Split(TextLine, " ")
    intRow = intRow + 1
    for intCol = lbound(strArray)  to ubound(strArray)    
        ActiveSheet.Cells(intRow, intCol + 1) = strArray(intCol)
    Next

    'insert similar code to parse the second line of each "record" here

Loop
Close #1

Open in new window

This is untested, and I might have the Row/Col backwards in the line where I'm writing the data from strArray() into the various cells.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 40197004
Actually, you'd probably be better off doing:
    Line Input #1, TextLine1    ' Read line into variable.
    if EOF then
         TextLine2 = ""
    Else
        Line Input #1, TextLine2
    Endif
    TextLine = TextLine1 & " " & TextLine2

Open in new window

   Debug.Print TextLine    ' Print to the Immediate window.
0
 

Author Comment

by:Jagwarman
ID: 40197286
I am getting an error on line

 If EOF Then

"Argument not oprional"
0
 

Author Comment

by:Jagwarman
ID: 40197313
In fact when I try your original code it splits the stuff out but it does not put it onto the one line as per my original request.
Client                                         Nominal      Orig Ccy      Credit
A B B HOLDINGS LTD (OF)      9.45      152.61      152.61
MALAOUDI S                               12.42        200.57       117.26

Thanks
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40197482
sorry, It's been a while since I've had to use this technique, and I was working mostly from memory, on my iPad.
That should have been:
IF EOF(1) then
    TextLine2 = ""
Else
    Line Input #1, TextLine2
Endif
TextLine = TextLine1 & " " & TextLine2 

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 40198652
fyed (Dale Fye) very much appreciate your help but unfortunately it is not achieving what I asked for.

This is the csv file data

Cat          Holding W/Held COI Tax W/Held B/U Tax    Charges Tax Deducted Type      Admin     Net Amount   Amount to Client
W S H0002 (330) OSCO      A B C HOLDINGS LTD (OF)
Norm          9.4500           0.00           0.00       0.00                         0.00         152.61         USD 152.61
MALAO0001 (999) INDV      MALAOUDI S                                  *** # ***
Norm         12.4200           0.00           0.00       0.00                         0.00         200.57         GBP 117.26

When I run it through your Macro it does Text to Columns like this

Cat      Holding      W/Held      COI      Tax      W/Held      B/U      Tax      Charges      Tax      Deducted      Type      Admin      Net      Amount      Amount      to      Client
W      S      H0002      -330      OSCO      A      S      H      HOLDINGS      LTD      (OF)                                          
Norm      9.45      0      0      0      0      152.61      USD      152.61                                                      
So you can see it is still keeping them on two rows.

I need the second row in each group to be on the same line as the first  so that I can end up [after maybe using a bit more code] like this

Client                                         Nominal      Orig Ccy      Credit
A B C HOLDINGS LTD (OF)      9.45      152.61      152.61

Maybe it's not doable?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40198951
just to be clear, this is not a csv file.  It appears have fixed width columns.

If you could post a file that has been sanitized of proprietary or personal info, maybe I could provide a better option, but I need more than two records to understand the pattern.

Dale
0
 

Author Comment

by:Jagwarman
ID: 40199015
File attached. I have put what the end result should look like on the tab called Result.

Many thanks

Regards
039038.csv
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40199146
OK,

Check out the attached, you should be able to do something with this.

You will obviously need to modify the path and filename, but this should give you a start point

Dale
MultiLineFlatFile.xlsm
0
 

Author Comment

by:Jagwarman
ID: 40199575
excelent thanks Dale, thanks for staying with it.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40199614
glad to help.

You might want to institute some form of filedialog code to allow you to select the file to import, instead of having to change the name manually in the code each time this is run.
0
 

Author Comment

by:Jagwarman
ID: 40201421
I have now been asked to include the A B C0002, YYX 0002, ABFA 0001 etc. so that it would look like:

Reference      Client      Nominal      Orig Ccy Amount      Amount to Client
A B C0002       ABCA HOLDINGS LTD (OF)      9.45      152.61      USD 152.61
How easy is that. [Happy to respost to give you the points]


Cat          Holding W/Held COI Tax W/Held B/U Tax    Charges Tax Deducted Type      Admin     Net Amount   Amount to Client
A B C0002 (330) OSCO      ABCA HOLDINGS LTD (OF)
Norm          9.4500           0.00           0.00       0.00                         0.00         152.61         USD 152.61
YYX  0002 (798) OSCO      YYX REINSURANCE (OF)(SCC)
Norm         32.1600           0.00           0.00       0.00                         0.00         519.36         USD 519.36
ABFA 0001 (168) OSCO      ABFA FOUNDATION (OF)
Norm          8.8700           0.00           0.00       0.00                         0.00         143.24         USD 143.24
ABEBE0001 (937) AJDI      ABEBAKAR F(WOS)(SCC)
Norm         10.8600           0.00           0.00       0.00                         0.00         175.38         USD 175.38
AC0008 (999) AJDI      ACMAN R P (OF)                                  *** # ***
Norm          6.9700           0.00           0.00       0.00                         0.00         112.56          GBP 65.81
AL BA0001 (390) AJDI      AL BARBI J A (PBGB)(WOS)
Norm          6.1700           0.00           0.00       0.00                         0.00          99.64          USD 99.64
AL MA0002 (360) AJDI      AL MAALIFA A (SCC) (ABS)
Norm         10.6400           0.00           0.00       0.00                         0.00         171.82         USD 171.82
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40201546
The last couple of lines of that macro are the ones that determine which of the fields show up in your worksheet.  Based on what you provided above, you will need to add a couple of lines, and modify some column pointers:

    Dim TextLine As String, TextLine1 As String, TextLine2 As String
    Dim strArray() As String
    Dim intRow As Integer, intCol As Integer
    
    Open "E:\Access\Experts Exchange\OtherPeoplesStuff\MultilineTextFile.csv" For Input As #1    ' Open file.
    
    ActiveSheet.Cells(1, 1) = "Reference"       '<==Added
    ActiveSheet.Cells(1, 2) = "Client"          '<==Modified
    ActiveSheet.Cells(1, 3) = "Nominal"         '<==Modified
    ActiveSheet.Cells(1, 4) = "Orig Ccy"        '<==Modified
    ActiveSheet.Cells(1, 5) = "Credit"          '<==Modified
    intRow = 1
    
    'Ignore the header row
    If Not EOF(1) Then Line Input #1, TextLine1
    
    'Read the rest of the file
    Do While Not EOF(1)    ' Loop until end of file.
    
        Line Input #1, TextLine1    ' Read line into variable.
        
        'Read the 2nd line of each pair
        If EOF(1) Then
            TextLine2 = ""
        Else
            Line Input #1, TextLine2
        End If
        TextLine = TextLine1 & "   " & TextLine2
        Debug.Print TextLine    ' Print to the Immediate window.
        
        'Get rid of all instances with more than three consecutive spaces
        Do While InStr(TextLine, "    ") > 0
            TextLine = Replace(TextLine, "    ", "   ")
        Loop
        Debug.Print TextLine    ' Print to the Immediate window.
    
        'Now parse what is left into individual elements
        strArray = Split(TextLine, "   ")
        intRow = intRow + 1
        
        ActiveSheet.Cells(intRow, 1) = Left(strArray(0), InStr(strArray(0) & "(", "(") - 1)           '<=Added
        ActiveSheet.Cells(intRow, 2) = strArray(1)                                                 '<==Modified
        ActiveSheet.Cells(intRow, 3) = strArray(UBound(strArray) - 6)               '<==Modified
        ActiveSheet.Cells(intRow, 4) = strArray(UBound(strArray) - 1)               '<==Modified
        ActiveSheet.Cells(intRow, 5) = strArray(UBound(strArray))                     '<==Modified
        
    Loop
    Close #1

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 40201946
Many thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question