Reading a fixed length and comma delimited text file

I have this text file that contains rows of data that are fixed length and comma delimited

I know how to read the text file but what I'm looking for is a way to read each line and test the values on certain columns so that I can perform what ever functions I need to based on the column values.

Basically I have certain columns that are flags for certain process. I have to read certain columns first to see what the values are in order to get the final output I need.

Thanks in advance
webedgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fritz_the_blankCommented:
You can use the FSO and the .ReadLine() method:

<%
dim objOpenFile, objFSO, strPath, objCreateFile, strNewFileName
strFile = "test.txt"
set objFSO = Server.CreateObject("Scripting.FileSystemObject")
strPath = server.MapPath(strFile)
set objOpenFile = objFSO.OpenTextFile(strPath,1)
Do while not objOpenFile.AtEndOfStream
      strText = objOpenFile.Readline
      Response.Write(Server.HTMLEncode(strText)& "<br>")
loop
objOpenFile.Close
set objOpenFile= Nothing
set objFSO = Nothing
%>

So now you can process each line.

FtB
HecatonchiresCommented:
Once you've opened the file using fritz's method, have a look at the mid() function if you're going with fixed length, and the split() function if you're going csv (and can assume the data is clean)

Mid Function   Language Reference
Version 1
 
--------------------------------------------------------------------------------

Description
Returns a specified number of characters from a string.
Syntax
Mid(string, start[, length])
The Mid function syntax has these arguments:

Part Description
string String expression from which characters are returned. If string contains Null, Null is returned.  
start Character position in string at which the part to be taken begins. If start is greater than the number of characters in string, Mid returns a zero-length string ("").
length Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned.



Remarks
To determine the number of characters in string, use the Len function.
The following example uses the Mid function to return six characters, beginning with the fourth character, in a string:

Dim MyVar
MyVar = Mid("VB Script is fun!", 4, 6) 'MyVar contains "Script".


--------------------------------------------------------------------------------
 
Note  The MidB function is used with byte data contained in a string. Instead of specifying the number of characters, the arguments specify numbers of bytes.

--------------------------------------------------------------------------------
 


Split Function   Language Reference
Version 2
 
--------------------------------------------------------------------------------

Description
Returns a zero-based, one-dimensional array containing a specified number of substrings.
Syntax
Split(expression[, delimiter[, count[, compare]]])
The Split function syntax has these parts:

Part Description
expression Required. String expression containing substrings and delimiters. If expression is a zero-length string, Split returns an empty array, that is, an array with no elements and no data.

delimiter Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.

count Optional. Number of substrings to be returned; -1 indicates that all substrings are returned.

compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.


Settings
The compare argument can have the following values:
Constant Value Description
vbBinaryCompare 0 Perform a binary comparison.
vbTextCompare 1 Perform a textual comparison.


Remarks
The following example uses the Split function to return an array from a string. The function performs a textual comparison of the delimiter, and returns all of the substrings.
Dim MyString, MyArray
MyString = Split("VBScriptXisXfun!", "x", -1, 1)
' MyString(0) contains "VBScript".
' MyString(1) contains "is".
' MyString(2) contains "fun!".


--------------------------------------------------------------------------------
justinbilligCommented:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp


The microsoft jet engine allows you to parse text files using ado. You can literally use select statements on a text file.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

kiddangerCommented:
Reading a file, line by line, is a slow process.  Disk access is much slower than evaluating/manipulating data in RAM.

If you're going to process the whole file, read it all at once.  You can still split the CSV into an array so it can be indexed.

This reads the file in all at once and puts each line as an element into an array.
arrText = split(f.ReadAll,vbCrLf)

Then to manipulate the fields of each line, split it again.

for i = 0 to ubound(arrText)
  arrLine = split(arrText(i),",")
  ' process the array
  for j = 0 to ubound(arrLine)
    f = arrLine(j) ' first field = 0, second = 1, etc.
  next
next
webedgeAuthor Commented:
Justin - I started to use that method, I will be testing that today.

Kid - I like that, so after I split it again can I test for field values or do I have to wait until the array is loaded then reloop?
webedgeAuthor Commented:
Ok, here is my code. When i run the pages its like its in a continious loop, no output.

<%@LANGUAGE="VBSCRIPT"%>

