dcsimg
www.webdeveloper.com
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 60

Thread: CSV to html form field

  1. #1
    Join Date
    Oct 2005
    Posts
    33

    CSV to html form field

    Is there a way to read a csv database then transfer the relevant information into an html form field client side. I have been searching for days now and at a dead end, so hope someone can help

  2. #2
    Join Date
    Apr 2005
    Posts
    387
    You can use JScript and ADO to read csv files but this must be done on the server or from an hta. Please further explain the situation.

  3. #3
    Join Date
    Oct 2005
    Posts
    33

    More detailed explanation

    I have a csv excel database listing people and other fields that arrives via e mail at present and it is then manually typed into a data source and merged into a word document. I want to be able to transfer the information on that database onto the agreement transfering all the relative headings and filled out details automatically and also into aother form that I have developed using javascript on internet explorer. The final form is fully complete it is the solution of transfering the data I need

  4. #4
    Join Date
    Apr 2005
    Posts
    387
    Ok, I'll make a test hta showing how to retrieve text file (csv etc.) data using ADO and JScript.

  5. #5
    Join Date
    Apr 2005
    Posts
    387
    Ok, save this as any_name.hta Double click on it to open.

    It's set to read csv files only but the function "text_recordSet" can read any delimited text file

    If you're not familiar with hta, look here

    Code:
    <html>
    <head>
    <title>Read CSV</title>
    
    <HTA:APPLICATION />
    
    <script type="text/jscript">
    
    var fso=new ActiveXObject('Scripting.FileSystemObject');
    
    function ADO(){
    return new ActiveXObject('ADODB.Connection')
    }
    
    String.prototype.folderOf=function(no_slash){
    return this.slice(0,this.lastIndexOf('\\',this.length-(/\\$/.test(this)?2:0))+(no_slash?0:1))
    };
    
    String.prototype.ext=function(){
    return this.slice(this.lastIndexOf('.')+1)
    };
    
    String.prototype.isCSV=function(){
    var ex=this.ext();
    return ex.length==3&&/csv/i.test(ex)
    };
    
    function text_recordSet(ado,file_path,column_headings,delimiter){
    ado.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+file_path.folderOf()+";Extended Properties='text;HDR="+(column_headings?'Yes':'No')+";FMT=Delimited("+(delimiter?delimiter:',')+")'");
    return ado.execute('select * from ['+fso.GetFile(file_path).name+']')
    }
    
    function getCSV(file_path){
    if(!file_path.isCSV()){
    alert('File must be of type ".csv"');
    return 0
    }
    var ado=ADO(),
    rs=text_recordSet(ado,file_path),
    n=rs.fields.count,
    i,
    str='';
    while(!rs.eof){
    i=0;
    while(i<n)str+=rs.fields.item(i++).value+' , ';
    str+='<br>';
    rs.moveNext()
    }
    ado.close();
    document.getElementById('output').innerHTML=str
    }
    </script>
    
    </head>
    <body>
    Browse to a csv file:
    <input type="file" onchange="getCSV(this.value)">
    <div id="output"></div>
    </body>
    </html>

  6. #6
    Join Date
    Oct 2005
    Posts
    33

    refined explanation

    thank you very much for the application which will come in handy for the input to the database.

    A better explanation of my original question is : I am trying to auto fill an html form with the contents from a csv database, from there I print the form which includes the form data - This process is all completed offline (client side).

  7. #7
    Join Date
    Apr 2005
    Posts
    387
    Just a side note. I wrote the text_recordSet function long enough ago to not remember why I used fso to get the file name. Below is the same hta without fso

    Quote Originally Posted by robinhood
    A better explanation of my original question is : I am trying to auto fill an html form with the contents from a csv database, from there I print the form which includes the form data - This process is all completed offline (client side).
    Are you saying you don't know how to put the data into a form ? If so, the while(!rs.eof) loop, coloured red below, is what you need to use to populate your form.
    Code:
    <html>
    <head>
    <title>Read CSV</title>
    
    <HTA:APPLICATION />
    
    <script type="text/jscript">
    
    function ADO(){
    return new ActiveXObject('ADODB.Connection')
    }
    
    String.prototype.folderOf=function(no_slash){
    return this.slice(0,this.lastIndexOf('\\',this.length-(/\\$/.test(this)?2:0))+(no_slash?0:1))
    };
    
    String.prototype.ext=function(){
    return this.slice(this.lastIndexOf('.')+1)
    };
    
    String.prototype.isCSV=function(){
    var ex=this.ext();
    return ex.length==3&&/csv/i.test(ex)
    };
    
    String.prototype.fileOnly=function(){
    return this.slice(this.lastIndexOf('\\')+1)
    };
    
    function text_recordSet(ado,file_path,column_headings,delimiter){
    ado.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+file_path.folderOf()+";Extended Properties='text;HDR="+(column_headings?'Yes':'No')+";FMT=Delimited("+(delimiter?delimiter:',')+")'");
    return ado.execute('select * from ['+file_path.fileOnly()+']')
    }
    
    function getCSV(file_path){
    if(!file_path.isCSV()){
    alert('File must be of type ".csv"');
    return 0
    }
    var ado=ADO(),
    rs=text_recordSet(ado,file_path),
    n=rs.fields.count,
    i,
    str='';
    while(!rs.eof){
    i=0;
    while(i<n)str+=rs.fields.item(i++).value+' , ';
    str+='<br>';
    rs.moveNext()
    }
    ado.close();
    document.getElementById('output').innerHTML=str
    }
    </script>
    
    </head>
    <body>
    Browse to a csv file:
    <input type="file" onchange="getCSV(this.value)">
    <div id="output"></div>
    </body>
    </html>

  8. #8
    Join Date
    Oct 2005
    Posts
    33
    Thanks GPH you have been a fantastic help

    I can now call the csv and input the csv content to the html page which renders the csv file contents between the div id="output"/div

    Is it posible to call each table name content from the csv and place the data in the INPUT TYPE=TEXT NAME="formname"
    field with the matching name in the form on the html page

  9. #9
    Join Date
    Apr 2005
    Posts
    387
    So I can better understand what you want for output, post an html sample form and attach a sample csv file. It might be helpful to name the text fields or hard code their values so I can see exactly what you need to do. Also, you'll probably need to change the csv file extension from csv to txt in order to post it as an attachment.

  10. #10
    Join Date
    Oct 2005
    Posts
    33

    Zip att..

    The last hurdle always the heighest
    Attatched in zip is my sample. Thank you so much for your help gph.
    Attached Files Attached Files

  11. #11
    Join Date
    Apr 2005
    Posts
    387
    Here's an output to text input example. If in the future I think of something I've missed I'll post an update.

    Notes:
    • The variable "csvPath", highlighted in red, must have double backslashes ("\\") in the path and must be set to your csv file path before it will work. If you want to change it back to browsing for the file let me know.

    • If you add or remove fields in the csv file you need to edit the html table, highlighted in blue, accordingly. Each row in the table represents a field in the csv file. Try editing the html table manually because the html editor you are using will likely mess it up. I have highlighted the first row in the table in dark red. Just add or remove table rows as required.

    • Now that I've removed fso, this might work as a regular html file instead of hta. It doesn't warn me when I open it as htm but my security setting for IE are low. If I remember correctly you'll run into this warning, see "RESOLUTION". If it doesn't work as htm just add "<HTA:APPLICATION />" as in my examples above and save it as hta.

    • It will need to be edited if you change the format of the dates in the csv file. I've run into a similar issue while reading Excel files with ADO/SQL and solved it using "IMEX=1" For some reason I can't make IMEX work in text files so I hard coded the date format as per your example csv. This issue is something I hope to resolve.

    • The array "separators", highlighted in green, is an array of html elements that gets added between each record. You can add or remove items from the array i.e. separators=['<br>']; removes one line break and the horizontal line.


    This is the csv file I used to test
    Code:
    firstname,lastname,dateofbirth
    John,Doe, 20/01/1975
    Sue, Jones,10/07/1970
    Joe, Smith, 25/12/1961
    Here's the code
    Code:
    <html>
    <head>
    <title>Read CSV</title>
    
    <style type="text/css">
    
    body{
    margin:50px;
    }
    .template{
    font-family:Verdana,Arial,Times New I2;
    font-size:12px;
    }
    
    </style>
    
    <script type="text/jscript">
    
    var csvPath='C:\\Documents and Settings\\gph\\Desktop\\csvdb.csv',
    
    separators=['<br>','<hr>','<br>'];
    
    function ADO(){
    return new ActiveXObject('ADODB.Connection')
    }
    
    String.prototype.folderOf=function(no_slash){
    return this.slice(0,this.lastIndexOf('\\',this.length-(/\\$/.test(this)?2:0))+(no_slash?0:1))
    };
    
    String.prototype.ext=function(){
    return this.slice(this.lastIndexOf('.')+1)
    };
    
    String.prototype.isCSV=function(){
    var ex=this.ext();
    return ex.length==3&&/csv/i.test(ex)
    };
    
    String.prototype.fileOnly=function(){
    return this.slice(this.lastIndexOf('\\')+1)
    };
    
    Date.prototype.shortYear=function(){
    return this.getFullYear().toString().slice(2)
    };
    
    Date.prototype.monthNumber=function(){
    return this.getMonth()+1
    };
    
    Number.prototype.twoDigit=function(){
    var str=this.toString();
    return str.length<2?0+str:str
    };
    
    Date.prototype.dayMonthYear=function(){
    return this.getDate().twoDigit()+'/'+this.monthNumber().twoDigit()+'/'+this.shortYear()
    };
    
    function text_recordSet(ado,file_path,column_headings,delimiter){
    try{ado.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+file_path.folderOf()+";Extended Properties='text;HDR="+(column_headings?'Yes':'No')+";FMT=Delimited("+(delimiter?delimiter:',')+")'")}
    catch(e){return e.message}
    return ado.execute('select * from ['+file_path.fileOnly()+']')
    }
    
    onload=function(){
    if(!csvPath.isCSV()){
    vb_warning('File must be of type ".csv"','File Type Error');
    return 0
    }
    var ado=ADO(),
    rs=text_recordSet(ado,csvPath);
    if(typeof rs=='string'){
    vb_warning(rs,'CSV File Connection Error');
    return 0
    }
    var seps=separators.length,
    n=rs.fields.count,
    table=document.body.firstChild,
    r=table.rows.length,
    txt,
    i;
    if(r!=n){
    vb_warning('The template table contains '+r+' rows.\nThe csv file contains '+n+' fields.\nThese numbers must be the same.\n\nOperation canceled.','Input Error');
    return 0
    }
    rs.moveNext();
    while(!rs.eof){
    i=0;
    while(i<n){
    txt=rs.fields.item(i).value;
    table.rows[i++].cells[1].firstChild.value=typeof txt!='date'?txt:new Date(txt).dayMonthYear();
    }
    document.body.appendChild(table);
    rs.moveNext();
    if(!rs.eof){
    i=0;
    while(i<seps)document.body.appendChild(document.createElement(separators[i++]));
    table=document.body.firstChild.cloneNode(1)
    }
    }
    ado.close()
    }
    </script>
    <script language="vbscript" type="text/vbs">
    
    Function vb_warning(str,title)
    MsgBox str,vbOKOnly+vbCritical+vbApplicationModal,title
    End Function
    
    </script>
    </head>
    <body>
    
    <table class="template">
    
    <tr>
    <td>First Name(s):</td>
    <td><input type="text" size="50" readonly></td>
    </tr>
    
    <tr>
    <td>Family Name:</td>
    <td><input type="text" size="25" readonly></td>
    </tr>
    
    <tr>
    <td>Date of Birth:</td>
    <td><input type="text" size="7" readonly></td>
    </tr>
    
    </table>
    
    </body>
    </html>

  12. #12
    Join Date
    Oct 2005
    Posts
    33

    Thank you very much

    Hi GPH

    Its fantastic what you have done and really appreciated. I did not think it was possible but you have proved a lot of people wrong.

    I have a lot of databases to print so if you could please take it back to browsing for the file that would be great

    many thanks again

  13. #13
    Join Date
    Apr 2005
    Posts
    387
    no problem. I'll post it in a bit

  14. #14
    Join Date
    Apr 2005
    Posts
    387
    Do you want it to remove an old query before adding a new one or do you want to append new queries to the bottom of old ones ?

  15. #15
    Join Date
    Oct 2005
    Posts
    33

    Please remove old queries

    Please could you remove the old queries as once I have dealt with a database the information will be input into the new form and saved on the hard drive so we can do away with the old one's

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center