scriptygoddess

1 | Marcus

November 10th, 2004 at 12:10 pm

Depending on how long the lookup list is going to be, you could always save the excel file in a CSV format and use ADODB and the text driver ("Driver={Microsoft Text Driver (*.txt; *.csv)}") and even run a normal sql query on it. I.e. "SELECT thetext, thenumber FROM lookupfile.csv WHERE thetext = 'text_you_entered'". I used this for a few simple web sites where clients wanted to display their inventory and update it with ease. They just upload a new CSV file to the web server.

2 | Marcus

November 12th, 2004 at 4:33 pm

Just inquiring whether you got my email reply and if the Text Driver query method worked for ya.

3 | Jennifer

November 12th, 2004 at 4:59 pm

Marcus – no I didn't get your email. I couldn't get this to work – because the only thing I was able to do was SELECT * from csvfile.csv … I couldn't seem to get it to select a single row of data using a "WHERE field = 'value'"… it just didn't seem to recognize the field names… If you know of a way to do that – PLEASE email me!!! 😀
scripty at scriptygoddess dot com

4 | Marcus

November 13th, 2004 at 4:17 pm

I re-sent the email. There's also an article here. Even though that one's for Java, the text driver stuff is the same.

5 | Jennifer

November 16th, 2004 at 4:11 pm

I still couldn't get it to select a specific row based on a "WHERE x=y"… I ended up using a simpler connection script I found here

and then, instead of spitting out all the data – I just did

if rs(3) = "243" then
(PRINT THE LINE…)
end if

And actually, ran into some complications because the data in the rs(x) was assumed to be a number, and I was comparing it to a (number-)string. So in order to "convert" the rs(x) into a STRING, I had to do this:

Dim strNum
strNum = rs(3) & ""

then I could do this:

if strNum = "1234" then
(PRINT LINE)
end if

Probably not terrible efficient technically – but thankfully the csv isn't terribly huge, so iterating through each line doesn't take more than a blink of an eye.

6 | Jennifer

December 6th, 2004 at 10:13 am

(note to self – and others dealing with this issue too) With some additional help from Marcus, I was able to get it to select specific columns – as well as fix some continuing issues with the fields being cast as numbers – when I needed it cast as a string (leading zeros get stripped when it casts the field as a number)

The trick was creating a Schema.ini file in the same directory as the csv file. There I could set the column names (and call those column names from my asp file) as well as set the length and type of the fields. More info on Schema.ini files here.

Thank you Marcus!!!!