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