Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New to the CF scene
    Join Date
    Sep 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Spliting a string?

    Hi, I'm coding a program to help people at my office to update the database, and the idea is to copy the content of an excel file and generate an sql from that, but I'm having a bit of trouble splitting the info into different tables:

    Spliting a string?-captura-de-pantalla-2014-10-15-11.16.32.png

    When copying everything the empty cells are read as tabulated, so I made a for that searches for more than one tabulator in a row (or find a \n and a following tabulator character) to split the info into several strings and format the info into a sql table, but I'm stuck here:

    Code:
    				var X=document.getElementById('opc');
    				var z=X.value.length;
    				
    				for(i=0;i<z;i++){
    					if(X.charAt(i)=='	'&& X.chaAt(i+1)=='	'){
    						char=X.value.substring(0,i-1);
    						break;
    					}
                                    }
    I tried with X.value(i) but I get the same error 'X.charAt is not a function', can someone tell me what am I doing wrong? Thanks.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,679
    Thanks
    25
    Thanked 655 Times in 654 Posts
    Are you sure it says error 'X.charAt is not a function' and not error 'X.chaAt is not a function' which is what you wrote
    Code:
    if(X.charAt(i)=='	'&& X.chaAt(i+1)=='	'){


    PLUS X.charAt(i) -- x is not the string. you want it's X.value (That's a guess because we can't see your html code.)

    And I think char is a reserved word so I used cat and this:
    cat = X.value.substring(0, i);
    Because i-1 would cut the last character of the string off.
    Last edited by sunfighter; 10-15-2014 at 02:32 AM.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    New to the CF scene
    Join Date
    Sep 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah, I realised that after posting, but still got the same error. I managed to fix the 'No function error' but now I don't seem to get the conditions to apply. I changed the code to this

    Code:
    				var X=document.getElementById('opc');
    				var z=X.value.length;
    				
    				for(i=0;i<z;i++){
    					if(X.value[i]=='N' && X.value[i+1]=='U'){
    						char=X.value.substring(0,200);
    						X.value=char;
    						break;
    					}
    					
    					else{
    						X.value='Error';
    					}	
    				}
    but I can't get the If to work, so I get "Error" all the time.

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,679
    Thanks
    25
    Thanked 655 Times in 654 Posts
    As I said char <- THIS WORD Is a reserved word in javascript. YOU CAN'T USE IT so "char=X.value.substring(0,200);" is wrong!!!
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #5
    New to the CF scene
    Join Date
    Sep 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sunfighter View Post
    As I said char <- THIS WORD Is a reserved word in javascript. YOU CAN'T USE IT so "char=X.value.substring(0,200);" is wrong!!!
    Thanks for the input, I replaced all the char for another name, but nothing happens, the text remains the same.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    I really think you are making a mistake, trying to parse the copy/paste from a spreadsheet. There are just too many ways in which you can misinterpret whether a cell value is present or not. It's MUCH better to use Excel to convert the spreadsheet to a ".csv" file, which is then easy to parse in JavaScript (especially if you use TABs for the field separator and NEWLINE for the line separator).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    When copying everything the empty cells are read as tabulated,
    And what are *NON*-empty cells read as? Are they tab-delimited as well? Or are they delimited in some other way?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New to the CF scene
    Join Date
    Sep 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I really think you are making a mistake, trying to parse the copy/paste from a spreadsheet. There are just too many ways in which you can misinterpret whether a cell value is present or not. It's MUCH better to use Excel to convert the spreadsheet to a ".csv" file, which is then easy to parse in JavaScript (especially if you use TABs for the field separator and NEWLINE for the line separator).
    I saved the file to csv but I don't see any difference when copying/pasting the info, and of course it doesn't seem to work either. Nevertheless what's bothering me is that I can't make the If statement work even though I replaced the TABs with 2 letters to force the substring and nothing happens.

    And what are *NON*-empty cells read as? Are they tab-delimited as well? Or are they delimited in some other way?
    They are read as TAB because I can replace them using the replace function.
    Last edited by Ixnayz; 10-15-2014 at 05:00 AM.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    I get the gut feel you are working at this too hard.

    Here. This code converts a copy/pasted Excel spreadsheet (or any part of the sheet!) into an HTML <table>.
    PHP Code:
    <!DOCTYPE html>
    <
    html>
    <
    head>
    </
    head>
    <
    body>
    <
    form id="theForm">
    <
    textarea name="data" rows="10" cols="80"></textarea>
    <
    input name="demo" type="button" value="convert"/>
    </
    form>
    <
    div id="holder"></div>

    <
    script type="text/javascript">
    var 
    form document.getElementById("theForm");
    form.demo.onclick = function( )
    {
        var 
    div document.getElementById("holder");
        var 
    tbl document.getElementById("theTable");
        if ( 
    tbl != null ) { div.removeChildtbl ); }

        var 
    tbl document.createElement("table");
        
    tbl.border "1";
        
    tbl.id "theTable";

        var 
    data form.data.value;
        var 
    drows data.split(/n/g);
        for ( var 
    0drows.length; ++)
        {
            var 
    row tbl.insertRow( );
            var 
    dcells drows[r].split(/t/g);
            for ( var 
    0dcells.length; ++)
            {
                var 
    cell row.insertCell( );
                
    cell.innerHTML dcells[c];
            }
        }
        
    div.appendChildtbl );
    }
    </script>
    </body>
    </html> 
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Having said all that...

    I think you are making a big mistake even *bothering* to try to do this in JavaScript. You surely intend to save the data to the database using PHP (or other server-side system). So why not just send the raw copy/pasted data to your PHP page and let PHP do the splits that I did above?

    You could easily convert the entire data into a 2D PHP array and then you can pull out any cells you want from that array and save them as appropriate.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New to the CF scene
    Join Date
    Sep 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Having said all that...

    I think you are making a big mistake even *bothering* to try to do this in JavaScript. You surely intend to save the data to the database using PHP (or other server-side system). So why not just send the raw copy/pasted data to your PHP page and let PHP do the splits that I did above?

    You could easily convert the entire data into a 2D PHP array and then you can pull out any cells you want from that array and save them as appropriate.
    I hadn't thought about that...But the problem is to make things easier for the people who don't know anything about DB because I'm doing an internship and won't be around to deal with the data entry, so I thought that a js program would be more 'user friendly'. Anyway thanks for the code! I will take take a look at it and see how this ends.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    It's not a bad idea to let them do the copy/paste and then automatically convert that into a viewable HTML <table> so they confirm that they got all the data they were trying to get.

    But then I'd still just submit the raw pasted data to PHP and let it split it all up again.

    One thing you could do is hide the <textarea> before you do the convert to HTML <table> just to clean up the appearance of the page.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Senior Coder
    Join Date
    Sep 2010
    Posts
    2,451
    Thanks
    17
    Thanked 275 Times in 275 Posts
    I'm all with O P's idea of converting to a .csv file. You could make an upload page which converts to a .csv and then processes it for what you want. Javascript can only deal with .js files, and only if proper syntax is used. PHP, using 'read' and 'explode' can make a 2d array of a .csv and you can do anything you want with that. You could also write download.php files to download any results.

  • #14
    Senior Coder rnd me's Avatar
    Join Date
    Jun 2007
    Location
    Urbana
    Posts
    4,460
    Thanks
    11
    Thanked 600 Times in 580 Posts
    Quote Originally Posted by sunfighter View Post
    As I said char <- THIS WORD Is a reserved word in javascript. YOU CAN'T USE IT so "char=X.value.substring(0,200);" is wrong!!!
    don't shout, especially when you're wrong:
    Code:
    var char=12342;
    alert(char); // works for me since "char" is not a keyword in JS
    Last edited by rnd me; 10-15-2014 at 10:48 PM.
    my site (updated 2014/10/20)
    BROWSER STATS [% share] (2014/9/03) IE7:0.1, IE8:4.3, IE11:9.2, IE9:2.7, IE10:2.6, FF:16.8, CH:47.5, SF:7.8, NON-MOUSE:37%

  • #15
    Senior Coder rnd me's Avatar
    Join Date
    Jun 2007
    Location
    Urbana
    Posts
    4,460
    Thanks
    11
    Thanked 600 Times in 580 Posts
    Quote Originally Posted by Ixnayz View Post
    Hi, I'm coding a program to help people at my office to update the database, and the idea is to copy the content of an excel file and generate an sql from that, but I'm having a bit of trouble splitting the info into different tables:

    Click image for larger version. 

Name:	Captura de pantalla 2014-10-15 11.16.32.png 
Views:	27 
Size:	20.1 KB 
ID:	13473

    When copying everything the empty cells are read as tabulated, so I made a for that searches for more than one tabulator in a row (or find a \n and a following tabulator character) to split the info into several strings and format the info into a sql table, but I'm stuck here...
    why would you want to remove the tabs? i don't get that. you can split it into a 2d array if you censor the tabs.

    all you need to do it split on lines, then loop each line and split on tab, leaving you with a 2d array of array with a place for everything everything in it's place.
    you then feed that 2d array or data to php/sql, JSON is probably the easiest way to do that.
    my site (updated 2014/10/20)
    BROWSER STATS [% share] (2014/9/03) IE7:0.1, IE8:4.3, IE11:9.2, IE9:2.7, IE10:2.6, FF:16.8, CH:47.5, SF:7.8, NON-MOUSE:37%


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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