The hour is disappearing when reading a text file into crystal

[thetoftee]

Hi,

I'm reading a text file directly into crystal which looks like the following:

Hr,0-4s,5-9s,10-14s,15-19s,20-24s,25-29s,30-44s,45-59s,60-89s,gt90 sec,max_ans_time
2013-06-26 04,10,0,0,0,0,0,0,0,0,0,0
2013-06-26 05,33,0,0,0,0,0,0,0,0,0,1
2013-06-26 06,58,0,0,0,0,0,0,0,0,0,3

The first column is the date and hour. Crystal picks the column up and converts the date properly, but I'm losing the hour. Anyone got any ideas how I can prevent this from happening?

Thanks

James

 

[lbass]

I think the datetime string would need to be expressed as:
'yyyy-MM-dd hh:mm:ss'. You could then format the result to show the date and hour, if you wished.

-LB

 

[thetoftee]

Thanks but unfortunatly that is the way the text file comes through

 

[Charliy]

What do you want to do with this field? If you're using it for grouping you can just leave it as a string.

 

[pmax9999]

I have not previously had occasion to report from a Text file before, but I have been playing around with this out of curiosity.

For some reason when Crystal interprets the first column of data it interprets it as Date/Time but seems to ignore the hours, presumably because it is an incomplete date in the absence of minutes and seconds. I could find no way around this as the hours seem to be lost completely in the conversion.

Is it possible to have the data file changed at its source, either to include minutes and seconds or to surround the first column of data in quotation marks? By including quotation marks, Crystal sees the first column as Text rather than Date/Time, and can be manipuated into the correct Date/Time using a formula (see below).

If the file can not be changed at its source, the other approach that worked in my testing, is to open the TXT file in Excel, follow the prompts to convert it to an XLS file, and base the report on that XLS file.

This approach allows the first column to be saved as Text, which can then be converted to a Date/Time. I used the following formula:

DateTime(Left({Your_File.Hr},10) + ' ' + Right({Your_File.Hr},2) + ':00:00')

These approaches are probably not ideal, but I am not sure if there is an alternative. It is likely that the conversion to XLS could be automated with some VBA code or similar, but that is beyond my skills.

Hope this helps.

Cheers
Pete

 

[SkipVought]

hi,

You could use Excel to condition the data using this process:

1. IMPORT the data into Excel using Data Get external data > From Text - Fixed width in order to import this into ONE COLUMN
[tt]
2013-06-26 04,10,0
2013-06-26 05,33,0
2013-06-26 06,58,0
[/tt]
2. Use Find & Replace to REPLACE , with : to give your this (Excel actually converts your text into a Date/Time numeric value and displays a Date/Time format)
[tt]
6/26/2013 4:10
6/26/2013 5:33
6/26/2013 6:58
[/tt]
which will be your date/time values.

SaveAs a text file for import into CR.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[thetoftee]

Hi,

Thanks for the responses, I thought I was going mad in crystal not being able to convert it on the import, but looks like I'm going to have to use excel.

Thanks again

James