Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading data from CSV file
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading data from CSV file
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 = fso.OpenTextFile(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 "Jacob" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading data from CSV file
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? On Jul 18, 5:52 pm, "Steve Yandl" wrote: 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 = fso.OpenTextFile(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 "Jacob" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading data from CSV file
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 "JL" wrote in message ... 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? On Jul 18, 5:52 pm, "Steve Yandl" wrote: 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 = fso.OpenTextFile(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 "Jacob" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading data from CSV file
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 "Jacob" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading data from an excel file to access and vice versa | Excel Programming | |||
reading txt file and copying the lines in new excel file | Excel Programming | |||
reading from another file and pasting to current file, "combobox" | New Users to Excel | |||
reading data from 2nd file in 1st file using Combobox | Excel Programming | |||
reading a data file | Excel Programming |