CSV reads whole file not line

[kermorvan]

using the code below I read data i from a .CSV file. When I run it on one file it works as expected. However when run on an almost identical file it fails and reads the whole file in to one line. Both files are exported from the same software in the same manner. When viewed in excel or in notepad++ they look almost identical. They are both over multiple lines. Any ideas on what could be causing this?

Do Until EOF(1)
    Line Input #1, linefromfile
        
    lineitems = Split(linefromfile, ",")
        
    For counter = 0 To UBound(lineitems)
    ActiveCell.Offset(row_number, counter).Value = lineitems(counter)
            
    Next
    row_number = row_number + 1
Loop

[TMS]

One piece of code, two files, two different results ... what does that tell you? They may look identical, but they're not.

How would you have us diagnose the problem, seeing as you have the two files and we don't.

I note that the output will start with the ActiveCell. Is that intentional? Do you select a specific cell before running the macro?

Probably be useful to see the whole of the code, ideally in the workbook in context. In other words, you need to upload three files

Regards, TMS

[kermorvan]

Thank you for the quick reply.

Was hoping someone might know without me having to post the files as they contain sensitive information as this is a work project. I have removed a few key words with find and replace so the files are not identical to the originals but they still behave the same.

As for the workbook I cannot upload that as it has multiple sheets that all rely on the previous one. This is information that I am unable to remove or fudge without serious work. Below is more of the macro if that helps.

username = (Environ$("Username"))
    FilePath = "C:\Users\" & username & "\Desktop\EoDFile.csv"
    Open FilePath For Input As #1
    ActiveWorkbook.Worksheets("EoD Paste Area").Activate
    ActiveWorkbook.Worksheets("EoD Paste Area").Range("A1").Select
    Do Until EOF(1)
        Line Input #1, linefromfile
        
        lineitems = Split(linefromfile, ",")
        
        For counter = 0 To UBound(lineitems)
            ActiveCell.Offset(row_number, counter).Value = lineitems(counter)
            
        Next
        row_number = row_number + 1
    Loop
    
    Close #1

*Edit

I have just recreated the part of the workbook that this is happening with. I have just tested with the two files and they still behave in the same manner.

[TMS]

OK, as I suspected, the file structures are different. The working file has Carriage Return / Line Feed, whereas, the other file has only a Line Feed.

The following code reads the whole file into memory. It then checks for a vbCrLf and, if it finds one, it uses that as the line separator. If it doesn't find one, it uses vbLf as the line separator. It splits the date into an array of lines and outputs that directly to the sheet. It then uses Text to Columns to separate out the columns. And, finally, it autofits the columns. You might need to put a bit of work into tidying up the formatting but hey, what do you expect for free?

At least all the data is output where it should be. Incidentally, with the broken file, all the data is output ... just all across the top row

Enjoy

Regards, TMS

' Based on code by Suat Mehmet Ozgur of Istanbul, Turkey.
' Suat develops applications in Excel, Access, and Visual Basic
' found at:
' http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27980851.html

Option Explicit

Sub ReadTxtLines()
    
Dim sht As Worksheet
Dim fso As Object
Dim fil As Object
Dim txt As Object
Dim strtxt As String

' choose which file to open
Const fName As String = "EoDFile.csv"
'Const fName As String = "EoDFile.working.csv"

'Working on the relevant sheet
Set sht = Sheets(fName)
'Clear data in the sheet
sht.UsedRange.ClearContents

'File system object that we need to manage files
Set fso = CreateObject("Scripting.FileSystemObject")

