Send SQL query to a text file using ASP and Javascript

First of all: Edit your post and embed your code using the CODE button or the [code][/code] tags. This increases readability of your post and simplifies copying it.

Classic ASP? ouch..

Do you want to create a file server-side or should the user download a CSV?

I have a SQL union query that I need to export to a text file using javascript in an ASP page.

JavaScript is client side it can only be used to send the request to the server.

A download is a very simple thing you
a) send the headers telling the browser what file you are sending it
b) you output the data.

In your ASP script you would do this

<%
'Your code to run your query and get your results here
' Now tell the browser what you are sending it

Response.ContentType = "application/csv"
Response.AddHeader "Cache-Control", "no-cache"
Response.AddHeader "Content-Disposition", "attachment; filename=myfilename.csv"

' Now output the data
Response.write(...)

Select allOpen in new window

How you invoke this script depends on your client side application - typically you want to call the URL directly - that contains the above script so the response is sent to the main browser window and not to an AJAX callback.

Ste5an:  I want to create a txt file server side.  Thank you

Hi Michele,

if the only reason to use JavaScript is because you like coding your tools in JavaScript then please look at my brand new article about compiling JavaScript to a stand alone executible:
https://www.experts-exchange.com/articles/33945/Javascript-Compiler.html

If you need support in fetching SQL response record set to a file then I can show you that too.

Zvonko

Zvonko:

I use javascript because I develop in a specific web program.  ASP and javascript are the standards that are used.  I could use VB script as well.  

I will check out your post and see if I might be able to make it work.

I am trying to put some code behind a button on an ASP page to export query results to a text file from SQL.  My real challenge seems to be doing this from an ASP page.

Behind the button, I was planning on calling another ASP page to do the export.

Thank you for your assistance.

Updated:  I have reviewed your article.  It does appear that this would be helpful.  However, I will need to figure out how to use it.

I can tell you how I would use it.
I would create tools for small simple single step tasks.
This single task tools can be then used in a chain by one global control task that also can be invoced from an executable prgramm or a batch script.

If you open questions for that single steps then I can help you to tune the JavaScript code for every step.

If you prefer then I can help you to write the executbles in C# code.
But I will not merge into VB version if you prefer that.

The C# version and the JavaScript version work using dot.Net interfaces to system calls.

Here is the new article how to write text files from compiled JavaScript:
https://www.experts-exchange.com/articles/33946/JavaScript-Compiler-Write-Text-File.html

Behind the button, I was planning on calling another ASP page to do the export.

Did you see my post above? This is exactly how you do what you want to do.

Not sure what compiled JavaScript has to do with this as what you are wanting to do is a server side operation and introducing another tech at this point is not really the best idea.

You have ASP (backend) HTML + JavaScript (frontend) - that is all you need to solve this problem.

Your ASP script, as described above has the capability of sending headers (required to tell the browser you are initiating a download and of what mime type) and of outputting the data that must be in the file.

All that is required is that you have something on the front end to kick that off.

It makes total sense to put the server code in its own script (as described in my earlier post) and have a button link to that script through a simple <a> tag (no JavaScript required). This will allow the browser to make the request to the ASP script, determine that the return is a file download and show the File Open / Save dialog to the user - UI in the browser remains as it is.

This is the standard way of solving this problem - don't get distracted by other technologies that are not going to add anything of value but will make your solution more complicated.

Julian:  Unfortunately, I am still working on my javascript.  With my knowledge, I am able to do 95% of what I need to do.  This is something new that I am trying to figure out how to accomplish.  Your solution makes sense in theory to me.  However, I require a bit more hand holding.

I had started out by creating a text file on the server and then I was going to attempt to write my query to the text file.

However, I think your solution tosses that out.  Correct?

If I am reading your solution correctly, I would do the following...

<%
'Your code to run your query and get your results here
var sql1="select * from vPOExport ORDER BY '2'";
var myQuery = CRM.CreateQueryObj(sql1,"");
myQuery.SelectSQL();

var result = myQuery;

' Now tell the browser what you are sending it
Response.ContentType = "application/csv"
Response.AddHeader "Cache-Control", "no-cache"
Response.AddHeader "Content-Disposition", "attachment; filename=myfilename.csv"

' Now output the data
Response.write(result)

%>

Select allOpen in new window

With this, the file would end up client side rather than on the server.  I am OK with this as the user will need the file to import it into the accounting system and it will be easier to get it from their computer than from the server.

