csv on two lines reformat to one line

wouldn't the easiest way be to have a 2nd sheet with links back to the desired cells in the 1st sheet?

No I have tried that and I have over 5000 rows

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

Select allOpen 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.

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

Select allOpen in new window

   Debug.Print TextLine    ' Print to the Immediate window.

I am getting an error on line

 If EOF Then

"Argument not oprional"

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

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 

Select allOpen in new window

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?

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

File attached. I have put what the end result should look like on the tab called Result.

Many thanks

Regards
039038.csv

excelent thanks Dale, thanks for staying with it.

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.

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

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

Select allOpen in new window

Many thanks