Solved: Reading a .csv File?

[bdl004]

Hi Everybody,

I have been given a .txt file that has a variable number of rows of garbage on top (usually 1 to 10 or so) followed by a variable number of columns of data. I have written some code to transform the .txt file to a .csv file to read the data element by element but I don't know how to skip the garbage lines on top. Any advice on how to do this?

Attached is an example of the created .csv file.

[GTO]

Hi there,

There is only one column of data in 'test2.csv' at #1. I would guess that the data you want starts at '1 2 3'?

Could you zip the original text textfile (or a fake one that accurately portrays, including non-printing chars and such) along with a wb that shows the desired 'After' view?

Mark

[bdl004]

Hi Mark,

Thanks for your reply. I have attached an example of an original text file. Perhaps I should describe my problem more fully in case you have a better solution than mine.

What I need to do is to read in a text file (like the one attached, but with more than 500 000 rows of data) directly in to a set of arrays in VBA, without writing the data to a sheet (takes too long).

The macro must read the first column of good data to a single array, the second column of good data to a second array, and the nth column of good data (user specified) to a third array. The user always specifies the number of lines of garbage heading the file as well as the column of the desired parameter.

The good data in the attached file starts with "123.4".

Thanks!

Brett

[GTO]

Hi Brett,

I wasn't sure how you wanted the user to select which columns to load into array(s), but maybe a start as to selecting which row to start on.

See attachment, but basic code is:

Option Explicit
 
Public lSkipLines As Long
 
Sub Main()
Dim FSO As Object
Dim fsoTFile As Object
Dim wbCSV As Workbook
Dim strFullName As String
Dim i As Long
 
'// Alter to suit - how many lines are previewed for user.                              //
Const NO_LINES_SHOWN As Long = 10
 
    '// Optional, change to Drive and Directory the text file is expected to reside in. //
    ChDrive Left(ThisWorkbook.FullName, 1)
    ChDir ThisWorkbook.Path
 
    strFullName = Application.GetOpenFilename( _
                            FileFilter:="Text Files (*.txt;*.csv), *.txt;*.csv", _
                            Title:="Select a TextFile", MultiSelect:=False)
 
    '// In case user cancels                                                            //
    If CStr(strFullName) = "False" Then Exit Sub
 
 
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fsoTFile = FSO.OpenTextFile(strFullName, 1, False, -2)
    With fsoTFile
        '// Load userform and add lines to
        Load frmValidateSkipLines
        For i = 1 To NO_LINES_SHOWN
            frmValidateSkipLines.lstPreview.AddItem .ReadLine
        Next
        .Close
    End With
 
    frmValidateSkipLines.Show
    If lSkipLines = 0 Then Exit Sub
 
    If GetCSV(strFullName, lSkipLines, wbCSV) Then
        MsgBox wbCSV.Sheets(1).Name
        '// Load arrays etc
    End If
End Sub
 
Function GetCSV(FName As String, SkipLines As Long, Optional WB As Workbook) As Boolean
Dim wks As Worksheet
Dim strTemp As String
 
    Set WB = Workbooks.Open(Filename:=FName, Format:=3, Origin:=xlWindows) 'xlWindows  xlMSDOS
    Set wks = WB.Worksheets(1)
 
    strTemp = "1:" & SkipLines
 
    With wks
        .Rows(strTemp).Delete
        .UsedRange.NumberFormat = "General"
    End With
 
    strTemp = Mid(FName, InStrRev(FName, "\") + 1)
    strTemp = Mid(strTemp, 1, InStrRev(strTemp, ".") - 1)
 
    WB.SaveAs Filename:=Left(FName, InStrRev(FName, "\")) & strTemp & ".csv", _
              FileFormat:=xlCSV
 
    If Not WB Is Nothing Then GetCSV = True
End Function

Mark

[bdl004]

Wow, thanks! I can definitely use that to fit my code... I really appreciate all the help!

Cheers,

Brett