Here are a few nice tips for working with the Microsoft Text ODBC Driver encapsulated in Fusion

Working with dates:
When comparing dates it is useful to hint the driver about the format. Consider the below sample fragment of a WHERE clause:
WHERE ('2017-02-08' >= format(mytable.[BeginDate], 'yyyy-MM-dd')) AND ( '2017-02-08' <= format(mytable.[EndDate], 'yyyy-MM-dd'))

You can use constant values also.
{d ‘value’} for date, {t ‘value’} for time, {ts ‘value’} for datetime
Example: WHERE mytable.[EndDate] <= {d '2017-02-08'}

Data Types:
You can explictly define the datatypes of a given table by modifying the driver file Schema.ini located in the directory of your data files.
[MyFile.csv] ColNameHeader=True Format=CSVDelimited DecimalSymbol=. DateTimeFormat=MM/dd/yyyy Col1=EmpNum Integer Col2=BeginDate date Col3=EndDate date col4=Desc char col5=Amount float

MSDN documentation

MSDN: Other Text File Driver Programming Details

MSDN: Text File Driver Programming Considerations


Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment