www.webdeveloper.com
Page 1 of 10 123 ... LastLast
Results 1 to 15 of 142

Thread: problem with null fields

  1. #1
    Join Date
    Nov 2006
    Posts
    178

    problem with null fields

    ello all,
    i have an excel .csv file that i am using navicat to convert to mysql. the format i recieve the csv file in looks like this:

    Code:
    fname   lname    vend    op      date
    
    bob      smith    vend1   op1     2007-31-01
    0         0          0         op2     0000-00-00
    it looks the same after converting to sql. (sorry, can't make the columns line up on here for some reason)
    the null row with the exception of the "op field" is what is messing me up.
    bob had 2 things done under his name but when i search for bob smith and echo the "op" i only get the first op. if i search for "op2" and echo the results i get nothing.

    is there any way anyone can think of to make this work? the way the report is run is fixed and i am stuck trying to make this output work if possible.

    i appreciate any help or advice!

    thanks!
    Chris

  2. #2
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Can you post parts of the .csv file? At least all the lines that pertain to Bob Smith..

  3. #3
    Join Date
    Nov 2006
    Posts
    178
    i took a closer look at the file last night, it's like this:

    Code:
    bob      smith    vend1   op1     2007-31-01
                              op2
    after the navicat conversion it fills the blank fields with 0's like this:

    Code:
    bob      smith    vend1   op1     2007-31-01
    0        0        0       op2     0000-00-00

    if there is just a single row, everything works perfect. it is just when there are multiple "ops" under a single name that i can't pull the data properly.

    thanks~!

    Chris

  4. #4
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Navicat is doing the best it can. It just isn't able to guess the data left out due to repetition.

    A script will be needed to import the data - but it's not rocket science.

    How large is the file - usually?

  5. #5
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Would this be the basic format of your .csv?
    Code:
    "bob","smith","vend1","op1","2007-31-01"
    "","","","op2","0000-00-00"
    "","","","op3","0000-00-00"
    "buco","nine","vend2","op1","2007-01-02"
    "","","","op2","0000-00-00"
    "","","","op3","0000-00-00"
    If so, would lines 2 and 3 refer to Bob Smith and lines 5 and 6 refer to Buco Nine?

  6. #6
    Join Date
    Nov 2006
    Posts
    178
    exactly. the ops that go under the name are related to the name above. that is where the issue is. it has no way to relate the name to the empty rows below it.

    i'll see what i can tweak in navicat and see how it goes...

    Thanks again!

    Chris

    oh yea, the file is usually around 80 rows.
    Last edited by buconine; 02-01-2007 at 06:06 PM.

  7. #7
    Join Date
    Nov 2006
    Posts
    178
    well, had to go to a dbase file instead. i guess the report generator was having issues creating a csv. still have the same null problem...

    spoke with navicat, nothing the program can do. like you said it is just doing what it can with the data provided.

    is there a way to get the null rows to be related somehow? i was thinking there had to be a way to get the query to read the rows as one till it hits a row that is !== to "". does that make any sense? possible? am i screwed? lol

    thanks for the help!!

  8. #8
    Join Date
    Nov 2006
    Posts
    178
    crazy idea here...

    would it be possible to write something in php to take the data from the dbase file(comma delimited text) and insert it into the table like this:

    dbfile:

    Code:
    "bob","smith","vend1","op1","2007-31-01"
    "","","","op2","0000-00-00"
    "","","","op3","0000-00-00"
    to this:

    Code:
    "bob","smith',"vend1","op1","op2","op3","2007-31-01"
    and rewrite the table to have fields "op", "op1", "op2", etc

    possible? am i reaching? do i need more beer?

    thanks again!

  9. #9
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Your brother already owes us more beer than you can guzzle...

    It would be possible to alter a (MySQL) table on the fly though I see that as a highly questionable practice.

    The cleanest solution would be to have one table with fields:
    "pk_id","fname","lname","vend"
    and then another one with fields:
    "fk_id", "op"
    This way, you wouldn't care how many "op1" and "op2" showed up in the excel/.csv sheet.

    Of couse, if "op"-types are limited to 3, then use 3 fields. But if the list/length is variable, the above option is your lifeline, I think.

  10. #10
    Join Date
    Nov 2006
    Posts
    178
    how would i split the info between the tables? send it all to one table then use php to fill the other tables? i can't split the data in navicat during conversion. only way would be after it is in sql and on the server.

    the number of ops is not a constant, most i've seen on the reports is 12 to a specific person and that is pretty rare.

    heh, i thought we had this thing beat till we started throwing live data at it.

    i'll play with navicat and see what i can do...

    Thanks!!!!!!!!

  11. #11
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Navicat won't do it for you.
    A script will.

  12. #12
    Join Date
    Nov 2006
    Posts
    178
    so was i correct in thinking that i would let navicat set the data in the table, then write a script to populate 2 other tables from the main table?

    or were you thinking forget navicat and just write a script to take the data and convert it myself?

  13. #13
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    I was thinking to let the script read the .csv file and insert/update your table as needed.

    Otherwise, you'll need one table for the Navicat import and another for your ultimate data destination.

    The fewer middlemen, the richer we are...

  14. #14
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Unless we are the middlemen

  15. #15
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    PHP Code:
    <?php
    $importFILE 
    "buconine.csv";

    // Array to contain imported CSV values
    $arrRECORDS = array();

    // Remember the last...
    $lastFNAME "";
    $lastLNAME "";
    $lastVEND  "";
    $lastDATE  "";

    IF (!
    $fh fopen($importFILE,"r")) :
      print 
    "Error opening .CSV file.";
    ELSE :
      WHILE (!
    feof($fh)) :
        
    // Read next line and remove leading/trailiing white space
        
    $thisLINE trim(fgets($fh,1024));
        IF (
    $thisLINE <> "") :
          
    // Break fields down into array elements
          
    $arrCURREC explode(","$thisLINE);
          FOREACH (
    $arrCURREC as $key => $val) :
            
    // Remove leading and trailing double quotes
            
    $arrCURREC[$key] = trim(substr($val,1,-1));
            IF (
    $arrCURREC[$key] == "0000-00-00") :
              
    $arrCURREC[$key] = "";
            ENDIF;
          ENDFOREACH;
          
    $lastFNAME $arrCURREC[0] == "" $lastFNAME $arrCURREC[0];
          
    $lastLNAME $arrCURREC[1] == "" $lastLNAME $arrCURREC[1];
          
    $lastVEND  $arrCURREC[2] == "" $lastVEND  $arrCURREC[2];
          
    $lastDATE  $arrCURREC[4] == "" $lastDATE  $arrCURREC[4];
          
    $arrCURREC[0] = $lastFNAME;
          
    $arrCURREC[1] = $lastLNAME;
          
    $arrCURREC[2] = $lastVEND;
          
    $arrCURREC[4] = $lastDATE;
          
    $arrRECORDS[] = $arrCURREC;
        ENDIF;
      ENDWHILE;
      print 
    "<pre>";
      
    print_r($arrRECORDS);
      print 
    "</pre>";
    ENDIF;
    ?>
    Live @ http://www.nightshift58.com/webdev/f...le_import2.php

    The input file is: http://www.nightshift58.com/webdev/f...t/buconine.csv

    That would be Step 1, getting all your lines in order. Step 2 is doing something with it... What do you want to do with that now?

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



Recent Articles