error reading comma-delimited ip address

read comma delimited text ip address asp

[mahaleleel]

I'm trying to read an IP address from a comma-delimited text file into an ASP page using VBScript. My code works for EVERYTHING in the text file except the IP Address.

The csv output file is from my web server. I created a comma-delimited text file list of all my company's websites using Internet Services Manager.

Here is an example of a bit of the output:

Description,IP Address,Status
helloworld.com,255.255.255.255,Running
testsite.com,244.244.244.244,Running
nowwellknow.com,233.233.233.233,Running

This is in a file called iisinfo.csv.

Here is the code that's calling the information:

'whenever working with comma-delimited text you have to
'direct the server to the FOLDER since it reads each text
'file as it would a table
connDBpath = Server.MapPath("../data/")
set connTXTdb = Server.CreateObject("ADODB.Connection")
  connTXTdb.open("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & connDBpath & ";Extensions=asc,csv,tab,txt;")
    sql = "SELECT `IP Address` FROM iisinfo.csv"
    set rs = Server.CreateObject("ADODB.RecordSet")
    rs.open sql, connTXTdb, adOpenForwardOnly, adLockReadOnly, adCmdText
     'here is where the problem lies:
     Response.Write(rs("IP Address") & "<br />")
    rs.close
    set rs = Nothing
  connTXTdb.close
set connTXTdb = Nothing

If I call rs("Description") it displays fine, but when I call rs("IP Address") it displays like this: 255.2552

When I check the VarType ( VarType(rs("IP Address")) )it says it is Currency (that's VarType 6). If I try to change the VarType using Cstr ( Cstr(rs("IP Address")) to change it to a string) the VarType changes to 8 (a String), but it still displays the IP Address the same way.

This seems to mean: VBScript is determining the variable type and reading it in that way before I can display it or change it.

So my question is: Is there any way to set the variable type to String in the sql statement or has anyone found (or can anyone find) another fix for pulling the IP address in?

Thanks for the efforts!
- mahaleleel

[aspdaddy]

If you output IP addresses in quotes they will be treated as strings. Or try SELECT replace(IP,"","")

[mahaleleel]

I don't really have any say in how this thing exports. That's my entire problem. My preferences begin and end with choosing .csv or .txt. file format. A csv file is comma delimited, txt is tab delimited, but Jet won't read the tab delimited at all so that further limits my options.

The .csv file I create looks just like this:

Description,IP Address,Status
helloworld.com,255.255.255.255,Running
testsite.com,244.244.244.244,Running
nowwellknow.com,233.233.233.233,Running

Thanks for the help so far.

[plumsauce]

if you cannot change the format of the file,
then try reading each line as a complete string
and reparse it yourself in code.

[aspdaddy]

What about MS Logparser component or the Excel driver - both can read csv using SQL

[mahaleleel]

plumsauce, that might be a good idea. I might try that.

aspdaddy, can you expound? What is the logparser? I'm not farmiliar with that... and I tried connecting to an Excel file because some of these were coming in as excel files originally, but I gave up on it at some point.

I think I gave up on it because I need to open this thing as a database or somehow have the strings split in a way I so that I can write them to a database. That is my ultimate goal. I couldn't find a way to open an excel file and separate the parts as strings.

I'm opening about 5 .csv files and writing each to the database with a fairly complex script. Since they're all csv I'm using selects to get filenames and column names and everything then looping through one file at a time and writing to the database... so it would probably be best if I could use the same code for this file as I use for all the others... but if I can't find another way I might can do what plumsauce suggested just to get the ip addy out of this file.

- m

[aspdaddy]

Logparser is a free dll that you can use to read csv files using extended SQL. It also reads multiple files with one SQL statement:

Set objRS = objLogParser.Execute("select ip, reversedns(ip) FROM *.csv")

Its freely available from m/s, just search Google for more info.

Excel driver gives similar functionality using ADODB, and *will* open a csv file as a database.

Reading the lines as strings is easy enough using split() function, but you cant use SQL to parse out the ip addresses.

Have you tried modifying the Text Driver settings at all, leaving out the (*.txt;) and all the other file extentions except .csv? Maybe this will force it to use a comma as the end-of-text indicator

[dschumann]

aspdaddy,

If I am not mistaken each line in a csv file ends in a CrLf not a comma.