Web Analytics Made Easy -
StatCounter How do I Move Data from an XTHML file to mysql database ? - CodingForum

Announcement

Collapse
No announcement yet.

How do I Move Data from an XTHML file to mysql database ?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How do I Move Data from an XTHML file to mysql database ?

    Hi

    I spent ages inputting my data into an Open Office Calc
    spreadsheet. I assumed that it would export it as a csv file
    but alas, it only gives options for XHTML and PDF.


    So my file starts off like this :

    Code:
    <title xml:lang="en-US">- no title specified</title><meta name="DCTERMS.title" content="" xml:lang="en-US"/><meta name="DCTERMS.language" content="en-US" scheme="DCTERMS.RFC4646"/><meta name="DCTERMS.source" content="http://xml.openoffice.org/odf2xhtml"/><meta name="DCTERMS.issued" content="2016-07-16T14:49:35.409000000" scheme="DCTERMS.W3CDTF"/><meta name="DCTERMS.modified" content="2016-08-01T15:27:35.494000000" scheme="DCTERMS.W3CDTF"/><meta name="DCTERMS.provenance" content="" xml:lang="en-US"/><meta name="DCTERMS.subject" content="," xml:lang="en-US"/><link rel="schema.DC" href="http://purl.org/dc/elements/1.1/" hreflang="en"/><link rel="schema.DCTERMS" href="http://purl.org/dc/terms/" hreflang="en"/><link rel="schema.DCTYPE" href="http://purl.org/dc/dcmitype/" hreflang="en"/><link rel="schema.DCAM" href="http://purl.org/dc/dcam/" hreflang="en"/><style type="text/css">
        @page {  }
        table { border-collapse:collapse; border-spacing:0; empty-cells:show }
        td, th { vertical-align:top; font-size:10pt;}
        h1, h2, h3, h4, h5, h6 { clear:both }
        ol, ul { margin:0; padding:0;}
        li { list-style: none; margin:0; padding:0;}
        <!-- "li span.odfLiEnd" - IE 7 issue-->
        li span. { clear: both; line-height:0; width:0; height:0; margin:0; padding:0; }
        span.footnodeNumber { padding-right:1em; }
        span.annotation_style_by_filter { font-size:95%; font-family:Arial; background-color:#fff000;  margin:0; border:0; padding:0;  }
        * { margin:0;}
        .gr1 { font-size:12pt; font-family:Liberation Serif; writing-mode:page; }
        .P1 { text-align:center ! important; }
        .ta1 { writing-mode:lr-tb; }
        .Default { font-family:Liberation Sans; }
        .ce1 { font-family:Liberation Serif; font-size:10pt; font-style:normal; text-shadow:none; text-decoration:none ! important; font-weight:normal; }
        .ce2 { font-family:Liberation Serif; font-size:10pt; font-style:normal; text-shadow:none; text-decoration:none ! important; font-weight:normal; }
        .ce3 { font-family:Liberation Serif; font-size:10pt; font-style:normal; text-shadow:none; text-decoration:none ! important; font-weight:normal; }
        .co1 { width:41.47mm; }
        .co2 { width:56.11mm; }
    
    ...
    Later I can see the Table headings ( languages )
    Code:
    </style></head><body dir="ltr"><table border="0" cellspacing="0" cellpadding="0" class="ta1"><colgroup><col width="181"/><col width="181"/><col width="181"/><col width="181"/><col width="245"/><col width="99"/><col width="99"/><col width="99"/><col width="99"/><col width="99"/><col width="321"/><col width="99"/><col width="99"/><col width="99"/><col width="99"/><col width="99"/><col width="99"/><col width="99"/><col width="99"/></colgroup><tr class="ro1"><td style="text-align:left;width:41.47mm; " class="Default">*</td><td style="text-align:left;width:41.47mm; " class="Default">*</td><td style="text-align:left;width:41.47mm; " class="Default">*</td><td style="text-align:left;width:41.47mm; " class="ce1"><p>Engl</p></td><td style="text-align:left;width:56.11mm; " class="ce1"><p>Span1</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Fren</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Ital</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>PtEu</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>PtBr</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Turk</p></td><td style="text-align:left;width:73.54mm; " class="Default"><p>Punj</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Beng</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Arab</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Russ</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Hind</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Chin</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Germ</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Roma</p></td><td style="text-align:left;width:22.58mm; " class="Default"><p>Japa</p></td></tr>
    And then my translations start ...

    The first word on the first row ...
    Code:
    <tr class="ro2"><td style="text-align:left;width:41.47mm; " class="Default">*</td><td style="text-align:left;width:41.47mm; " class="Default">*</td><td style="text-align:left;width:41.47mm; " class="Default"><p>People</p></td><td style="text-align:left;width:41.47mm; " class="ce1"><p>I</p></td><td style="text-align:left;width:56.11mm; " class="ce1"><p>yo</p></td><td style="text-align:left;width:22.58mm; " class="ce1"><p>moi / je</p></td><td style="text-align:left;width:22.58mm; " class="ce1"><p>io</p></td><td style="text-align:left;width:22.58mm; " class="ce1"><p>eu</p></td><td style="text-align:left;width:22.58mm; " class="ce1"><p>eu</p></td><td style="text-align:left;width:22.58mm; " class="ce1"><p>ben</p></td><td style="text-align:left;width:73.54mm; " class="ce2"><p>ਮੈਂmaiṁ</p></td><td style="text-align:left;width:22.58mm; " class="ce2"><p>আমিĀmi</p></td><td style="text-align:left;width:22.58mm; " class="ce2"><p>‫أنا‬ana</p></td><td style="text-align:left;width:22.58mm; " class="ce2"><p>ЯYa</p></td><td style="text-align:left;width:22.58mm; " class="ce2"><p>मैंmaiṁ</p></td><td style="text-align:left;width:22.58mm; " class="ce2"><p>我wǒ</p></td><td style="text-align:left;width:22.58mm; " class="ce2"><p>ich</p></td><td style="text-align:left;width:22.58mm; " class="ce3">*</td><td style="text-align:left;width:22.58mm; " class="ce2"><p>私watashi</p></td></tr>
    When I I had my data in a csv file I used the following code to get it into my table ...

    PHP Code:
    $sql "CREATE TABLE `phrases` (
      `rowno` int(8) NOT NULL AUTO_INCREMENT,
        `engl` varchar(200) default NULL,
        `span1` varchar(200) default NULL,
        `span2` varchar(200) default NULL,
        `span3` varchar(200) default NULL,
        `span4` varchar(200) default NULL,
        `fren` varchar(200) default NULL,
        `ital` varchar(200) default NULL,
        `port1` varchar(200) default NULL,
        `port2` varchar(200) default NULL,
        `turk` varchar(200) default NULL,
        `punj` varchar(200) default NULL,
        `type` varchar(3) default NULL,
        `catmain` varchar(5) default NULL,
        `catsub` varchar(5) default NULL,
        `level` tinyint(2) default NULL,
        `confirm` char(1) default NULL,
         PRIMARY KEY (rowno)
       
    ) ENGINE=INNODB DEFAULT CHARSET=utf8"
    ;
     
     
    $result mysql_query($sql)
        or 
    write_error("could not CREATE phrases.".mysql_error()."\r\n");

      
    write_log("Created phrases Table <br> date:$today \r\n");
      echo 
    "Created phrases Table <br>" ;
        
    $row 1;
    $handle2 fopen("lang1.txt""r");
     echo 
    "Opened lang1.txt <br>";
     
    while ((
    $data fgetcsv($handle21000",")) !== FALSE) {
      
    $num count($data);
      
    write_log("Line $row contains $num fields  \r\n");
      echo 
    "Line $row contains $num fields ";
        
      
    $Db_data0 safe_sql$data[0] );
      
    $Db_data1 safe_sql$data[1] );
      
    $Db_data2 safe_sql$data[2] );
      
    $Db_data3 safe_sql$data[3] );
      
    $Db_data4 safe_sql$data[4] );
      
    $Db_data5 safe_sql$data[5] );
      
    $Db_data6 safe_sql$data[6] );
      
    $Db_data7 safe_sql$data[7] );
      
    $Db_data8 safe_sql$data[8] );
      
    $Db_level 1;
        
        
    $sql "INSERT INTO `phrases` (engl,span1,fren,ital,port1,turk,type,catmain,level,confirm )
        VALUES ('
    $Db_data5','$Db_data0','$Db_data1','$Db_data2','$Db_data3','$Db_data4','$Db_data7','$Db_data8','$Db_level','y')";
                    
        
    $result mysql_query($sql) or die("could not execute INSERT query"mysql_error());

      
    write_log("Inserting words: $Db_data0$Db_data1$Db_data2$Db_data3$Db_data4$Db_data5$Db_data6$Db_data7$Db_data8, 'n'  \r\n");
        echo 
    "$row Inserting words: Db_data5, $Db_data0 , $Db_data1 , $Db_data2 , $Db_data3 , $Db_data4 , $Db_data7 , $Db_data8 , $Db_level , 'y' <br>";
      
    $row++; 

    Pretty simple

    But what can I do with this spreadsheet ?

    Is there a painless way to get it into my table ?

    Many thanks
    David.
    Last edited by VIPStephan; Sep 12, 2016, 03:00 PM. Reason: added code BB tags
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #2
    You should be able to save spreadsheet data as CSV. In fact: https://wiki.openoffice.org/wiki/Doc...d_text_file%3F
    Stop solving problems you don’t yet have!

    Comment


    • #3
      *
      *

      thank you !!!

      *
      *
      If you want to attract and keep more clients, then offer great customer support.

      Support-Focus.com. automates the process and gives you a trust seal to place on your website.
      I recommend that you at least take the 30 day free trial.

      Comment

      Working...
      X