Assuming that text files are having the same structure you should only do following:
- paste files in the same location
- make sure "FROM" statement in your script has a sort of wildcard characters in it so multiple files can be read. for example
- so your FROM statement has to look like (note *):
- From [lib://MyConnection/report*.txt]
- as a good practice i also create usually one extra field in my load script which indicates from which file data come from
- FileBaseName() as Source
- FileName() as Source
Sorry i missed your second question:
so regarding timestamps it is just important that the are recognized as a timestamps. It would be helpful if you could just provide how they are written in your files, so i can determine what formatting the are having 12 or 24 hrs with AM/PM or without etc...
for now lets assume you have 2 timestamps (to make it more complicated i am just on purpose showing them in 2 different formats)
- timestamp 1 = 2016/12/06 13:13:55
- timestamp 2 = 06-12-2016 8:15:45 PM
So in your script you have to make sure that the are read as timestamps in the same formats. You can do following then:
- for first timestamp do: Timestamp(Timestamp#(timestamp1, 'YYYY/MM/DD hh:mm:ss')) as timestamp1,
- for secondtimestamp do: Timestamp(Timestamp#(timestamp2, 'MM-DD-YYYY hh:mm:ss TT')) as timestamp2,
- you can also create another field calculating difference between them in load script or just do it on front end expression
as you can see format of timestamps in this file is not YYYY-MM-DD but MM/DD/YYYY.
So please change your script for all timestamp fields to something like:
Timestamp(Timestamp#(timestamp1, 'MM/DD/YYYY hh:mm'))
I am ot sure how your time is held there as there is no indication whether it is AM or PM in timestamp field bu you should get away just withi this code
let me know how it goes!
Still cant get that date thing working?
Timestamp (ScheduledLeaveTime, 'MM-DD-YYYY hh:mm') as ScheduledLeaveTime,
Timestamp (ActualLeaveTime, 'MM-DD-YYYY hh:mm') as ActualLeaveTime
FROM [lib://HealthEX (pharmore_rwinkel)/*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
You are missing very important part of Timestamp# statement which is #.
Compare my & yours code.
Timestatmp# converts text to timestamp where Timestamp function without # converts timestamp to format you want to looka at it.
I made example for you - see attached - cheers
timestamps.qvf 176.0 K