Question Mark instead of space

[t_bergman]

I'm having an issue with schedule imports, specifically the route during import. When I import a schedule, the route imports question marks instead of spaces ie: MARCI?V7?SWAGS???.

Is there a fix? I have not changed anything in my environment, has anyone else seen this before?

 

Thanks,

[ProAvia]

Can't say as I've ever seen that before. In the database, is the 'route' field 'Type' set to 'text'? And you imported it as a CSV file - without any changes to the CSV formatting during editing?

Edited February 26, 2017 by ProAvia

[t_bergman]

13 hours ago, ProAvia said:

Can't say as I've ever seen that before. In the database, is the 'route' field 'Type' set to 'text'? And you imported it as a CSV file - without any changes to the CSV formatting during editing?

I've checked and everything is set to text, as well as within the csv excel sheet. I've been doing more troubleshooting and I haven't found an answer yet. 

[ProAvia]

What version of Excel are you using? When you saved the CSV, did you save as CSV (Comma delimited), CSV (MS-DOS) or another format? Also, when saving the CSV, be sure to select Yes if asked if you want to keep the workbook in this format (To keep this format, which leaves out any incompatible features, click Yes.).

I seem to remember saving as one of those and had an issue with the import (not the ??? in the route field though). If you saved in one, try another to see if that solves the issue.

Are you using the stock Import.php file in admin/modules/Import or an edited one? Maybe post the entire public function processimport () section for review here. I believe in version 5.5.2 it starts on line 314.

[t_bergman]

ProAvia,

Thanks for the reply, I'm using office 2010, I've tried saving an uploading the sheet in all of the csv file formats and nothing seems to make a different. 

