problem with null fields

[buconine]

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:

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

[NightShift58]

Can you post parts of the .csv file? At least all the lines that pertain to Bob Smith..

[buconine]

i took a closer look at the file last night, it's like this:

bob      smith    vend1   op1     2007-31-01
                          op2

after the navicat conversion it fills the blank fields with 0's like this:

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

[NightShift58]

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?

[NightShift58]

Would this be the basic format of your .csv?

"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?

[buconine]

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.

[buconine]

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!!

[buconine]

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:

"bob","smith","vend1","op1","2007-31-01"
"","","","op2","0000-00-00"
"","","","op3","0000-00-00"

to this:

"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!

[NightShift58]

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.

[buconine]

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!!!!!!!!

[NightShift58]

Navicat won't do it for you.
A script will.

[buconine]

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?

[NightShift58]

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...

[NightShift58]

Unless we are the middlemen

[NightShift58]

<?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?