[Microsoft] [ODBC Text Driver] Too few parameters. Expected:1

I had a complex query that returned the above error intermittently. It turned out that one of the fields was DATE (upper case) as specified in the schema.ini file for the text file. The query referred to Date (proper case). When the query was changed to refer to DATE (upper case) throughout, the intermittent failures stopped.

More than two tables in an outer join query

I've often come across situations where MS Query complains that you can't have more than two tables involved in an outer join query. I'm not sure if this occurs with every ODBC driver but it certainly does with the Microsoft Text Driver (*.txt; *.csv) driver.  I've always worked around this by using multiple queries to get the job done but I recently discovered that it seems to be a MS Query issue rather than a restriction associated with the text driver. I stumbled across this because Excel 2007 and later allow you to edit the SQL associated with your data connections without invoking MS Query.  I had a three table query with inner joins and discovered that one of the inner joins needed to be an outer join.  I made the change to the SQL manually and it worked.  MS Query still complains and will refuse to deal with the query so I often adopt a hybrid approach to dealing with such queries as follows:

• construct the query in MS Query using inner joins only;
• manually change the inner join details in the SQL generated by MS Query to outer joins

A simple example follows using three data files (Data, Data2 and Data3) each with an ID column on which they are joined. The initial SQL generated from MS Query was:

SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name
FROM Data.txt Data, Data2.txt Data2, Data3.txt Data3
WHERE Data.ID = Data2.ID AND Data.ID = Data3.ID

After editing the SQL became:

SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name
FROM (Data.txt Data
left outer join Data2.txt Data2 on Data.ID = Data2.ID)
left outer join Data3.txt Data3 on Data.ID = Data3.ID

The output of this query looks as follows:

ID	DName	D2Name	D3Name
1	Unrestricted		Unrestricted
2	Restricted	Restricted
3	Designated		Designated

The outer join operation is evidenced by the null values returned under D2Name and D3Name.


For Excel 2003 I make the changes to the SQL by opening an Immediate window in the Visual Basic Editor and typing something like [?activecell.querytable.commandtext] followed by [Enter]. This reports the first SQL above on rows two to four of the window. Then I edit the first line to read [activecell.querytable.commandtext = "SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name FROM (Data.txt Data left outer join Data2.txt Data2 on Data.ID = Data2.ID) left outer join Data3.txt Data3 on Data.ID = Data3.ID"] followed by [Enter]. This changes the SQL without the need to use MS Query.  A simple refresh of the data will show the multitable query with the outer joins in place.

I hope you will find this helpful.  It has always worked for me, but I worry that there must be some good reason that MS Query complains about such a query.

Your comments would be very welcome

Running Totals using SQL and the Text File ODBC driver

With a data file containing detailed dated transactions how can you use SQL to record a running total?

Here's the RTData.txt data file:

Item	Date	Amount
A	01/01/2011	12.30
A	15/01/2011	16.00
B	16/01/2011	5.69
A	01/02/2011	65.12
B	01/02/2011	0.58
A	14/02/2011	8.91
A	28/02/2011	1.00
A	15/03/2011	9.00
B	01/04/2011	31.45
B	01/05/2011	738.00
B	21/05/2011	9.11
A	21/05/2011	10.93

Schema.ini looks as follows:

