Importing CSV file into Excel

[ExcelMonkey]

I have a CSV file with six columns of hourly data for a one mont
period.

Field 1: Type (Binomial: "Offer" or "Bid")
Field 2: Amount (number)
Field 3: Date (yyyy-mm-dd)
Field 4: Hour (01 to 24)
Field 5: Amount2 (number)
Field 6: Amount3 (number)

I want to import a range of data within the entire data set given
date constraint. For example, I would like to import the "Bids" fo
2004/01/01 hours 1-24. How do I do this? Furthermore, will the dat
format (yyyy-mm-dd) and/or the hour format (01 to 24) in the CSV sourc
file need to be reformatted for VBA to interpret them correctly?

As usual thanks for your time

 

[ExcelMonkey]

When I record the macro to simply import the wholes CSV File withou
query for specific date range the data looks like the example below.
have used "x" for spaces. The recorded code looks like the code below
However, there is more data in the CSV file than excel can handl
(i.e. CSV rows>65536). I need to find a way to import only a portio
of the CSV data given the Field Constraints that I want in Fields 1,
and 4 (i.e. import CSV Data for "Bid" for the date "1/1/2004", fo
hours "1-24"). Basically its like filtering but I want to imort th
filtered CSV data.

Data in Excel after import from CSV
BIDTYPExxAmount1xDATExxxxHourxxAmount2xAmount3
BIDxxxxxx999.99xxx1/1/2004x1xxxx0xxxxxxxxx200
BIDxxxxxx999.99xxx1/1/2004x1xxxx0xxxxxxxxx50
BIDxxxxxx500xxxxxx1/1/2004x1xxxx2xxxxxxxxx2
OFFERxxxx0xxxxxxxx1/1/2004x1xxxx200xxxxxxx200
OFFERxxxx0xxxxxxxx1/1/2004x1xxxx58xxxxxxxx58
etc.


With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Historical_Trading_2004_01.CSV
_
, Destination:=Range("A1"))
.Name = "Historical_Trading_2004_01"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 5, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Su

 

[Tom Ogilvy]

Use data => get external data and build and sql query that will get your
subset.

If you need a macro, turn on the macro recorder.

 

[ExcelMonkey]

Hi Tom. I am not familiar at all with queries or databases. If I go t
Data=>Import External Data=> I have 3 options:

Import Data
New Web Query
New Database Query

As I am pulling from a CSV file, which option do I choose(Databas
Query)? I guess this also means I would have to know how to write th
query in SQL which I do not. Any tips would be appreciated.

Thank-yo