<%
Server.ScriptTimeout = 86400
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = Server.CreateObject("ADODB.Connection")
Set objRecordSet = Server.CreateObject("ADODB.Recordset")

strPathtoTextFile = Server.Mappath("uploaded/austin.csv")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoTextFile & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""

objRecordset.Open "SELECT * FROM austin.csv", objConnection, adOpenStatic, adLockOptimistic, adCmdText

response.write "<table cellspacing=1 cellpadding=5 border=0>"
count = 0

Do While Not objRecordset.EOF
    response.write "<tr>"
      for each field in objRecordset.Fields
      response.write "<td>" & field.Value & "</td>"
      next
      response.write "<tr>"
      count = count + 1
    objRecordset.MoveNext
Loop

objRecordset.Close
set objRecordset = Nothing
set objConnection = Nothing
response.write count
response.write "</table>"
%>
webedgeAuthor Commented:
Let me also say that my text file that is being read is a fixed length, comma seperated, qoute encapsulated file.

here is some data from my file

934377   ,6232005   ,"AD","3998717     ","TR","RUEDAS, RODOLFO                    ",
justinbilligCommented:
strPathtoTextFile = Server.Mappath("uploaded/austin.csv")


should be

strPathtoTextFile = Server.Mappath("uploaded/")

the path to the text file can't include the text file
webedgeAuthor Commented:
I'm such a bone head, thanks justin. Your was is so much faster, I did redeveloped it another way but  I will continute to use the ADO way, alot , alot faster. I have a couple of other things i need it to do but...... I will be testing this today and tomorrow.

check this out guys,

this is the other way i was talking about, it reads the text file then imports into the db, i will eventually do this using the ADO but since I'm a bone head and didnt catch justins remark in time, i developed this.

<%@LANGUAGE="VBSCRIPT"%>
<%
Server.ScriptTimeout = 86400
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

textfile = Server.Mappath("uploaded/austin.csv")

Set objConnection = Server.CreateObject("ADODB.Connection")
Set objRecordSet = Server.CreateObject("ADODB.Recordset")
set fso = Server.CreateObject("Scripting.FileSystemObject")
set stream = fso.OpenTextFile(textfile,1)

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.Mappath("db/austin.mdb") & ";"

objRecordset.Open "SELECT * FROM importaustin", objConnection, adOpenStatic, adLockOptimistic, adCmdText

