Reading data from CSV file

[Jacob]

I'm trying to create a program using VBA within excel that will read
specific data from a user specified CSV file. I would like the user to
browse for and select the CSV file, and once selected, the program
imports only the required variables from the file into the program to
be used. ideally, this would be done without having to physically open
the CSV file. it would just read it. is this something that an amateur
VBA programmer could handle? does anyone have any advice or
suggestions for me, to point me in the right direction. i am able to
create a diolog box to open a CSV file, but that's about it so far.

thanks!

 

[Steve Yandl]

Jacob,

You didn't really describe what specifically would be retrieved from the csv
file. For the example below, I just have a message box appear and indicate
what the value of the third field in the second line was. Technically, the
csv file gets 'opened', but I think what you're after is that the user
doesn't have to open the file so it appears in a window. Typically, I'd
want to include some error handling but this should get you started.

'--------------------------------------------------------

Sub CherryPickCSV()

Const ForReading = 1

Dim fdPicker As FileDialog
Dim strSel As String
Dim arrFileLines()
Dim i As Long

Set fdPicker = Application.FileDialog(msoFileDialogFilePicker)
Set fso = CreateObject("Scripting.FileSystemObject")

With fdPicker
.Filters.Clear
.Filters.Add "CSV text data", "*.csv"
If .Show = -1 Then
strSel = .SelectedItems(1)
End If
End With

Set objFile = fspenTextFile(strSel, ForReading)
i = 0
Do Until objFile.AtEndOfStream
ReDim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop
objFile.Close

' Do something with the retrieved text
' For demonstration, show the third item in the second line
myField = Split(arrFileLines(1), ",")
MsgBox myField(2)

Set fso = Nothing
Set fdPicker = Nothing
End Sub


'--------------------------------------------------------

Steve Yandl

 

[JL]

Thanks, Steve. I'll have to look at your suggestion a little closer
when i get some time. i'm not that very familiar with this language.
the CSV file will be output from an engineering program. i'd like to
take specific values that are in the file and run them through a
program to get results. searching for, and getting all the values
from the file will be another issue, but i just want to make sure that
i can get them out and into an array or something. once i get them
in, i shouldn't have trouble writing the algorithms. can the csv file
be treated as an excel file as when it is opened in excel? in other
words, will i be able to reference cells within the csv file as though
i was referencing a cell on a sheet?

 

[Steve Yandl]

Your task would probably be easier if you did want to open the csv file as
an Excel workbook. In fact, if you locate a CSV file in Windows Explorer
and double click it, it typically opens in Excel if Excel is installed on
the computer. You could use the Workbooks OpenText method and avoid having
to use the "Scripting.FileSystemObject" at all.

Steve

 

[GS]

I do exactly what you describe here using ADO and treating the CSV file as a
database table. This does not require opening the file and allows you to
specify exactly what values you want to extract. (Assumes the first line in
the file contains fields, all other lines contain relative data for those
fields)

HTH
Garry