[RTData.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
Col1=Item Text width 255
Col2=Date Date
Col3=Amount Currency

The SQL written to show the running totals is as follows:

SELECT Cur.Item, Cur.Date, Cur.Amount, Sum(Cum.Amount) AS 'Running Total'
FROM RTData.txt Cum, RTData.txt Cur
WHERE Cum.Item = Cur.Item AND Cum.Date <= Cur.Date
GROUP BY Cur.Item, Cur.Date, Cur.Amount
ORDER BY Cur.Item, Cur.Date

The output is as follows:

Item	Date	Amount	Running Total
A	01/01/2011	12.30	12.30
A	15/01/2011	16.00	28.30
A	01/02/2011	65.12	93.42
A	14/02/2011	8.91	102.33
A	28/02/2011	1.00	103.33
A	15/03/2011	9.00	112.33
A	21/05/2011	10.93	123.26
B	16/01/2011	5.69	5.69
B	01/02/2011	0.58	6.27
B	01/04/2011	31.45	37.72
B	01/05/2011	738.00	775.72
B	21/05/2011	9.11	784.83

It adds another layer of complexity if we want to generate monthly totals and also keep a running total.  This SQL will do it:

SELECT Cur.item, Cur.month, sum(Cur.Amount)/count(Cur.Amount) AS Amount, sum(Cum.Amount) AS 'Running Total'
FROM (SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cur,
(SELECT RTD.Item, (year(RTD.Date)*100)+month(RTD.Date) AS Month, Sum(RTD.Amount) AS Amount
FROM RTData.txt RTD
Group By RTD.Item, (year(RTD.Date)*100)+month(RTD.Date)) as Cum
WHERE Cum.Item = Cur.Item AND Cum.Month <= Cur.Month
Group By Cur.Item, Cur.Month
ORDER BY Cur.Item, Cur.Month

The output is as follows:

Item	Month	Amount	Running Total
A	201101	28.30	28.30
A	201102	75.03	103.33
A	201103	9.00	112.33
A	201105	10.93	123.26
B	201101	5.69	5.69
B	201102	0.58	6.27
B	201104	31.45	37.72
B	201105	747.11	784.83

The trick here is to realise that the Amount column inflates artificially as we progress and we need to correct for this by dividing by count(Cur.Amount) which returns 1, 2, 3 ... with successive records.

Comments welcome.

Dealing with mixed data types in Excel ODBC queries (2)

Shortly after posting the first message on this topic I had cause to use it in a real life situation.  All was fine until a cell in my text field had more than 255 (?) characters.  It broke the solution!

I haven't had time to fully investigate exact limits in all of this but I think the Excel ODBC driver will only accept up to about 255 characters in a "Text" field.  If the driver comes across more than 255 characters it interprets it as a "Memo" type content (up to 32k characters?) and the attempt to query fails at the record that contains the offending data.  How to work around?

Using the IMEX=2 approach from the previous article, firstly, I appended 256 spaces into the first rows of the field that needs to be "Memo".  This was to force the majority type interpretation of this field to be "Memo".  Once this had been done I discovered that the query no longer failed but it only returned the first 253 characters of the "Memo" field.  Why I'm getting 253 I'm not sure but it seems this is a limitation of the Excel ODBC driver when dealing with "Memo" fields.  However, I discovered that if I constructed additional calculated fields in my SQL I could bring back the entire contents of the "Memo" field in 253 character chunks.  The SQL select clause entries look as follows:

''+F1 as [Memo Field],
mid(''+F1,254,253) as [Memo Field 2],
mid(''+F1,507,253) as [Memo Field 3], ... and so on.

I'd be really interested if anyone is able to put some better flesh on these bones.

Functions available with Text File ODBC driver

I can't find documentation anywhere.  The functions available seem to be mainly VB Scipt functions. This is my work in progress list and some comments thereon.  I refer to VB Script documentation for questions on syntax.

http://msdn.microsoft.com/en-us/library/3ca8tfek(VS.85).aspx


Use [ ] or `` with field names.  (` is the character on the key to the left of the number 1 at the top of the keyboard)

isnull() returns true or false. It is a good idea to use iif(isnull(<field>),0,<field>) to set a value for null entries if you might ever want to perform aggregation on the field.

mid()
left()
right()
strcomp()

Concatenate using + e.g. 'Dropped'+' '+'Pennies'

Use single quotes (not double quotes) for text.

year('2009-12-31') = 2009
month('2009-12-31') = 12
day('2009-12-31') = 31
dateadd('yyyy',1,`Date`)
dateserial()
datevalue()
now()
weekday()
isdate()
isnumeric() (watch for nulls)
isempty()
len()
lcase()
ucase()
trim()
ltrim()
rtrim()
string()
chr()
asc()
instr()
rnd()
str() converts number to string (leaves space for minus)
space()
sgn()
abs()
round()
CBool()
CByte()
CCur()
CDate()
CDbl()
CInt()
CLng()
CSng()
CStr()
timer()
typename()

Creating virtual records with Text File ODBC driver

Sometimes it can be useful to be able to add a virtual record to a recordset derived from a text file without actually adding a physical record into the file.

With the text file ODBC driver the table name can be omitted such that 

SELECT 'Hello world!' as [Greeting]

results in

Greeting
Hello world!

Consider a names.txt file with first_name and surname columns. You could add John Doe onto the query output by doing something like

SELECT * FROM [names.txt]
UNION ALL
SELECT 'John' as [first_name]
, 'Doe' as [surname]

CurrencyPosFormat and CurrencyNegFormat settings in Schema.ini

There are 4 possible values for CurrencyPosFormat in a schema.ini file:

• Currency symbol prefix with no separation ($1)
• Currency symbol suffix with no separation (1$)
• Currency symbol prefix with one character separation ($ 1)
• Currency symbol suffix with one character separation (1 $)

However, they are not specified as ($1) and so forth, they are specified with an index number between 0 and 3
i.e. CurrencySymbol=£ and CurrencyPosFormat=1 will result in a setting of 1£.

There are 16 possible values for CurrencyNegFormat in a schema.ini file:

• ($1)
• –$1
• $–1
• $1–
• (1$)
• –1$
• 1–$
• 1$–
• –1 $
• –$ 1
• 1 $–
• $ 1–
• $ –1
• 1– $
• ($ 1)
• (1 $)

However, they are not specified as ($1) and so forth, they are specified with an index number between 0 and 15
i.e. CurrencySymbol=£ and CurrencyNegFormat=15 will result in a setting of (1 £).

So far as I can tell CurrencySymbol=£ and CurrencyPosFormat=1 will cope with either 1£ or just 1 in a data file.  However, writing to a file with CurrencySymbol=£ and CurrencyPosFormat=1 specified will result in 1£ format for currency output.

I hope this saves someone some heartache.

By the way, I've been unable to get the CurrencyThousandSymbol in schema.ini to work at all.  I've given up trying at the moment.  I'd love to hear if anyone else gets it working.

P.S. It's CurrencyThousandsSymbol not CurrencyThousandSymbol! The Microsoft documentation is wrong! This works for reading files but not writing them. I'll keep looking at this from time to time.  BTW the international thousands separator (a non-breaking space) doesn't seem to work.  Again I'll report back if I find otherwise.

ODBC Text File Driver with Unicode files

I use ODBC links to text files on a regular basis and often have to join files coming from different systems.  Recently I had to join a Unicode file with an ANSI encoded one.  Simple, you’d think, as the scant documentation on the text files ODBC driver does mention dealing with Unicode.  However, the Define Text Format dialog used to configure the ODBC text driver (results stored in schema.ini) has only two radio buttons: ANSI and OEM.  I couldn’t get either to work with the Unicode file and so had to convert it to ANSI format before I could use it.  A real pain.  Then a eureka moment.  Manually edit schema.ini for the Unicode file entry to include the line CharacterSet=UNICODE.  It works! 

After spending ages on this I was frustrated to find a document called "Initializing the Text Data Source Driver" in information relating to Microsoft Office Access 2003.  The discussion appears to be about the Microsoft Jet database engine but it confirms that CharacterSet can also contain UNICODE or the code page number of a specific character set (e.g. 1232)

The relevant document is at:

http://office.microsoft.com/en-gb/access-help/initializing-the-text-data-source-driver-HP001032166.aspx?CTT=5&origin=HP001032168