FSO & local PC help 2

scribbler · Aug 5, 2005

Please can anyone tell me if it is possible to read a file on my local pc and use it's information to populate a database on the server ?

I have used fso to read files Ive uploaded onto the server but hoped to save the hassle of uploading large files and then running thro' them to read the conents for a 2nd time.

DotNetGnat · Aug 5, 2005

FSO with ActiveX is what you are looking for...

-DNG

scribbler · Aug 6, 2005

Thanks for the reply DNG but can you point me to any examples of how to achieve this bearing in mind I'm quite new to all this HTML,Javascript,ASP,VBScript coding lark.

DotNetGnat · Aug 6, 2005

does this link help you:

http://4umi.com/web/javascript/fileread.htm

-DNG

scribbler · Aug 6, 2005

I had a look and at first glance couldn't follow whats happening but I'll sit down and try to figure it out.

I expected a more simple command line or two after using FSO to read files on the server seemed relatively simple.

Cheers

onpnt · Aug 6, 2005

there are also a bunch of hacks in this forums and the vbscript's forum on using FSO in the FAQ sections. over the years we've added just about any form of it's use along with a few trouble spots to avoid

scribbler · Aug 7, 2005

Cheers onpnt I have had a look thro' the forum but didn't see what I was looking for but I'll take another look thro'.

scribbler · Aug 16, 2005

onpnt or anyone

I browsed the forums and found a reference from Chris Hirst stating the following....

"ASP cannot create files client side!"

..which I obviously understand but he goes on to say ...

"you will need use the WSH. Search the forums there will be a few threads around."

Can anyone enlighten me further...
As all I am hoping to achieve is to open ASCii files on my local PC and post them into database fields onto the remote server.

Sheco · Aug 16, 2005

Alright but you need to do this with client-side script. ASP runs on the server.

The best client-side script to use is JavaScript because it is most widely supported. The only time it is safe to consider using VBScript for client-side scripting is on an intranet for a company where IE is the only approved browser.

scribbler · Aug 16, 2005

OK Sheco

DO you, or anyone else, know if it is possible to achieve my goal using javascript and if so do you know where I might locate some useful tutorials on this ?

I'll have a browse through the javascript forum to see if I can track something down.

Sheco · Aug 16, 2005

Yes it is possible to do it with javascript...

The javascript code might look something like this:

Code:

var oFSO, oTxt;

oFSO = new ActiveXObject("Scripting.FileSystemObject");

oTxt = oFSO.OpenTextFile("c:\\testfile.txt", 1, false);

// Insert logic here to read TextStream using Read, ReadLine, or ReadAll

oTxt.Close;

The next question after you get this FSO going is how you'll submit it to the server. Your two main choices are (1) using javascript to populate values in an HTML form and (2) by using the xmlhttp activex object.

scribbler · Aug 16, 2005

OK Sheco

Can you tell me if I'm right in thinking then that the following opens up the file on my Client PC, and closes it.

Code:

var oFSO, oTxt;
oFSO = new ActiveXObject("Scripting.FileSystemObject");
oTxt = oFSO.OpenTextFile("c:\\testfile.txt", 1, false);
// Insert logic here to read TextStream using Read, ReadLine, or ReadAll
oTxt.Close;

And then if I set a varMyDatabseLocation="Whatever the path of my database on the remote server is"
that is sufficient, obviously provided I fill in the code to read & write. If so, seeing as at present I use asp to convert the ascii file at the moment, can I mix asp with javascript to achieve what I require. Reason being I know less about javascript than I do about asp, and thats not saying much.

theniteowl · Aug 16, 2005

scribbler, you did not say what type of file you were reading or if it was delimited.
Here is a sample script in javascript that will let you browse to and select a file then read that file into a javascript array using the split function to separate the info based on a specified delimiter.

