Importing CSV file into Excel
 

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

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

Importing CSV file into Excel
 

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 rows65536). 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

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

Importing CSV file into Excel
 

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.

--
Regards,
Tom Ogilvy

"ExcelMonkey " wrote in message
...
When I record the macro to simply import the wholes CSV File without
query for specific date range the data looks like the example below. I
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 handle
(i.e. CSV rows65536). I need to find a way to import only a portion
of the CSV data given the Field Constraints that I want in Fields 1,3
and 4 (i.e. import CSV Data for "Bid" for the date "1/1/2004", for
hours "1-24"). Basically its like filtering but I want to imort the
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 Sub


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

Importing CSV file into Excel
 

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

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