On Tue, 2 Feb 2010, Chris wrote: > I'm having some trouble getting data into gretl through ODBC. I have a > fairly large sql statement, so I'm importing it from a file. When I run the > data command I get an error and it halts. Here's the command script: > nulldata 160 > setobs 5 2001:1 --time-series > open dsn=guestCalgary user=####### password=##### --odbc > string QRY1 = readfile("C:/gretl/maxbufftemp.txt") > data maxbufftemp obs-format-"%d" @QRY1 --odbc > > gives me this error: > gretl version 1.8.7 > Current session: 2010-02-02 14:34 > ? nulldata 160 > periodicity: 1, maxobs: 160, > observations range: 1-160 > ? setobs 5 2001:1 --time-series > Full data range: 2001 - 2160 (n = 160) > > ? open dsn=guestCalgary user=##### password=#### --odbc > Connected to ODBC data source 'guestCalgary' > ? string QRY1 = readfile("C:/gretl/maxbufftemp.txt") > Replaced string QRY1 > ? data maxbufftemp obs-format-"%d" @QRY1 --odbc > > Error executing script: halting > > data maxbufftemp obs-format-"%d" @QRY1 --odbc The syntax here is "obs-format=fmt". That is, an equals sign rather than a hyphen/dash comes after "obs-format". One question about your dates setup: setobs 5 2001:1 --time This is rather unusual. With frequency 5 one would expect daily data, and therefore something like setobs 5 2001/01/02 Allin Cottrell _______________________________________________ Gretl-users mailing list Gretl-users(a)lists.wfu.edu http://lists.wfu.edu/mailman/listinfo/gretl-users

From reading the manual that bit was there because I am importing 2 columns,

On Wed, 3 Feb 2010, Chris wrote: > Thanks for the help Allin, > > That was just my own lack of understanding of how to use that command. So > now, thanks to your help, I have it working. The ODBC command executes and > it pulls 217 observations, which is the same number I would expect from > running the sql query in query analyzer. However, now that it's in gretl, > when I try to view the dat I get an "maxbufftemp: no valid values" error, > however, when I look at the data in query analyzer It's there (example piece > of data from query analyzer: 39.900" I think the problem is that you're using obs-format="%d" which is designed for integer data, but with values like 39.900 you actually have floating-point data. Try dropping the format string: data maxbufftemp @QRY1 --odbc Allin Cottrell _______________________________________________ Gretl-users mailing list Gretl-users(a)lists.wfu.edu http://lists.wfu.edu/mailman/listinfo/gretl-users

On Wed, 3 Feb 2010, Chris wrote: > From reading the manual that bit was there because I am > importing 2 columns, the first one being an identifier. When I > remove the first column from my select statement I can import > data from both my big query and my test query, but all the data > shows up as "5.16957e-38" not the data that shows up when I run > the same query in query analyzer. * What does the identifier look like? * 5.16957e-38 looks like one of the limits of a single-precision "float". Gretl binds the last (data) column to SQL_C_DOUBLE. But somehow we're getting the wrong data type? Perhaps the values in this db are stored as SQL_REAL (which has a mantissa of precision only 7). What's the database engine? Allin Cottrell _______________________________________________ Gretl-users mailing list Gretl-users(a)lists.wfu.edu http://lists.wfu.edu/mailman/listinfo/gretl-users

On Wed, 3 Feb 2010, Chris wrote: > It should be an integer string that is the date without the / in > them so feb 1 , 2007 stored as 20070201. In the case where I > can import data into gretl I'm not even selecting that column > though. > > The particular bit of data I'm pulling is stored as numeric(7,3) > in an mssql server 2008. I believe we can read that as numeric > precision 7, numeric scale 3 > For the small and simple test query I was using the data is stored as > numeric(16,3) in the same sql server. Thanks for persevering! We're making some progress -- you've given me some ideas for making the manual more explicit. As to the specifics: First, on the "obs-format" business: the point of that is if you want to screen the values coming in via ODBC and add only some of them to the dataset. But if your identifiers look like "20070201" and you use "%d" to grab the whole integer value, then _all_ the ODBC values will be screened out (leaving nothing but missing values or NAs in the input series). That's because a string like "20070201" will not match any gretl observation identifier. Note that the gretl obs identifiers are what you see in the leftmost column if you do "print index --byobs" after doing "nulldata" and "setobs". So in this case it looks as if you want a straight one-column import. And I need to research "numeric(7,3)" in mssql and see what I can find. My first thought is that since gretl is asking for SQL_C_DOUBLE, it's the server's fault that it's _not_ giving gretl the values in the C double-precision format. But I'm not an SQL expert and maybe it's not that simple. Jack, any thoughts? Allin Cottrell _______________________________________________ Gretl-users mailing list Gretl-users(a)lists.wfu.edu http://lists.wfu.edu/mailman/listinfo/gretl-users

On Wed, 3 Feb 2010, Chris wrote: > That straightens out some things for me. > http://www.servers.net.in/Servers%20Archive-odbc%20sqlserver%20public%20m... > I suspect that is the reason this isn't working. If you have any suggestions > on how to get around this I would appreciate it. Ah, that reference is helpful. We'll see if we can find a way to work around the bug in question. But I think that has to be in the bowels of gretl's ODBC-import code; I don't think there's any way to work around it at the level of user input. Allin Cottrell _______________________________________________ Gretl-users mailing list Gretl-users(a)lists.wfu.edu http://lists.wfu.edu/mailman/listinfo/gretl-users

On Wed, 3 Feb 2010, Chris wrote: > That's ok. It's about time for a Microsoft product to screw me > over again; I was just starting to think Microsoft products were > decent and maybe I was wrong to be a linux user at home for the > last decade;) Well, I don't want to start a flamefest here, but I don't see how one could possibly be wrong to use linux.

> As a side note, do you know if anyone is working on updating the > import features to allow multiple variable imports? Nobody's working on that, that I know of. But it's not too hard to replicate your query string with modification so as to grab as many variables as you need.