With objRecordset
      strFieldCount = .fields.count - 1
      redim strArr(strFieldCount, 3)
            
            For i = 0 to strFieldCount
                  strArr(i, 0) = .Fields(i).Name
                  strArr(i, 1) = .Fields(i).DefinedSize
                  strArr(i, 2) = .Fields(i).Type                  
            Next
      
            Do While Not stream.AtEndOfStream
                  TextLine = stream.ReadLine                  
                  TextLine = replace(TextLine, """", "")
                  TextLine = replace(TextLine, ",", "")
                  .AddNew
                  For i = 0 To strFieldCount                        
                           TextField = Mid(TextLine, 1, strArr(i, 1))                           
                        .Fields(i) = TextField                        
                           TextLine = Mid(TextLine, strArr(i, 1) + 1)      
                  Next                  
                  .Update
            Loop
End With
Set stream = nothing
Set fso = nothing
objRecordset.Close
set objRecordset = Nothing
set objConnection = Nothing

%>
kiddangerCommented:
<%@ Language=VBScript %>
<%
Option Explicit
Response.Buffer = True
Server.ScriptTimeout = 86400
'On Error Resume Next

sub prt(str)
  Response.Write str & vbCrLf
end sub
sub lprt(str)
  Response.Write str & "<br />" & vbCrLf
end sub

function readfile(file)
  Const ForReading = 1
  dim fso, f
  file = server.MapPath(file)
  set fso = CreateObject("Scripting.FileSystemObject")
  if fso.FileExists(file) then
    set f = fso.OpenTextFile(file,ForReading)
    readFile = split(f.ReadAll,vbCrLf)
    f.close
    set f = nothing
  end if
  set fso = nothing
end function

prt "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">"
prt "<html>"
prt "<body>"
prt "<table cellspacing=""1"" cellpadding=""5"" border=""0"">"

dim count, i, j, arrLine, arrText
arrText = readfile("/uploaded/austin.csv")
count = ubound(arrText)
for i = 0 to ubound(arrText)
  arrLine = split(readFile(arrText,",")
  prt "<tr>"
  for j = 0 to ubound(arrLine)
    prt "<td>" & arrLine(i) & "</td>"
  next
  prt "</tr>"
next
prt "</table>"
lprt count
prt "</body>"
prt "</html>"
%>
kiddangerCommented:
If you're going to use a recordset, getString is more efficient and performs better than recordset looping.

<%@ Language=VBScript %>
<%
Option Explicit
Response.Buffer = True
Server.ScriptTimeout = 86400

sub prt(str)
  Response.Write str & vbCrLf
end sub

dim conn, rs, strPath
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
strPath = Server.Mappath("uploaded/")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""
rs.Open "SELECT * FROM austin.csv", conn, adOpenStatic, adLockOptimistic, adCmdText

prt "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">"
prt "<html>"
prt "<body>"
prt "<table cellspacing=""1"" cellpadding=""5"" border=""0"">"
dim strTable : strTable = rs.GetString(,,"</td><td>","</td></tr><tr><td>","&nbsp;")
rs.Close
set rs = Nothing
conn.Close
set conn = Nothing
prt "<tr><td>"
prt strTable
prt "</td></tr>"
prt "</table>"
prt "</body>"
prt "</html>"
%>

Ex: http://kiddanger.com/lab/ee/q_21482694gs.asp
CSV: http://kiddanger.com/csv/dummy.csv
kiddangerCommented:
webedge...

To answer your question earlier... when you use split, it creates an array with that statement.  There is nothing to wait for.
webedgeAuthor Commented:
kid, i like your style. Sorry I have not responded all weekend, I got into a car wreck on Thursday night. Not hurt!

Anyways, my next step to this is going to be to build a DB table on the fly based on what I read from the table. I would like it to read my text file and based on the delimiter and fixed length it will build my db fields accordingly.

code i have so far just writes my text file to the screen in a table. next step would be to obviously loop and build rather than print to the screen.
<%@LANGUAGE="VBSCRIPT"%>
<%
Server.ScriptTimeout = 86400
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = Server.CreateObject("ADODB.Connection")
Set objRecordSet = Server.CreateObject("ADODB.Recordset")

'strPathtoTextFile = "C:\Databases\"
strPathtoTextFile = Server.Mappath("uploaded/")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoTextFile & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""

objRecordset.Open "SELECT * FROM austin.csv", objConnection, adOpenStatic, adLockOptimistic, adCmdText

response.write "<table cellspacing=1 cellpadding=5 border=0>"
count = 0

Do While Not objRecordset.EOF
    response.write "<tr>"
      for each field in objRecordset.Fields
      response.write "<td>" & field.Value & "</td>"
      next
      response.write "<tr>"
      count = count + 1
    objRecordset.MoveNext
Loop

objRecordset.Close
set objRecordset = Nothing
set objConnection = Nothing
'response.write count

response.write "</table>"
%>

kiddangerCommented:
Sorry to hear about your accident.  Glad you're not hurt.

It appears you've chosen to use recordset looping.  If you build a db table on the fly, you're only running this once.  Did you say which database you're using?
webedgeAuthor Commented:
well, the only reason that i did it this way is just to see how fast it could read the text file.

basically the process for this app goes as follows.

read a text file, import into db table, re read the data based on various functions, write 4 different text files with various data from the functions.

thats it
kiddangerCommented:
It doesn't matter to me which method you use.  It only matters to me when it's my code.  (O:=

Thanks for the general overview.  Can you elaborate on "read the data based on various function" and why is it necessary to store it in a database if you're going to produce 4 files from the original input?  Perhaps more detail is needed for me to get a good understanding of the specifics.

webedgeAuthor Commented:
ok, im going to first give you the data

file that is being read is a fixed length, comma seperated, qoute string encapsulated file.

994377   ,6232005   ,"AD","3233d17     ","TR","RUEDAS, RODOLFO                    ","Y"
934237   ,6232005   ,"PA","3998d317     ","TR","jim, smith                                 ","Y"
334377   ,6232005   ,"WD","3998d17     ","TR","Rachel, cruz                              ","  "

there are over 800 records but im going to just give you three

1st thing i need to do is ......(this part is not complete, i just the specs last night)
find all account numbers that are the same, once found then check to see if the 3rd field is either a PA or WD, if we have a duplicate accounts but one with a PA value and a WD value, delete the PA value. - I need the logic on this, what are your thoughts at this point?

THE REASON WHY I CHOSE THE ADO READ TEXT METHOD IS BECAUSE, IT HAS ALL THE BELLS AND WHISTLES FOR HANDELING ANY FILE TYPE I THROW AT IT AND I WILL NOT HAVE TO MAKE SEPEARATE FUNCTIONS FOR STRIPING.

2nd.....the 1st has to be done....


webedgeAuthor Commented:
this is what i have as far as getting the data into a table, and yes its important that i delete any previouse data that was in the table.

<%@LANGUAGE="VBSCRIPT"%>
<%
'Server.ScriptTimeout = 86400
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set strConnection = Server.CreateObject("ADODB.Connection")
Set rsAustinTextFile = Server.CreateObject("ADODB.Recordset")
Set rsAustin = Server.CreateObject("ADODB.Recordset")

'strPathtoTextFile = "C:\Databases\"
strPathtoTextFile = Server.Mappath("uploaded/")

strConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoTextFile & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""
rsAustinTextFile.Open "SELECT * FROM austin.csv", strConnection, adOpenStatic, adLockOptimistic, adCmdText

'DELETE ALL PREVIOUS ENTRIES
rsAustin.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.Mappath("db/austin.mdb") & ";"
rsAustin.Source = "Delete * FROM ImportAustin"
rsAustin.CursorType = 1
rsAustin.CursorLocation = 2
rsAustin.LockType = 2
rsAustin.Open
for i = 0 to 100000
      'Just to buy some time
next
rsAustin.close
'END DELETE

rsAustin.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.Mappath("db/austin.mdb") & ";"
rsAustin.Source = "SELECT * FROM ImportAustin where 0 = 1"
rsAustin.CursorType = 1
rsAustin.CursorLocation = 2
rsAustin.LockType = 2
rsAustin.Open
strFieldCount = rsAustinTextFile.fields.count
Do While Not rsAustinTextFile.EOF            
      if strFieldCount <> 36 then
            response.write "This text file does not contain 36 fields."
            response.write strFieldCount
            exit Do
      else
            rsAustin.AddNew      
            rsAustin.fields("personid")             = mid(rsAustinTextFile("F1"),1,9)
            rsAustin.fields("curr_date")       = mid(rsAustinTextFile("F2"),1,10)
            rsAustin.fields("trans_code")       = mid(rsAustinTextFile("F3"),1,2)
            rsAustin.fields("case_num")             = mid(rsAustinTextFile("F4"),1,12)
            rsAustin.fields("case_type")       = mid(rsAustinTextFile("F5"),1,2)
            rsAustin.fields("def_name")             = mid(rsAustinTextFile("F6"),1,30)
            rsAustin.fields("def_add1")             = mid(rsAustinTextFile("F7"),1,30)
            rsAustin.fields("def_add2")             = mid(rsAustinTextFile("F8"),1,30)
            rsAustin.fields("def_city")             = mid(rsAustinTextFile("F9"),1,16)
            rsAustin.fields("def_state")       = mid(rsAustinTextFile("F10"),1,2)
            rsAustin.fields("def_phone")       = mid(rsAustinTextFile("F11"),1,10)
            rsAustin.fields("def_zip")             = mid(rsAustinTextFile("F12"),1,5)      
            rsAustin.fields("dl_num")             = mid(rsAustinTextFile("F13"),1,24)
            rsAustin.fields("dl_state")             = mid(rsAustinTextFile("F14"),1,2)
            rsAustin.fields("def_dob")             = mid(rsAustinTextFile("F15"),1,10)
            rsAustin.fields("ss_num")             = mid(rsAustinTextFile("F16"),1,11)
            rsAustin.fields("empl_name")       = mid(rsAustinTextFile("F17"),1,30)
            rsAustin.fields("empl_addr")       = mid(rsAustinTextFile("F18"),1,30)
            rsAustin.fields("empl_phone")       = mid(rsAustinTextFile("F19"),1,10)
            rsAustin.fields("ttl_due")             = mid(rsAustinTextFile("F20"),1,11)
            rsAustin.fields("ttl_paid")             = mid(rsAustinTextFile("F21"),1,11)
            rsAustin.fields("bal_due")             = mid(rsAustinTextFile("F22"),1,11)
            rsAustin.fields("issue_date")       = mid(rsAustinTextFile("F23"),1,10)
            rsAustin.fields("viol_desc")       = mid(rsAustinTextFile("F24"),1,30)
            rsAustin.fields("viol_place")       = mid(rsAustinTextFile("F25"),1,30)
            rsAustin.fields("plate_num")       = mid(rsAustinTextFile("F26"),1,10)
            rsAustin.fields("plate_st")             = mid(rsAustinTextFile("F27"),1,2)
            rsAustin.fields("plate_exp")       = mid(rsAustinTextFile("F28"),1,7)
            rsAustin.fields("vehi_make")       = mid(rsAustinTextFile("F29"),1,6)
            rsAustin.fields("vehi_model")       = mid(rsAustinTextFile("F30"),1,6)
            rsAustin.fields("vehi_color")       = mid(rsAustinTextFile("F31"),1,3)      
            rsAustin.fields("conviction")       = mid(rsAustinTextFile("F32"),1,1)
            rsAustin.fields("status_cod")       = mid(rsAustinTextFile("F33"),1,2)
            rsAustin.fields("stats_date")       = mid(rsAustinTextFile("F34"),1,10)
            rsAustin.fields("bad_addr")             = mid(rsAustinTextFile("F35"),1,1)
            rsAustin.fields("adjudicated")       = mid(rsAustinTextFile("F36"),1,1)
            rsAustin.update
      end if
      rsAustinTextFile.MoveNext            
Loop
rsAustinTextFile.Close
rsAustin.close
set rsAustinTextFile = Nothing
set rsAustin = Nothing
set strConnection = Nothing
%>

kiddangerCommented:
If the first field the account number?
kiddangerCommented:
If you're going to use a recordset, did you create a schema.ini file yet?
kiddangerCommented:
You do not appear to be using the MID() function correctly.
Mid(string, start[, length])

webedgeAuthor Commented:
kid,
1st question - actually the 4th field is the account number
2nd question - yes, i have my schema.ini file in place
3rd question - mid is working, i have to cut off some of the data because when i add it to the DB the fields in the DB have a certain amount of space for the next step...after importing into the db, i take that data in the db and export it out into 4 text files that will then be imported into our collection system. our collection system can only take certain data types and field sizes plust the 4 files correspond to various account types, payment records etc that are in our collection system. I wish our collection system was ODBC compliant but its not, no way to work around this!

got it so far? it may be a little confusing but it will be a sweet app.
kiddangerCommented:
so case_num is the account code and you need all the fields you have listed?
Can you show me your schema.ini file?
webedgeAuthor Commented:
you got it!

[austin.csv]
Format=CSVDelimited
ColNameHeader=False
MaxScanRows=0
CharacterSet=OEM
kiddangerCommented:
This is what I am testing with:

[austin.csv]
Format=CSVDelimited

Col1=personid Text
Col2=currDate DateTime
Col3=transCode Text
Col4=caseNum Text
Col5=caseType Text
Col6=defName Text
Col7=unknown Text

Just to verify, account = case_num?
Can you fill in the rest of the fields for me like I'm doing in the schema.ini?
webedgeAuthor Commented:
sweet, i see where you are going with this. I will update you first thing tomorrow. So rather than doing my method you would rather use the field defenitions in the schema file?
kiddangerCommented:
Well, doin' it this way is new to me so I'm not sure if I need the all.  I'm first selecting the account field to get a list of all the account codes.  I thought personid was the account code, since nothing is called account.  I'm still looking for verification that case_num is the account code and then I'll have something to test soon after that.
kiddangerCommented:
That article said if there is no header line, then we need a schema.ini file so perhaps it needs all of the fields.  It's probably a good idea anyway.
webedgeAuthor Commented:
well, you can actually do it without all the columns in the schema file or you can add them.

case_num is the account code
webedgeAuthor Commented:
Did you still need me to fill out the schema file? the reason why i didnt fill it out is because im doing it the other way.
kiddangerCommented:
The way I read it, you can either create a header record or you need a schema.ini file.  If you have a header record you need to change your connection string so ADO knows to use the first record for the headers.  Since you have chosen HDR=NO, then we need the schema.ini file to tell ADO which field is which.

You should never use selstar in production code.  Fields should be called by name.
Ex. This is good for testing but bad for production code.

SELECT * FROM tablename ...

Preferred:

SELECT FIELD1, FIELD2, ... FROM tablename ...
webedgeAuthor Commented:
exactly, I know about the * thing i just need to get something dirty working and I will clean up at the end.
kiddangerCommented:
I would like to have a full record and the full schema.ini.
webedgeAuthor Commented:
ouch, give me your email and I will shoot over a text file with a complete record
kiddangerCommented:
I found out GetString seems to have a bug but I got past it.  It returns an extra blank record at the end.

You can go here and leave me your contact information and I'll email you.
http://kiddanger.com/asp/contacts.asp
webedgeAuthor Commented:
copy and past into notepad

934377   ,6232005   ,"AD","3998717     ","TR","RUEDAS, RODOLFO                    ","6807 SUENA ST                 ","                              ","AUSTIN          ","TX","78741","5123890822","01704866                 ","TX",3071963   ,461159312  ,"                                   ","                                                  ","          ",331.50     ,.00        ,331.50     ,3091998   ,"FAIL TO MAINTAIN FINANCIAL RESP         ","MONTOPOLIS & PONCA                      ","VRY92F    ","TX",398   ,"CHEV  ","      ","BLU"," ","N ",0         ," ","Y"

that is one complete row of data

Did you get my contact info?
kiddangerCommented:
I just got it.  That page saves the info in an XML file and emails me the contact information.  I just sent you an email.
kiddangerCommented:
Please let me know if you're finished testing so I can post the code here.
webedgeAuthor Commented:
Ok, i have really tweaked this

I have this in steps now..

Step 1 - upload file to the server - then rediredt to step 2 page sending the file name with it
Step 2 - get the file name - and do the following...

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set strConnection = Server.CreateObject("ADODB.Connection")
Set rsAustinTextFile = Server.CreateObject("ADODB.Recordset")
Set rsAustin = Server.CreateObject("ADODB.Recordset")

'strPathtoTextFile = "C:\Databases\"
strPathtoTextFile = Server.Mappath("uploaded/")

strConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoTextFile & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""
rsAustinTextFile.Open "SELECT * FROM " & request("FileName"), strConnection, adOpenStatic, adLockOptimistic, adCmdText

'DELETE ALL PREVIOUS ENTRIES
rsAustin.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.Mappath("db/austin.mdb") & ";"
rsAustin.Source = "Delete * FROM tbl_Import_Austin"
rsAustin.CursorType = 1
rsAustin.CursorLocation = 2
rsAustin.LockType = 2
rsAustin.Open

for i = 0 to 10000
      'Just to buy some time
next
rsAustin.close
'END DELETE
'-------------------------------------------------------------------------------------------------------------------------
'IMPORT TEXT FILE DATA INTO THE DB FOR TEMPORARY STORAGE
rsAustin.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.Mappath("db/austin.mdb") & ";"
rsAustin.Source = "SELECT * FROM tbl_Import_Austin where 0 = 1"
rsAustin.CursorType = 1
rsAustin.CursorLocation = 2
rsAustin.LockType = 2
rsAustin.Open
strFieldCount = rsAustinTextFile.fields.count
Do While Not rsAustinTextFile.EOF            
      if strFieldCount <> 36 then
            response.write "This text file does not contain 36 fields."
            response.write strFieldCount
            exit Do
      else
            rsAustin.AddNew      
            rsAustin.fields("personid")             = mid(rsAustinTextFile("F1"),1,9)
            rsAustin.fields("curr_date")       = mid(rsAustinTextFile("F2"),1,10)
            rsAustin.fields("trans_code")       = mid(rsAustinTextFile("F3"),1,2)
            rsAustin.fields("case_num")             = mid(rsAustinTextFile("F4"),1,12)
            rsAustin.fields("case_type")       = mid(rsAustinTextFile("F5"),1,2)
            rsAustin.fields("def_name")             = mid(rsAustinTextFile("F6"),1,30)
            rsAustin.fields("def_add1")             = mid(rsAustinTextFile("F7"),1,30)
            rsAustin.fields("def_add2")             = mid(rsAustinTextFile("F8"),1,30)
            rsAustin.fields("def_city")             = mid(rsAustinTextFile("F9"),1,16)
            rsAustin.fields("def_state")       = mid(rsAustinTextFile("F10"),1,2)
            rsAustin.fields("def_phone")       = mid(rsAustinTextFile("F11"),1,10)
            rsAustin.fields("def_zip")             = mid(rsAustinTextFile("F12"),1,5)      
            rsAustin.fields("dl_num")             = mid(rsAustinTextFile("F13"),1,24)
            rsAustin.fields("dl_state")             = mid(rsAustinTextFile("F14"),1,2)
            rsAustin.fields("def_dob")             = mid(rsAustinTextFile("F15"),1,10)
            rsAustin.fields("ss_num")             = mid(rsAustinTextFile("F16"),1,11)
            rsAustin.fields("empl_name")       = mid(rsAustinTextFile("F17"),1,30)
            rsAustin.fields("empl_addr")       = mid(rsAustinTextFile("F18"),1,30)
            rsAustin.fields("empl_phone")       = mid(rsAustinTextFile("F19"),1,10)
            rsAustin.fields("ttl_due")             = mid(rsAustinTextFile("F20"),1,11)
            rsAustin.fields("ttl_paid")             = mid(rsAustinTextFile("F21"),1,11)
            rsAustin.fields("bal_due")             = mid(rsAustinTextFile("F22"),1,11)
            rsAustin.fields("issue_date")       = mid(rsAustinTextFile("F23"),1,10)
            rsAustin.fields("viol_desc")       = mid(rsAustinTextFile("F24"),1,30)
            rsAustin.fields("viol_place")       = mid(rsAustinTextFile("F25"),1,30)
            rsAustin.fields("plate_num")       = mid(rsAustinTextFile("F26"),1,10)
            rsAustin.fields("plate_st")             = mid(rsAustinTextFile("F27"),1,2)
            rsAustin.fields("plate_exp")       = mid(rsAustinTextFile("F28"),1,7)
            rsAustin.fields("vehi_make")       = mid(rsAustinTextFile("F29"),1,6)
            rsAustin.fields("vehi_model")       = mid(rsAustinTextFile("F30"),1,6)
            rsAustin.fields("vehi_color")       = mid(rsAustinTextFile("F31"),1,3)      
            rsAustin.fields("conviction")       = mid(rsAustinTextFile("F32"),1,1)
            rsAustin.fields("status_cod")       = mid(rsAustinTextFile("F33"),1,2)
            rsAustin.fields("stats_date")       = mid(rsAustinTextFile("F34"),1,10)
            rsAustin.fields("bad_addr")             = mid(rsAustinTextFile("F35"),1,1)
            rsAustin.fields("adjudicated")       = mid(rsAustinTextFile("F36"),1,1)
            rsAustin.update
      end if
      rsAustinTextFile.MoveNext            
Loop
rsAustinTextFile.Close
rsAustin.close
set rsAustinTextFile = Nothing
set rsAustin = Nothing
set strConnection = Nothing

'-------------------------------------------------------------------------------------------------------------------------
'DELETE ALL PA'S WITH A CASE_NUM THAT ARE DUPLICATES BUT HAS A TRANS_CODE OF WA, WT, WS, WD
Set rsAustin = Server.CreateObject("ADODB.Recordset")
rsAustin.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.Mappath("db/austin.mdb") & ";"
rsAustin.Source = "DELETE * FROM tbl_Import_Austin WHERE trans_code = 'PA' AND exists ( SELECT * FROM tbl_Import_Austin S WHERE S.case_num = tbl_Import_Austin.case_num AND S.trans_code IN ( 'WA', 'WT', 'WD', 'WS'));"
rsAustin.CursorType = 1
rsAustin.CursorLocation = 2
rsAustin.LockType = 2
rsAustin.Open
rsAustin.close
set rsAustin = Nothing
response.redirect "MSB_AFR_Step3.asp?Step=3"

step 3 - ......
kiddangerCommented:
I'm not sure how to respond to that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.