[RESOLVED] Import from CSV skipping records
Results 1 to 8 of 8

Thread: [RESOLVED] Import from CSV skipping records

  1. #1
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    resolved [RESOLVED] Import from CSV skipping records

    I need another brain. I seem to have lost mine. I have a script that purposely ignores several rows. Here's everything:

    Here's the table
    Code:
    CREATE TABLE `calls_to_dispatch`.`<table_name>` (
    	`IdNum` bigint(20) NOT NULL AUTO_INCREMENT,
    	`WorkDate` date NOT NULL,
    	`BBT` varchar(100) NOT NULL,
    	`FirstName` varchar(100),
    	`LastName` varchar(100),
    	`Address` varchar(100),
    	`City` varchar(100),
    	`Zip` varchar(12),
    	`ServiceWindow` varchar(100),
    	`Start` time,
    	`End` time,
    	`NodeID` varchar(100),
    	`WorkZone` varchar(100),
    	`Status` varchar(100),
    	`ICOMS` varchar(100),
    	`ManualICOMS` varchar(100),
    	`QCode` varchar(100),
    	`WorkSkill` varchar(100),
    	`WorkPoints` int(7),
    	`DispatchComments` varchar(100),
    	`AccountNum` varchar(100),
    	`CustName` varchar(100),
    	`Notes` varchar(200),
    	`FaultCode` varchar(100),
    	`WOComments` varchar(250),
    	`TagNum` varchar(100),
    	`Payment` decimal(7,2),
    	`Duration` time,
    	`WOClass` varchar(100),
    	`WorkOrder` varchar(100),
    	`CODAmount` decimal(7,2),
    	PRIMARY KEY (`IdNum`, `Address`)
    ) ENGINE=`InnoDB` COMMENT='';
    Here's the php script:
    PHP Code:
    <?php

    $host
    ="localhost";
    $user="root";
    $password="password";
    $Dbname "calls_to_dispatch";


    $cxn mysql_connect($host,$user,$password)
    or die (
    mysql_error() . "couldn’t connect to server");


    mysql_select_db($Dbname$cxn)
    or die(
    mysql_error() . "couldn't select database");

    ini_set('auto_detect_line_endings'true);



    $ImportFile $_FILES['userfile']['name'];





        
        
    $TimsPath "TOA/";


    chdir ($TimsPath);

    $try glob('Texoma_*.csv');
    foreach (
    $try as $ImportFile)
      {
      echo 
    $ImportFile "<br>";
      



    $ReadFile fopen($ImportFile"r") or die("You dope, this file is unreachable");
        
    while ((
    $LineArray fgetcsv($ReadFile1000",")) !== FALSE
        {
        
    $BBT addslashes($LineArray[0]);
        
    $Address addslashes($LineArray[1]);
        
    $City addslashes($LineArray[2]);
        
    $Zip addslashes($LineArray[3]);
        
    $ServiceWindow addslashes($LineArray[4]);
        
    $Start addslashes($LineArray[5]);
        
    $End addslashes($LineArray[6]);
        
    $NodeID addslashes($LineArray[7]);
        
    $WorkZone addslashes($LineArray[8]);
        
    $Status addslashes($LineArray[9]);
        
    $ICOMS addslashes($LineArray[10]);
        
    $ManualICOMS addslashes($LineArray[11]);
        
    $QCode addslashes($LineArray[12]);
        
    $WorkSkill addslashes($LineArray[13]);
        
    $WorkPoints addslashes($LineArray[14]);
        
    $DispatchComments addslashes($LineArray[15]);
        
    $AccountNum addslashes($LineArray[16]);
        
    $CustName addslashes($LineArray[17]);
        
    $Notes addslashes($LineArray[18]);
        
    $FaultCode addslashes($LineArray[19]);
        
    $WOComments addslashes($LineArray[20]);
        
    $TagNum addslashes($LineArray[21]);
        
    $PaymentAmount addslashes($LineArray[22]);
        
    $Duration addslashes($LineArray[23]);
        
    $WOClass addslashes($LineArray[24]);
        
    $WorkOrder addslashes($LineArray[25]);
        
    $COD addslashes($LineArray[26]);














        
    $BBTArray explode("-"$BBT);
            
    $TechNum addslashes($BBTArray[0]);
            
    $Title1 addslashes($BBTArray[2]);
            
    $Title2 addslashes($BBTArray[3]);
                            
            if (
    $Title2)
                {
                
    $Title $Title1 ' - ' $Title2;
                }
                else 
    $Title $Title1;
                            
                
    $Name explode(" "$BBTArray[1]);
                
    $FirstName addslashes($Name[0]);
                
    $LastName addslashes($Name[1]);
                            
                        
                            
                
    $WorkZoneArray explode("-"$WorkZone);
                
    $SysDbase addslashes($WorkZoneArray[0]);
                
    $System addslashes($WorkZoneArray[1]);
                
    $Area addslashes($WorkZoneArray[2]);
                
                
    $FileDate explode("_"$ImportFile);
                    
    $Month addslashes($FileDate[1]);
                    
    $Day addslashes($FileDate[2]);
                    
    $ShortYear addslashes($FileDate[3]);
                    
                    if (
    $ShortYear 2000$ShortYear 2000 $ShortYear;
                    
                    
    $WorkDate $ShortYear '-' $Month '-' $Day;
                    
                                
            
                            
                    
            echo 
    $WorkDate ' !! ' $TechNum ' !! ' $FirstName ' !! ' $LastName ' !! ' $Title ' !! ' $Type '<br>';
            
            if (!
    stripos($LineArray[0], 'BBT'0)) {
            
                
    $Quest "INSERT INTO TOAWorkOrdersNew
                    (WorkDate, 
                    BBT, 
                    TechNum, 
                    FirstName, 
                    LastName, 
                    Title,
                    Address,
                    City,
                    Zip,
                    ServiceWindow,
                    Start,
                    End,
                    NodeID,
                    WorkZone,
                    Status,
                    ICOMS,
                    ManualICOMS,
                    QCode,
                    WorkSkill,
                    WorkPoints,
                    DispatchComments,
                    AccountNum,
                    CustName,
                    Notes,
                    FaultCode,
                    WOComments,
                    TagNum,
                    Payment,
                    Duration,
                    WOClass,
                    WorkOrder,
                    CODAmount
                    
                    ) VALUES 
                    ('
    $WorkDate', 
                    '
    $BBT', 
                    '
    $TechNum', 
                    '
    $FirstName', 
                    '
    $LastName', 
                    '
    $Title',
                    '
    $Address',
                    '
    $City',
                    '
    $Zip',
                    '
    $ServiceWindow',
                    '
    $Start',
                    '
    $End',
                    '
    $NodeID',
                    '
    $WorkZone',
                    '
    $Status',
                    '
    $ICOMS',
                    '
    $ManualICOMS',
                    '
    $QCode',
                    '
    $WorkSkill',
                    '
    $WorkPoints',
                    '
    $DispatchComments',
                    '
    $AccountNum',
                    '
    $CustName',
                    '
    $Notes',
                    '
    $FaultCode',
                    '
    $WOComments',
                    '
    $TagNum',
                    '
    $Payment',
                    '
    $Duration',
                    '
    $WOClass',
                    '
    $WorkOrder',
                    '
    $CODAmount')";
                    
                    
                    
                    
                    
    $result mysql_query($Quest$cxn)  or die(mysql_error());     
                
            }
             
         }
         
    fclose($ReadFile);
        
    //unlink($ImportFile) or die('unlink failed  ' . mysql_error());

      
    }
      
      echo 
    '<script language="javascript">';
          echo 
    'window.alert("Import Completed")';
        echo 
    '</script>';

           
    mysql_close(); 
    ?>
    And the data:
    Code:
      85278-Alfred Alvarez-TNT	1828 SANDY POINT RD 2 2202	BRYAN	778078209		4:51 PM	5:01 PM	B333X	ETX-BY-Bryan/CS-Area 3	completed	Sent		6	SRO - Video(1/1)	2	DISC*	7355705	April Sanchez			LINE DISC RESCHEDULED FROM 05/15/13 SLOT L2	E219761		0:10	C	63740977_811	0
    85278-Alfred Alvarez-TNT	920 CLEARLEAF DR TRLR 273	BRYAN	778033534		5:12 PM	5:13 PM	B318A	ETX-BY-Bryan/CS-Area 3	completed	Sent		6	SRO - Video(1/1)	2	DISC*	258162601	Benjamin Flores			LINE DI RESCHEDULED FROM 05/13/13 SLOT L2			0:01	C	63725184_811	0
    26279-Jody Wiley-BBT IV (Line Tech)				08 AM - 08 AM	8:01 AM	9:23 AM			completed														1:22			
    26279-Jody Wiley-BBT IV (Line Tech)					9:39 AM	9:55 AM			completed														0:16			
    26279-Jody Wiley-BBT IV (Line Tech)					9:57 AM	12:21 PM			completed														2:24			
    26279-Jody Wiley-BBT IV (Line Tech)				11 AM - 02 PM	12:21 PM	1:22 PM			completed														1:01			
    
    83066-Brent Lawrence-BBT I (Training)	3130 E VILLA MARIA RD APT 809	BRYAN	778035071		6:49 PM	6:51 PM	B216B	ETX-BY-Bryan/CS-Area 2	completed	Sent		4	Disconnect - Non Pay (1/1)	2	DIGITAL	254073706	Mary Killman			BULK AUTOMATIC NON-PAY DISCONNECT. BULK DIGITAL	X659800		0:02	C	63716251_811	0
    83066-Brent Lawrence-BBT I (Training)	3132 E 29TH ST APT 97	BRYAN	778022722		6:58 PM	7:04 PM	B208A	ETX-BY-Bryan/CS-Area 2	completed	Sent		6	SRO - Video(1/1)	2	DISC*	254581809	Sarah Henry			LINE DISC RESCHEDULED FROM 05/08/13 SLOT L2	M496985		0:06	C	63702588_811	0
    83066-Brent Lawrence-BBT I (Training)	2929 STEVENS DR TRLR 25	BRYAN	778030666		7:17 PM	7:24 PM	B202X	ETX-BY-Bryan/CS-Area 2	completed	Sent		4	Disconnect - Non Pay (1/1)	2	CABLE	253722508	Olga Suarez			 AUTOMATIC NON-PAY DISCONNECT. RESCHEDULED FROM 04/30/13 SLOT L2	E219320		0:07	C	63737107_811	0
    Unassigned 811	524 HENDERSON ST	RUSK	757851370	08 AM - 12 PM	12:00 AM	12:45 AM	RK001		pending			5	Commercial(1/1)	9	COMMERCIAL	710893001	 CPA ANITA L WOODLEE			CTM 9036831002 DATA SERVICE OUT 	96597		0:45	D	921373_100	0
    Unassigned 811	6611 S FM 14	HAWKINS	757654797	08 AM - 12 PM	12:00 AM	12:45 AM	HA100		pending			5	Commercial(1/1)	9	COMMERCIAL	711349401	LY CPA'S MCNEELY & MCNEE			CTM GETTING INTERMITTENT SLOW SPEEDS HAS TO POWER CYCLE MODEM TO CLEAR	232140		0:45	D	921631_100	0

    For some unknown reason, the script goes from Alfred to Unknown, skipping Jody Wiley and Brent Lawrence. I don't see any difference between the rows.

    Another pair or two of eyes, please?
    Last edited by bradgrafelman; 05-21-2013 at 12:33 PM.

  2. #2
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,697
    Tim, I really think you should take your root password out of the PHP script
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  3. #3
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    admin, please blank out my stupidity

  4. #4
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,414
    Quote Originally Posted by timstring View Post
    For some unknown reason, the script goes from Alfred to Unknown, skipping Jody Wiley and Brent Lawrence. I don't see any difference between the rows.
    The difference is that the rows contain 'BBT' in the first column, which will make the condition in this if() statement:
    PHP Code:
    if (!stripos($LineArray[0], 'BBT'0)) { 
    evaluate to FALSE.
    Last edited by bradgrafelman; 05-21-2013 at 12:37 PM.

  5. #5
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    that did the trick

  6. #6
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,697
    Would you mind to mark your thread "Resolved" using the "Thread Tools"?

    Thanks! Glad you got it sorted.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  7. #7
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    Yes, sorted out after slapping self on the forehead and screaming "Why didn't I see that"

  8. #8
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,697
    Quote Originally Posted by timstring View Post
    Yes, sorted out after slapping self on the forehead and screaming "Why didn't I see that"
    C'est la vie ... been there, done that, got the T-shirt (but I try not to wear it to work on paydays )
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

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
  •