Also, I don't want any headers included in the file.  Just the data.  I may have indicated a "header" in my original post.  However, it is a header record and not a true "header".  The first line represents a PO record and the second line represents the PO details.  All of this is data and not a "header" or "label".  I hope that makes sense.  I tried to show what the output should look like  (below is 3 POs)
C,1,1,,zzzFRTR,, , , , , , , , , , ,.000000, , , , , , , ,                                           <<---Data that is the "header" for a PO
CI,1, , , ,01/13/20, ,15-10-0668,,,, , , , ,,650.000000, , , , , , , ,                  <<---Details for the PO (this could be more than 1 line.)
C,2,1,,zzzFRTR,, , , , , , , , , , ,.000000, , , , , , , ,                                           <<---Data that is the "header" for the next PO
CI,2, , , ,01/13/20, ,15-10-0668,,,, , , , ,,625.000000, , , , , , , ,                  <<---Details for the PO (this could be more than 1 line.)
C,3,1,,zzzFRTR,, , , , , , , , , , ,.000000, , , , , , , ,                                           <<---Data that is the "header" for the next PO
CI,3, , , ,12/30/99, ,15-10-0668,,,, , , , ,,675.000000, , , , , , , ,                 <<---Details for the PO (this could be more than 1 line.)

Your assistance is greatly appreciated, I just need more help understanding your solution.

Unfortunately, I am still working on my javascript.

Not sure where JavaScript comes into this? Unless you are using JavaScript to send data to the server (AJAX) it should not really have anything to do with the actual generation of the file.

With this, the file would end up client side rather than on the server
...
However, I think your solution tosses that out.  Correct?

No not at all, if you need to write it to a file on the server that is just as simple.

Drop the header code (Response.AddHeader)
And (from here https://www.w3schools.com/asp/met_createtextfile_folder.asp)

var sql1="select * from vPOExport ORDER BY '2'";
var myQuery = CRM.CreateQueryObj(sql1,"");
myQuery.SelectSQL();

var result = myQuery;
dim fs,tfile
set fs=Server.CreateObject("Scripting.FileSystemObject")
set tfile=fs.CreateTextFile("c:\somefile.txt")
tfile.WriteLine("result)
tfile.close
set tfile=nothing
set fs=nothing

Select allOpen in new window

[NB: Above is untested and is used for illustration only]

Also, I don't want any headers included in the file

There are no headers AddHeader - has nothing to do with the file - it is telling ASP to create an HTTP Header which is meta data that the browser uses to decide what it must do with data it has received. The data itself is in the body of the response and only includes what you put in the Response.write statements

If I can summarize

You want a browser action to kick off the creation of a file on the server. The file can either be saved to the server or sent back to the client as a download.

Your options are [use this if you want to do a client download]

<a href="youraspscript.asp">Click me to create account dump</a>

Select allOpen in new window

Note: You can use JavaScript to do a download but it is a lot more complicated.
Or
JavaScript (jQuery in this case)

$('someelement').click(function(e) {
  e.preventDefault();
  $.post('youraspscript.asp', data, function(resp) {
     // do something with the response (show a message, redirect the page ...)
  });
});

Select allOpen in new window

Server your script is simple - as above for the first case (download) - and as per the code in this post.

Thank you your assistance and the explanations.  Also, thank you for the link to W3schools that will help with my understanding so I really appreciate that.

I will review all this and see if I can apply it to my project and respond back.

Not sure where JavaScript comes into this?

In regards to Javascript, I thought I was using javascript in my asp page when I wrote...

var sql1="select * from vPOExport ORDER BY '2'";
var myQuery = CRM.CreateQueryObj(sql1,"");
myQuery.SelectSQL();
var result = myQuery;

Then it looks like you finished that out with VBscript...

dim fs,tfile
set fs=Server.CreateObject("Scripting.FileSystemObject")
set tfile=fs.CreateTextFile("c:\somefile.txt")
tfile.WriteLine("result)
tfile.close
set tfile=nothing
set fs=nothing

var is javascript
dim and set are vbscript

However, overall, I think I get the gist of it.

Again...thank you!!  I will report back.

Here my example:

<%@ Page Language="JavaScript" Debug="true" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.IO" %>

<!DOCTYPE html>
<html>
<body>
<%
var CONNSTRING = "Provider=SQLOLEDB;SERVER=MD2M7GQC;UID=myUser;PWD=myPassword;DATABASE=Northwind";
var QUERYSTRING = "SELECT TOP (100) *  FROM Orders ORDER BY 2";
var Conn = new OleDbConnection(CONNSTRING)
Conn.Open();
var command = new OleDbCommand(QUERYSTRING, Conn);
var oleReader = command.ExecuteReader();
var strFileBase = Server.MapPath("/");
var strFilePath = Server.MapPath("./data/myFile.txt");
var strFileUrl = strFilePath.replace(strFileBase,"/");
var file = File.CreateText(strFilePath);
	while (oleReader.Read()){
		var strLine = "";
		for(var i=0;i<oleReader.FieldCount;i++){
			strLine += ","+oleReader[i].ToString();
		}
		file.WriteLine(strLine.substr(1));
	}
	file.Flush();
	file.Close();
	oleReader.Close(); 
	Conn.Close();
%>
<a href="<%=strFileUrl%>" target="_blank">data/myFile.txt</a>
</body>
</html>

Select allOpen in new window

But this version ist an ASPX version.

Du you prefer Clasic ASP?

And here the Clasic ASP version:

<%@ Language= "Javascript" %> 
<!DOCTYPE html>
<html>
<body>
<%
var CONNSTRING = "Provider=SQLOLEDB;SERVER=MD2M7GQC;UID=myUser;PWD=myPassword;DATABASE=Northwind";
var QUERYSTRING = "SELECT TOP (100) *  FROM Orders ORDER BY 2";
var strFileBase = Server.MapPath("/");
var strFilePath = Server.MapPath("./data/myFile.txt");
var strFileUrl = strFilePath.replace(strFileBase,"").replace(/\\/g,"/");;
var fso = Server.CreateObject("Scripting.FileSystemObject");
var file = fso.CreateTextFile(strFilePath)
var conn = Server.CreateObject("ADODB.Connection");
conn.ConnectionString = CONNSTRING;
conn.open();
var rs = Server.CreateObject("ADODB.recordset");
rs.Open( QUERYSTRING, conn );
rs.MoveFirst(); 
while (rs.EOF != true) {
	var strLine = "";
    for(var i=0;i<rs.Fields.Count;i++){
		strLine += ","+rs.Fields(i);		
    }
	file.WriteLine(strLine.substr(1));
    rs.MoveNext();
}
rs.close();
conn.close();
%>
<a href="<%=strFileUrl%>" target="_blank">data/myFile.txt</a>
</body>
</html>

Select allOpen in new window

And here the JavaScript executable version:

// sqlwritefile.js 
import System;
import System.IO;
import System.Text;
import System.Data.OleDb
import System.Diagnostics;

var verInfo = "sqlwritefile v1.0.1";
var logFileName = "";
var curDir = "";
var dataFileName = "myFile.txt";
var CONNSTRING = "Provider=SQLOLEDB;SERVER=MD2M7GQC;UID=myUser;PWD=myPassword;DATABASE=Northwind";
var QUERYSTRING = "SELECT TOP (100) *  FROM Orders ORDER BY 2";
//  call main:
	main();

	
function main(){
	try{
		var exeArgs = System.Environment.GetCommandLineArgs();	
		var exeFilePath = System.Reflection.Assembly.GetExecutingAssembly().Location;
		var curDir = System.IO.Path.GetDirectoryName(exeFilePath)+"\\";
		var exeFileName = exeFilePath.replace(/^([^\\]*\\)*/g,'');
		var exeProcName = exeFileName.replace(/\.exe/i,'');
		logFileName = curDir+"logs\\"+exeProcName+"_"+getTimeStamp()+".log";
		if(!Directory.Exists(curDir+"logs")){
			Directory.CreateDirectory(curDir+"logs")
		}
		Console.SetWindowSize(160, 40);
		Console.SetBufferSize(160, 9999);
		Console.BackgroundColor = ConsoleColor.Gray;
		Console.ForegroundColor = ConsoleColor.DarkBlue;
		Console.Clear();
		Console.Title = "*** "+verInfo+" ***";
		log(verInfo);
		var strUserName = System.Environment.UserName;
		var strComputerName = System.Environment.MachineName;
		
		if(exeArgs.length > 1){
			log("Params: "+exeArgs.join(" "));			
		}
		var strFilePath = curDir+dataFileName;
		var file = File.CreateText(strFilePath);
		var Conn = new OleDbConnection(CONNSTRING)
		Conn.Open();
		var command = new OleDbCommand(QUERYSTRING, Conn);
		var oleReader = command.ExecuteReader();	
		while (oleReader.Read()){
			var strLine = "";
			for(var i=0;i<oleReader.FieldCount;i++){
				strLine += ","+oleReader[i];
			}
			file.WriteLine(strLine.substr(1));
		}
		file.Flush();
		file.Close();
		oleReader.Close(); 
		Conn.Close();
		log("File created: "+strFilePath);
	} catch(err) {
		log(err.message);
		log("ERROR:\r\n\r\n"+System.Environment.StackTrace);
	}
}

function padStart(theNum, length, padString) {
    var str = theNum+'';
    while (str.length < length)
        str = padString + str;
    return str;
}

function writeLine(theFile, theLine){
	var fs = System.IO.File.AppendText(theFile);
	fs.WriteLine(theLine);
	fs.Close();
}	

function log(theLine){
	var logLine = '['+getTimeStamp()+'] '+theLine;
	if(logFileName){
		System.IO.File.AppendAllText(logFileName, logLine+"\r\n", Encoding.UTF8);
	}
	print(logLine);
}

function getTimeStamp(){
	var now = new Date();
	return now.getFullYear()+d2(now.getMonth()+1)+d2(now.getDate())+d2(now.getHours())+d2(now.getMinutes())+d2(now.getSeconds());
}

function d2(theNum){
  return ((theNum>9)?'':'0')+theNum;
}

Select allOpen in new window

Hello Michele, any progress here?

Hello Michele, do you need more help with this?