I've attached my import.php file below. 

 public function processimport() {
        $this->checkPermission(IMPORT_SCHEDULES);
        echo '<h3>Processing Import</h3>';

        if (!file_exists($_FILES['uploadedfile']['tmp_name'])) {
            $this->set('message', 'File upload failed!');
            $this->render('core_error.php');
            return;
        }

        echo '<p><strong>DO NOT REFRESH OR STOP THIS PAGE</strong></p>';

        set_time_limit(270);
        $errs = array();
        $skip = false;


        # Fix for bug VMS-325
        $temp_name = $_FILES['uploadedfile']['tmp_name'];
        $new_name = CACHE_PATH . $_FILES['uploadedfile']['name'];
        move_uploaded_file($temp_name, $new_name);

        $fp = fopen($new_name, 'r');

        if (isset($_POST['header']))
            $skip = true;

        /* Delete all schedules before doing an import */
        if (isset($_POST['erase_routes'])) {
            SchedulesData::deleteAllSchedules();
        }


        $added = 0;
        $updated = 0;
        $total = 0;
        echo '<div style="overflow: auto; height: 400px; border: 1px solid #666; margin-bottom: 20px; padding: 5px; padding-top: 0px; padding-bottom: 20px;">';

        while ($fields = fgetcsv($fp, 1000, ',')) {
            // Skip the first line
            if ($skip == true) {
                $skip = false;
                continue;
            }

            // list fields:
            $code = $fields[0];
            $flightnum = $fields[1];
            $depicao = $fields[2];
            $arricao = $fields[3];
            $route = $fields[4];
            $aircraft = $fields[5];
            $flightlevel = $fields[6];
            $distance = $fields[7];
            $deptime = $fields[8];
            $arrtime = $fields[9];
            $flighttime = $fields[10];
            $notes = $fields[11];
            $price = $fields[12];
            $flighttype = $fields[13];
            $daysofweek = $fields[14];
            $enabled = $fields[15];
            $week1 = $fields[16];
            $week2 = $fields[17];
            $week3 = $fields[18];
            $week4 = $fields[19];

            if ($code == '') {
                continue;
            }

            // Check the code:
            if (!OperationsData::GetAirlineByCode($code)) {
                echo "Airline with code $code does not exist! Skipping...<br />";
                continue;
            }

            // Make sure airports exist:
            if (!($depapt = OperationsData::GetAirportInfo($depicao))) {
                $this->get_airport_info($depicao);
            }

            if (!($arrapt = OperationsData::GetAirportInfo($arricao))) {
                $this->get_airport_info($arricao);
            }

            # Check the aircraft
            $aircraft = trim($aircraft);
            $ac_info = OperationsData::GetAircraftByReg($aircraft);

            # If the aircraft doesn't exist, skip it
            if (!$ac_info) {
                echo 'Aircraft "' . $aircraft . '" does not exist! Skipping<br />';
                continue;
            }
            $ac = $ac_info->id;

            if ($flighttype == '') {
                $flighttype = 'P';
            }

            if ($daysofweek == '')
                $daysofweek = '0123456';

            // Replace a 7 (Sunday) with 0 (since PHP thinks 0 is Sunday)
            $daysofweek = str_replace('7', '0', $daysofweek);

            # Check the distance

            if ($distance == 0 || $distance == '') {
                $distance = OperationsData::getAirportDistance($depicao, $arricao);
            }

            $flighttype = strtoupper($flighttype);

            if ($enabled == '0')
                $enabled = false;
            else
                $enabled = true;

            # This is our 'struct' we're passing into the schedule function
            #	to add or edit it

            $data = array(
                'code' => $code, 'flightnum' => $flightnum, 'depicao' => $depicao,
                'arricao' => $arricao, 'route' => $route, 'aircraft' => $ac, 'flightlevel' => $flightlevel,
                'distance' => $distance, 'deptime' => $deptime, 'arrtime' => $arrtime,
                'flighttime' => $flighttime, 'daysofweek' => $daysofweek, 'notes' => $notes,
                'enabled' => $enabled, 'price' => $price, 'flighttype' => $flighttype,
                'week1' => $week1, 'week2' => $week2, 'week3' => $week3, 'week4' => $week4
            );

            # Check if the schedule exists:
            if (($schedinfo = SchedulesData::getScheduleByFlight($code, $flightnum))) {
                # Update the schedule instead
                $val = SchedulesData::updateScheduleFields($schedinfo->id, $data);
                $updated++;
            } else {
                # Add it
                $val = SchedulesData::addSchedule($data);
                $added++;
            }

            if ($val === false) {
                if (DB::errno() == 1216) {
                    echo "Error adding $code$flightnum: The airline code, airports, or aircraft does not exist";
                } else {
                    $error = (DB::error() != '') ? DB::error() : 'Route already exists';
                    echo "$code$flightnum was not added, reason: $error<br />";
                }

                echo '<br />';
            } else {
                $total++;
                echo "Imported {$code}{$flightnum} ({$depicao} to {$arricao})<br />";
            }
        }

        CentralData::send_schedules();

        echo "The import process is complete, added {$added} schedules, updated {$updated}, for a total of {$total}<br />";

        foreach ($errs as $error) {
            echo '&nbsp;&nbsp;&nbsp;&nbsp;' . $error . '<br />';
        }

        echo '</div>';

        unlink($new_name);
    }

It is the stock import. 

[ProAvia]

That's exactly the same as my import.php. I'm still using Excel 2003 for my CSV. I know there are some differences in mine and newer versions.

Open the CSV file you used for import in Notepad++ (instead of Excel) and see what's in the route column. Maybe that will give us an idea.

Another option may be to open the CSV in Open Office (freeware) and see what the route column looks like.

[t_bergman]

ProAvia,

I opened the file in Notepad++, this is definitely a local issue and not a phpvms or web host. Thanks for pointing me in the right direction for troubleshooting this one. I'm migrating to linuxmint slowly and I'm going to try it in LibreOffice and see how it goes. 

[ProAvia]

So Notepad++ showed the question marks? Then I'd guess it's most probably something to due with your version of Excel.

Please keep us updated on your migration and schedule import.

[t_bergman]

Yes, I saw quotations when I opened the local file in notepad++. Which means excel is the culprit and for some reason saving them inside the file. As soon as I mess around with it a bit more hopefully I have an answer about how to fix it.

[ProAvia]

Maybe this will help.... https://www.ablebits.com/office-addins-blog/2014/04/24/convert-excel-csv/

Look in the section titled: Export Excel to CSV with UTF-8 or UTF-16 encoding

Definitely don't open and re-save the file in Excel after you do this - or you'll need to do the above procedure again.