Using Input statement to read from text/csv file

[KGee]

I'm using the input statement to read data from a CSV file. I need to retrieve the strings from the 7th and 14th fields in the file and assign them to variables. Is there any way to specify only the 7th and 14th fields so I don't have to bother listing the other 12 variables I don't need?

Input #1, var1, var2, var3...var14

 

[Ruddles]

You could cut down on the variables slightly by using the same variable for the unwanted fields but if you're going to read the items one at a time, you have to list them all.

If you read the file one line at a time instead, you can do something like this:-

Do Until EOF(intFH)
  Line Input #intFH, sRec
  var7 = Split(sRec, ",")(6)
  var14 = Split(sRec, ",")(13)
Loop

The other option if the file has field titles is to run a SQL query against the file. Let me know if you'd like to consider that. I may grub around for some code and post it anyway...

 

[apr pillai]

You are reading a sequential file; you cannot skip the reading order but you can ignore the read data in between. Try the following code snippet to take only the 7th and 14th data fields and ignore others:

For j = 1 To 6: Input #1, var1: Next

Input #1, var7

For j = 1 To 6: Input #1, var1: Next

Input #1, var14

 

[GTO]

Just curious, if its a csv file, wouldn't it be easy to open; grab the 7 & 14th columns, plop the vals into arrays and read thru those?

 

[Ruddles]

Yes, if you're interested this will also do the job. Paste this code in a new standard module and add a reference to the Microsoft ActiveX Data Objects Library (Tools > References). Change the folder name and file name to suit and change the field names so they match the names of the 7th and 14th field names in the first row of your CSV file.

Option Explicit
 
Public Sub RunSQLqueryCSV()
 
' Requires: Microsoft ActiveX Data Objects Library
 
  Dim rsConn As ADODB.Connection
  Dim rsData As ADODB.Recordset
  Dim strSQL As String
  
  Set rsConn = New ADODB.Connection
  rsConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;" & _
              "Data Source=[COLOR=red][B]C:\Folder\[/B][/COLOR];"
  
  If rsConn.State <> 1 Then
    MsgBox "Connection failed" & Space(15), vbOKOnly + vbExclamation
    Exit Sub
  End If
       
  strSQL = "SELECT DS1.[COLOR=red][B]Field7Name[/B][/COLOR], DS1.[COLOR=red][B]Field14Name[/B][/COLOR] FROM [COLOR=red][B]import_file.csv[/B][/COLOR] DS1;"
         
  Set rsData = New ADODB.Recordset
  rsData.Open strSQL, rsConn, 3, 1, 1
  
  Do Until rsData.EOF
[COLOR=green]    ' at this point you have a new row from your input file
[/COLOR]    Debug.Print rsData![B][COLOR=#ff0000]Field7Name[/COLOR][/B], rsData![B][COLOR=#ff0000]Field14Name[/COLOR][/B] [COLOR=green]' (for example)[/COLOR]
    rsData.MoveNext
  Loop
  rsData.Close
  
  Set rsData = Nothing
  Set rsConn = Nothing
 
End Sub

SQL can be asked to return the data from the file sorted in any sequence you like, which can be useful.

 

[KGee]

OK, thanks for the suggestions. It looks like there are a few options I can try. I should have mentioned that there are no field names in the file. It only contains data but I know the order of the fields. And I only need to get the values from the first record as the same values will repeat for all of the records in the file. The 7th and 14th fields should really have been setup as part of a header record but we would have to submit a request through IT and it's not worth it at this point since we can easily work around the issue.

Thanks again I'll try these out and post back if I need further assistance.

 

[Ruddles]

Just curious, if its a csv file, wouldn't it be easy to open; grab the 7 & 14th columns, plop the vals into arrays and read thru those?

The GRAB and PLOP commands were discontinued in versions of Excel from 2007 onwards.

 

[KGee]

If you read the file one line at a time instead, you can do something like this:-

Do Until EOF(intFH)
  Line Input #intFH, sRec
  var7 = Split(sRec, ",")(6)
  var14 = Split(sRec, ",")(13)
Loop

I ended up using a variation of this code, minus the loop as I only needed to read one record. I ran into a slight issue as the field separator is a comma but the fields are enclosed in double quotes which I didn't want in my final variable. I tried using the replace function to remove them but couldn't get the syntax right. I'm guessing an escape clause is needed for double quotes but I ended up using Chr(34) and that worked.

Thanks again.

PS - What are grab and plop? I tried searching help and they did not appear. I'm using v2003 at work.

 

[Ruddles]

What are grab and plop? I tried searching help and they did not appear. I'm using v2003 at work.

Sorry, I was making fun of GTO's post. There's no such thing.

 

[GTO]

The GRAB and PLOP commands were discontinued in versions of Excel from 2007 onwards.

I can't make up my own commands?