<html>
<head>
<title>Browse to and read a file</title>
<script language="JavaScript">
<!--
var arrData = new Array();
function readthefile()
{
var filename = document.all('MyFile').value;
var fso, a, ForReading;
ForReading = 1;
fso = new ActiveXObject('Scripting.FileSystemObject');
if (fso.FileExists(filename))
{
file = fs

penTextFile(filename, ForReading, false);
var intxtstr = file.readline();
file.Close();
if (intxtstr != "")
{
arrData=intxtstr.split("¦"); // ASCII character 127
}
}
}
// -->
</script>
</head>
<body>
<form>
File: <input type="file" name="MyFile" size="40"><br><br>
<input type="button" value="Process" onclick="readthefile();">
</form>
</body>
</html>

OR, you could bypass the split function and write the data into a hidden form field that you can read from your ASP page. Then you can use ASP to parse out the data.

scribbler · Aug 16, 2005

Hi theniteowl

Firstly thanks for taking the time to reply to my post.

My existing files are received as quite large ascii files and at present I upload them to the site, then use asp to read in line by line,place into strings and then post into an access database. it takes about 45 mins to upload, then a further 1-1.5 hrs to convert and this is, I am told, to be done on a 2-3 times per week basis.

Im very poor at javascript so can I trouble you to comment your code so I can try to understand whats happening. I am fairly new to this.

Sheco · Aug 16, 2005

Do you want the code to check the contents of the file to see if it is OK to send the entire file to the server?

... Or do you wish to extract some of the information out of the file and just send the extracted data?

theniteowl · Aug 16, 2005

Wow, any file that takes 45 minutes to upload is going to be troublesome. I do not know that clientside script is going to be able to handle it. I do not know the limits on any data stored in the form.

However, I do have script that will let you select a file locally and write it to a server folder at which time your ASP page could begin reading the file.

What method do you use to read and convert the file now?
Once you upload the file to the server do you have one script that processes the file and pushes it to the database?

Is your data delimited AND each line ended with a carriage return?
Here is some detail about my script.
By setting the type="file" in the form it forces IE to present you with a Browse button which returns the path and filename of what you selected to the field. The button "Process" simply calls the javascript function and passes no values.

<form>
File: <input type="file" name="MyFile" size="40"><br><br>
<input type="button" value="Process" onclick="readthefile();">
</form>

Here I am creating a new array to store the data in. It sounds like you may not need an array ultimately though.
var arrData = new Array();

function readthefile()
{
//Grab the path/filename stored in the form field.
var filename = document.all('MyFile').value;
//Declare the variables for the filesystemobject
var fso, a, ForReading;
ForReading = 1;
//This creates the new FileSystemObject and calls it fso
fso = new ActiveXObject('Scripting.FileSystemObject');
//This does a test to make sure the filename/path is valid
if (fso.FileExists(filename))
{
//Open the file for reading.
file = fs

penTextFile(filename, ForReading, false);
//Reads from the file until it sees a carriage return.
//My file used a delimiter but no carriage returns so it read in all in one string.
//You may need to setup a loop and test for EOF.
var intxtstr = file.readline();
//Close the file
file.Close();
//If the content of the file was not blank.
if (intxtstr != "")
{
//split the string into array elements wherever
//it sees ASCII character 127 which is what I used for
//a delimiter.
arrData=intxtstr.split("¦"); // ASCII character 127
}
}
}

theniteowl · Aug 16, 2005

A lot depends on what you have to do to process the file before uploading to the database and what format the data is in.
If you have something like this:

data1, data2, data3, data4<cr>
data5, data6, data7, data8<cr>

Then it will be easy enough to read in a single line of the file with file.readline();
You can then pass that data string to another script to have it upload to the database and then move on to the next line.
You may want to look into xmlhttp to do the passing to your ASP page as sheco was suggesting. I have not used it myself but it looks like it will be useful.

scribbler · Aug 16, 2005

This is how I achieve the conversion so far aftre uploading the ascii files to the server

Code:

'This file converts all Item prices and selectable options into 2 databases (1) tblprices  (2) tbloptions<br>
Dim counter,fs
dim char1, char2, char3, char4, char5, char6, char7, char8, char9, char10, char11, char12, char13, char14
Dim thisfile, thisline, whichfile
'Dim VehicleOptionsSQL   'Holds the SQL query for the database
Dim TimeOut, Con,  DBPath
DBPath=Session("dbpath")
TimeOut = Server.ScriptTimeout 
Server.ScriptTimeout = 10000
Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPath
'1st clear out all current price records in database
con.Execute("DELETE FROM tblprices")
con.Execute("DELETE FROM tbloptions")
whichfile=server.mappath("CPRNEW.A05")
Set fs = CreateObject("Scripting.FileSystemObject")
Set thisfile = fs.OpenTextFile(whichfile, 1, False)
thisline=thisfile.readline  'Just to Read 1st Line then ignore it as it contains date information
do  while not thisfile.AtEndOfStream
   thisline=thisfile.readline   
    If left(thisline,5) <> "TOTAL" Then    'Just checking that the last line is ignored as it just gives total records
		char1=left(thisline,3)' No Of Options
		char2=mid(thisline,4,20)'CAP VehCode
		char3=mid(thisline,24,5)'CAPVehId	
		char4=mid(thisline,29,15)'BasicPrice
		char5=mid(thisline,44,14)'BasicVAT
		char6=mid(thisline,58,15)'BasicTotal
		char7=mid(thisline,73,8)'LastPriceChange
		char8=mid(thisline,81,8)'LastOptionChange
		con.Execute("INSERT INTO tblprices (Options, CAPVehCode, CAPVehId, BasicPrice, BasicVAT, BasicTotal, LastPriceChange, LastOptionChange) VALUES ('" & char1 & "', '" & char2 & "', '" & char3 & "', '" & char4 & "', '" & char5 & "', '" & char6 & "', '" & char7 & "', '" & char8 & "')")
		response.Write("Just Inserted CAPVehId=") & char3 & ("<br>")
		
		for counter = 0 to char1-1 'start counter at 0 due to multiplying x by 52 each time		
			char9=mid(thisline,89 + (counter*52),7) ' This is the Actual Option Code
			char10=mid(thisline,96 + (counter*52),1) 'The Option Type
			char11=mid(thisline,97 + (counter*52),15)'The Option Basic
			char12=mid(thisline,112 + (counter*52),14)'OptionBasicVAT
			char13=mid(thisline,126 + (counter*52),15)'OptionTotalCost
			con.Execute("INSERT INTO tbloptions (CAPVehID, Code, Type, OptionBasic, OptionVAT, OptionTotal) VALUES ('" & char3 & "','" & char9 & "', '" & char10 & "', '" & char11 & "', '" & char12 & "', '" & char13 & "')")			
		next 	
	end if
loop
thisfile.Close
set fs=nothing
Con.Close
Set Con = Nothing
Server.ScriptTimeout = 190

theniteowl · Aug 16, 2005

Any chance you have IIS on your own PC?
If you have to download this large file to your own machine before sending it to the server then you might be able to save an extra upload step by executing your script locally on the machine where the ascii file exists.

Otherwise you could use a script to let you browse/select the file, upload it to the server and automatically kick off your processing script. I have a script that will upload a file to a server folder. Will have to pull it out and clean it up tomorrow morning if you need it.
On submission of the file to the server it could then automatically begin processing of the file in that location.

scribbler · Aug 16, 2005

OK Guys thank you both for you posts. I need to look into this further and taking in what you suggest, give it more thought. The database on the server once converted is circa. 342MB so this gives you some idea on the scale of things. I also, from what I'm told, need to consider changing to SQL instead of Access but being quite new to all this html, asp , javascript etc si driving me crackers. Life was much more siple when I began learning VB3.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

FSO & local PC help 2

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

Programmer

Programmer

MIS

Programmer

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor