Keith Joubert logo - home.

Home
Contact

Binary Search
Sort Multiple Columns
Filing Functions
Extracting Excel data
Read and Write .csv files
Show Progress Messages
Send Automatic Email
Running under .hta
Setting Folder Attributes
Long-script warnings
Desktop Shortcut
ADODB & SQL queries


 

 
READ and WRITE .csv FILES

If you need to read & write data from/to .csv files, this can be carried out with ActiveX and Javascript.

You need to choose a suitable delimiter.  Choices are usually:
comma [ '"" ]
semi-colon: [ ;"" ]
tab: [ \t"" ]
pipe: [ |"" ].

Some versions of Excel only accept [ ;"" ] .csv input. 
[ |"" ] .csv files are probably the most fool-proof, in that punctation does not result in incorrect column allocation, which can arise with the other delimiters.  However, the most-used is [ ,"" ].

// var headArr is an array containing the column headings.  If you don't want to use a header row, leave the array empty
// var TheArr is any array passed in.  It may be multi-dimensional.
// var delim is the delimiter character used, e.g., ',' (a comma).
// Global path parameter: gpath, and an empty file name: gFile, are used.
// Set gpath to the path-name of the.csv file you are saving.

var headArr=new Array(); // if you want header put names in here, eg, 'Item','Description','Color','Number','Price'........

function SaveCSVData(headArr,TheArr,delim) {
  DeleteFile(); // this function is shown in Filing Functions - it uses gpath.
  OpenFile(ForWriting);  // this function is shown in Filing Functions - it uses gpath.
  var len = TheArr.length;
  var tstr1 = new String;
  if(headArr.length>0){
    tstr1 = headArr.join(d);
    gFile.writeline(tstr1);
  }
  for (var i=0; i<len; i++){
    tstr1 = TheArr[i].join(d);
    gFile.writeline(tstr1);
  }
  gFile.Close();
}

 
// GetPlainData reads un-encoded files, including .csv.
// With .csv data, the extra quotes must be stripped, or searching, etc, becomes difficult.
// If csv==true then d=delimiter, eg, ';'   ','   '|'   '\t'

function GetPlainData(TheArr,csv,d){
  var tstr1='';
  var i=x=0;
  var len=0;
  var y='';
  OpenFile(ForReading);  // this function is shown in Filing Functions - it uses gpath & gFile.
  while(!gFile.AtEndOfStream){
    tstr1 = gFile.readline();
    var tArr = new Array();
    tArr = tstr1.split(d);
    if (csv==true){
      len = tArr.length;
      for (var j=0; j<len; j++){
        y=tArr[j];
        if(y.substr(0,1)=='"'){tArr[j]=y.substring(1,y.length-1);}
        if(!isNaN(tArr[j]*1)){tArr[j]=(tArr[j]*1);} // optional line to convert string to number, if applicable.  Usage depends on you.
      }
    }
    TheArr[i]=tArr;
    i++;
  }
  gFile.Close();
}

// Note:  to remove a header row that has been read-in, and is unwanted during array manipulation, use:  var t=AnArr.shift();
// AnArr is the array containing the data, e.g., as a result of calling GetPlainData(AnArr, true, ',') 
// This loads comma-delimited csv file into the array AnArr, from the file described by gpath (global).

TopTop