I might suggest importing the raw data as is for the individual date
and time strings.
Once the SQL table has been created, add an additional column with
datetime type set...
You can then update all rows by concatenating the two fields into the
new datetime field...
Not sure how this would be accomplished within SQL,,, however, given
that the formatting of the two text fields are correct,,, using CF it
can be done as follows:
<cfquery name="updateTableName" datasource="yourDataSourceHere">
Update theTableName
Set theDateTimeField = <cfqueryparam
value="#theTableName.theDateStringField#
#theTableName.theTimeStingField#" cfsqltype="CF_SQL_TIMESTAMP">
Where 'WhatEver Condition You Want Here'
</cfquery>
Hope this helps...
Michael Lee
On Feb 11, 2010, at 8:32 AM, Ian Skinner wrote:
>
> Not a ColdFusion specific question, other then that I will write some
> ColdFusion templates to report the data once I have it imported. But
> the HOF groups are just such a great resource.
>
> It has been quite some time since I have worked with SQL Server. I
> would like to import a large (~673MB) text log file into a table. The
> fields in the text file are space[ ] deliminated. The first two
> fields
> are a date string and a time string. I would like to combine these
> two
> fields into a single datetime field in the database table. I am
> finding
> this surprisingly difficult to do. There does not seem to be any
> way to
> concatenate the first two fields of the text file into one field with
> the SQL Server Import and Export Wizard in the SQL Server Management
> Studio.
>
> I have a bulk loader example that would theoretically import this data
> as well, but it also assumes a one to one relationship between the
> text
> fields and the table fields.
> BULK INSERT dprmisc.iis.weblog1 FROM 'c:\IIS Tools\preplog
> \allsites.log'
> WITH ( FIELDTERMINATOR = ' ', ROWTERMINATOR = '\n' )
>
> I could probably write a SQL insert statement, but I don't know how to
> access a flat text file with SQL inside the Management Studio tools.
>
> Any help or sugestions would be appreciated, or do I just have to give
> up on this dream and seperate the date and the time values in the
> database table?
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3282
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