'File that we like to open and read
Set fil = fso.GetFile("C:\Users\Trevor\Downloads\" & fName)

'Opening file as a TextStream
Set txt = fil.OpenAsTextStream(1)

'Reading file include into a string variable at once
strtxt = txt.ReadAll

'Close textstream and free the file. We don’t need it anymore.
txt.Close

'Find the first placement of new line char
Dim lSep As Long, chrSep
lSep = InStr(1, strtxt, vbCrLf)
If lSep = 0 Then
    chrSep = vbLf
Else
    chrSep = vbCrLf
End If

Dim lineText
lineText = Split(strtxt, chrSep)

Application.ScreenUpdating = False
With sht
    ' output the data
    .Range("A1").Resize(UBound(lineText) + 1, 1) = Application.Transpose(lineText)
    ' split the data with Text to Columns
    .Range("A1").Resize(UBound(lineText) + 1, 1) _
        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, _
            FieldInfo:=Array _
                    (Array(1, 1), Array(2, 1), Array(3, 1), _
                     Array(4, 1), Array(5, 1), Array(6, 1), _
                     Array(7, 1), Array(8, 1), Array(9, 1), _
                     Array(10, 1), Array(11, 1), Array(12, 1), _
                     Array(13, 1), Array(14, 1)), _
            TrailingMinusNumbers:=True
    ' autofit the column width
    .Range("A3").CurrentRegion.EntireColumn.AutoFit
End With
Application.ScreenUpdating = True

' It will be already released by the ending of this procedure but
' as a good habit, set the object as nothing.
Set fso = Nothing

End Sub

[kermorvan]

Thank you that works a treat. Still don't understand why the reporting software spits out two different formats. With this method it doesn't matter though, so once again thank you.

[TMS]

A slightly amended version of the code. Should make it easier to tailor to to match your original code.

' Based on code by Suat Mehmet Ozgur of Istanbul, Turkey.
' Suat develops applications in Excel, Access, and Visual Basic
' found at:
' http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27980851.html

Option Explicit

Sub ReadTxtLines()
    
Dim sht As Worksheet
Dim fso As Object
Dim fil As Object
Dim txt As Object
Dim strtxt As String

Dim sPath As String
Dim fName As String
Dim shName As String
Dim FilePath As String

Dim username As String
username = (Environ$("Username"))

'sPath = "C:\Users\" & username & "\Desktop\"
sPath = "C:\Users\" & username & "\Downloads\"  ' for testing

' choose which file to open
'Const whichFile As String = "EoDFile"
Const whichFile As String = "EoDFile.working"

fName = whichFile & ".csv"
shName = whichFile & " Paste Area"
FilePath = sPath & fName

'Working on the relevant sheet
Set sht = Sheets(shName)
'Clear data in the sheet
sht.UsedRange.ClearContents

'File system object that we need to manage files
Set fso = CreateObject("Scripting.FileSystemObject")

'File that we want to open and read
Set fil = fso.GetFile(FilePath)

'Open file as a TextStream
Set txt = fil.OpenAsTextStream(1)

'Reading whole file into a string variable at once
strtxt = txt.ReadAll

'Close textstream and free the file. We don’t need it anymore.
txt.Close

'Determin what the line separator is
Dim lSep As Long, chrSep
lSep = InStr(1, strtxt, vbCrLf)
If lSep = 0 Then
    chrSep = vbLf
Else
    chrSep = vbCrLf
End If

Dim lineText
lineText = Split(strtxt, chrSep)

Application.ScreenUpdating = False
With sht
    ' output the data
    .Range("A1").Resize(UBound(lineText) + 1, 1) = Application.Transpose(lineText)
    ' split the data with Text to Columns
    .Range("A1").Resize(UBound(lineText) + 1, 1) _
        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, _
            FieldInfo:=Array _
                    (Array(1, 1), Array(2, 1), Array(3, 1), _
                     Array(4, 1), Array(5, 1), Array(6, 1), _
                     Array(7, 1), Array(8, 1), Array(9, 1), _
                     Array(10, 1), Array(11, 1), Array(12, 1), _
                     Array(13, 1), Array(14, 1)), _
            TrailingMinusNumbers:=True
    ' autofit the column width
    .Range("A3").CurrentRegion.EntireColumn.AutoFit
End With
Application.ScreenUpdating = True

' It will be already released by the ending of this procedure but
' as a good habit, set the object as nothing.
Set fso = Nothing

End Sub

[TMS]

Is this resolved?



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

You're welcome.