I have a CSV File which I want to import into my spreadsheet. I hav
included the file in this posting.

I use the following code below to pull in the entire file. It work
great. It creates separate columns for the data items based on comm
delimited logic. I want to update the code so that I can enter a quer
based on the items in column 1, 3 and 4. Column 1 has either "Offers
or "Bids" data. Note they are continuous. Column 3 has "date" data
and column 4 has "Hour" data (1-24). I want to be able to query o
only the "Offers", for a given day in column 3 (ie. 01/01/2004) for
given hour in column 4.

How do I do this?


Sub ImportRange1()
Set ImpRng = ActiveCell
Open "c:\Documents an
Settings\rkaczanowski.EDCASSOCIATES\Desktop\Histor ical_Trading_2004_01.CSV
_
For Input As #1
r = 0
c = 0
txt = ""
Application.ScreenUpdating = True

Do While Not EOF(1)
Line Input #1, VData
For i = 1 To Len(VData)
char = Mid(VData, i, 1)
If char = "," Or i = Len(VData) Then
ActiveCell.Offset(r, c) = txt
c = c + 1
txt = ""
Else
If char < Chr(34) Then _
txt = txt & Mid(VData, i, 1)
End If
Next i
c = 0
r = r + 1
Loop
Close #1
Application.ScreenUpdating = True
End Su

Attachment filename: historical_trading_2004_01.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=57349
--
Message posted from http://www.ExcelForum.com

I'd suggest using a function to parse the long string into fields...

Good luck...

Mike Clare


Sub SampleMacro()
'
Dim r1 as Integer
Dim c1 as integer
Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim fname As String
fname = Application.GetOpenFilename("Text Files (*.txt;*.csv)
*.txt;*.csv")
Open fname For Input As #1
r1 = 1
Do While Not EOF(1)
Line Input #1, s1
s2 = parseCSV(s1, 2)
if s2="Offers" then
c1 =1
s2=parseCSV(s1,c1)
do while len(s2)0
ActiveCell.Offset(r1, c1) = s2
c1=c1+1
s2=parseCSV(s1,c1)
loop
r1 = r1 +1
end if
Loop
End Sub

Private Function parseCSV(s0 As String, i1 As Integer)
Dim i0 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer
Dim i5 As String
Dim q1 As String
Dim q2 As String
Dim s1 As String
Dim s2 As String
s1 = s0
q1 = Chr(34)
q2 = q1 & ","
i2 = 1 ' i2 is the pointer to the current field
i3 = Len(s1) ' i3 is the length of the field
i4 = 1 ' i4 is the field counter
i0 = InStr(s1, q1 & q1)
Do While (i0 < 0) ' this loop replaces d.dquote with unprintabl
characters
Mid(s1, i0, 2) = Chr(1) & Chr(1)
i0 = InStr(s1, q1 & q1)
Loop
Do While (i4 < i1 And i2 < i3) ' the first loop jumps through th
leading fields
If Mid(s1, i2, 1) = q1 Then ' does the field begin with a dquote
i5 = InStr(i2 + 1, s1, q2) ' i5 points to next '",'
If i5 = 0 Then ' if '0' then end of string
i5 = i3
i2 = i3
Else
i2 = i5 + 2 ' set the pointer to point to nex
field
End If
Else
i5 = InStr(i2 + 1, s1, ",") ' find the next comma
If i5 = 0 Then
i5 = i3 ' not found then eol
i2 = i3
Else
i2 = i5 + 1 ' set pointer to start of next field
End If
End If
i4 = i4 + 1 ' bump counter
Loop
If i2 = i3 Then ' when it gets here, it is pointing to the field
parseCSV = "" ' *or* it ran out of fields
Else
If Mid(s1, i2, 1) = q1 Then ' begin with a dquote?
i5 = InStr(i2 + 1, s1, q2) ' bump pointer
If i5 = 0 Then ' is field @ eol
i5 = i3
End If
s2 = Mid(s1, i2 + 1, i5 - i2 - 1) ' extract the target field
i2 = InStr(s2, Chr(1) & Chr(1)) ' look for previously flagge
d.dquote
Do While i2 < 0
s2 = Left(s2, i2) & Right(s2, Len(s2) - i2 - 1)
Mid(s2, i2, 1) = q1
i2 = InStr(s2, q1 & q1)
Loop
Else
i5 = InStr(i2 + 1, s1, ",") ' point to next comma
If i5 = 0 Then ' if eol, point to end
i5 = i3
End If
s2 = Mid(s1, i2, i5 - i2) ' extract feild
End If
parseCSV = s2
End If
End Functio

--
Message posted from http://www.ExcelForum.com

ExcelMonkey wrote ...

I have a CSV File which I want to import into my spreadsheet. I have
included the file in this posting.

I use the following code below to pull in the entire file. It works
great. It creates separate columns for the data items based on comma
delimited logic. I want to update the code so that I can enter a query
based on the items in column 1, 3 and 4. Column 1 has either "Offers"
or "Bids" data. Note they are continuous. Column 3 has "date" data,
and column 4 has "Hour" data (1-24). I want to be able to query on
only the "Offers", for a given day in column 3 (ie. 01/01/2004) for a
given hour in column 4.

How do I do this?

Open "c:\Documents and
Settings\rkaczanowski.EDCASSOCIATES\Desktop\Histor ical_Trading_2004_01.CSV"
_
For Input As #1

<SNIP

You could do this with ADO and SQL:

Option Explicit

Const PATH As String = "" & _
"c:\Documents and Settings\rkaczanowski.EDCASSOCIATES\Desktop\"

Const TABLE As String = "Historical_Trading_2004_01.CSV"

Private Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<<PATH;" & _
"Extended Properties='Text;HDR=NO'"

Sub TestLateBound()

Dim oConn As Object
Dim oRs As Object
Dim strCon As String
Dim strSql As String

strCon = CONN_STRING
strCon = Replace(strCon, "<<PATH", PATH)

Set oConn = CreateObject("ADODB.Connection")
With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = strCon
.Open
End With

strSql = "SELECT F1, F2, F3, F4" & _
" FROM " & TABLE & _
" WHERE F1='Offers'" & _
" AND F3=#01 JAN 2004#" & _
" AND F4=12"

Set oRs = oConn.Execute(strSql)

ActiveCell.CopyFromRecordset oRs

oRs.Close
oConn.Close

End Sub