How to type out hidden characters in a variable?

General PHP Help
#1

I am working with importing a CSV file into a MySQL database. Looking at this file in Notepad++ looks like the delimiter is a tab. Is there a way in PHP to type out the characters /t /n and /r if they exist? I tried explode() print_r() addslashes(). None seem to display what I’m looking for.

Hope the block of code below renders correctly, first time on this site.

sections after
elseif ($row == 2) {
and
} elseif (($row > 2) and ($row <= $max_row)) {
Is where I’m trying to output content to see if hidden characters are detected.

<html>
<head>
    <title>AMB-Network Navigator</title>
    <link rel="stylesheet" href="../css/style.css" type="text/css" media="screen">
    <script src="../jquery-3.2.1.min.js"></script>
</head>
<body>
<?php
/** Display PHP errors **/
//error_reporting(E_ALL);
//ini_set('display_errors', 1);

include('index.php');
include_once('db.php');
$col_value[500000] = "";
$col_heading[500000] = "";
$sql = "";
try {
    foreach ($_FILES as $key => $value) {
        if (is_array($value)) {
            foreach ($value as $a => $b) {
                echo $a . ": " . $b . '<br>';
            }
        } else {
            echo $key . ": " . $value . '<br>';
        }
    }
    $size_limit = ini_get('upload_max_filesize');
    echo 'upload_max_filesize = ' . $size_limit . '<br>';
    $file = fopen($_FILES['file']['tmp_name'], "r");
    $count_file = new SplFileObject($_FILES['file']['tmp_name'], "r");
    $count_file->seek(PHP_INT_MAX);
    $max_row = $count_file->key() - 1;
    $row = 0;
    while (($data = fgetcsv($file, 0, "\t")) !== FALSE) {
        $row++;
        if ($row == 1) {
            $sql = "INSERT INTO report (sid, sn, modality, prod_line_desc, mpg_name, ll_name, region, country, city, customer, vos_status, return_type, recalculated_value, final_disposition, est_sales_date, est_sales_quarter, deinstall_date, opp_number, order_number, disposition_approval, est_arrival_date, installation_year, kanban_comments, sub_region, service_contract_number) ";
        } elseif ($row == 2) {
            echo nl2br($data[0]).$row.'<br>';
            $sql .= "VALUES (`" . trim($data[0]) . "`,`" . trim($data[1]) . "`,`" . trim($data[2]) . "`,`" . trim($data[3]) . "`,`" . trim($data[4]) . "`,`" . trim($data[5]) . "`,`" . trim($data[6]) . "`,`" . trim($data[7]) . "`,`" . trim($data[8]) . "`,`" . trim($data[9]) . "`,`" . trim($data[10]) . "`,
            `" . trim($data[11]) . "`,`" . trim($data[15]) . "`,`" . trim($data[21]) ."`,`" . trim($data[22]) . "`,`" . trim($data[23]) . "`,`" . trim($data[24]) . "`,`" . trim($data[25]) . "`,`" . trim($data[26]) . "`,`" . trim($data[27]) . "`,`" . trim($data[54]) . "`,`" . trim($data[56]) . "`,
            `" . trim($data[77]) . "`,`" . trim($data[79]) . "`,`" . trim($data[80]) ."`,`" . trim($data[82]) . "`)";
        } elseif (($row > 2) and ($row <= $max_row)) {
            echo nl2br($data[0]).$row.'<br>';
            $sql .= ", (`" . trim($data[0]) . "`,`" . trim($data[1]) . "`,`" . trim($data[2]) . "`,`" . trim($data[3]) . "`,`" . trim($data[4]) . "`,`" . trim($data[5]) . "`,`" . trim($data[6]) . "`,`" . trim($data[7]) . "`,`" . trim($data[8]) . "`,`" . trim($data[9]) . "`,`" . trim($data[10]) . "`,
            `" . trim($data[11]) . "`,`" . trim($data[15]) . "`,`" . trim($data[21]) ."`,`" . trim($data[22]) . "`,`" . trim($data[23]) . "`,`" . trim($data[24]) . "`,`" . trim($data[25]) . "`,`" . trim($data[26]) . "`,`" . trim($data[27]) . "`,`" . trim($data[54]) . "`,`" . trim($data[56]) . "`,
            `" . trim($data[77]) . "`,`" . trim($data[79]) . "`,`" . trim($data[80]) ."`,`" . trim($data[82]) . "`)";
        }
    }
    fclose($file);
    echo $sql;
#2

Is your goal to find out what the characters actually are (you can highlight them in Notepad++ and goto the Plugins/Convert/ASCII->HEX menu) or do you want php to do something based on what the characters are?

#3

I am trying to get PHP to spit out text like “[Column 1 Value] \t [Column 2 Value] \t [Column 3 Value] ect…”

When I tried the ASCII->HEX option I get popup:
PluginsManager::runPluginCommand Exception
bad allocation

I’m using the 32-bit version of Notepad++ since the 64-bit version does not support the NppExport plugin.

#4

Unfortunately, that doesn’t state what the overall goal is, why you are doing this at all, it just states what you are trying to make work.

If you are trying to debug query errors, you have two mistakes and one potential problem. 1) You have a mismatch between the number of columns and the number of data values for each row, 2) you are using back-tacks around the values, not single-quotes, and 3) if any of the csv values ‘contain’ any sql special characters, they will break the sql query syntax.

If you have more than about 2-3 fields/columns of data, don’t write out code for every possible column. Dynamically process the data, by defining array(s) that will tell general-purpose code what to do, then use php array functions to filter, produce, and process the data all at once. This will eliminate problem #1, since you can fix the defining array(s) in one place and a single instance of the code will just use that definition. You should also use a prepared query. This will eliminate problem #2 and #3.

How large is and how many lines does the CSV typically have? Your current code is building a multi-value INSERT query, so, unless the un-used columns contain a lot of data, the file will fit into memory all at once.

Edit: Also, how often is this code used, since that could determine how its performance in speed vs memory usage should be balanced.

#5

End goal is to take a csv file that has 111 columns and 1000 rows. I have done this with a different file that had | (pipe symbol) as a delimiter. That working version is about 20000 rows of data. I do not want to import all 111 columns.

Trying to find what magic value is needed to separate $data into an array for each column.

I will remove the code I copied from another php page and try to redesign this page from scratch.

#6

If you cannot get your notepad++ to work, all it would take is a simple php function to display non-printing characters as hex -

// show non-printing ASCII characters as hex
function show_chars($str)
{
	$output = '';
	foreach(str_split($str) as $char)
	{
		if($char >= '!' && $char <= '~')
		{
			$output .= $char;
		} else {
			$output .= '['.bin2hex($char).']';
		}
	}
	return $output;
}

An example program that processes a csv using a general purpose data driven design -

<?php

// the following assumes you have determined that the file has uploaded successfully, at $_FILES['file']['tmp_name']
$input_file = $_FILES['file']['tmp_name'];

// define the query column names	
$columns = ['sid', 'sn', 'modality', 'prod_line_desc', 'mpg_name', 'll_name', 'region', 'country', 'city', 'customer', 'vos_status', // 11
			'return_type', 'recalculated_value', 'final_disposition', 'est_sales_date', 'est_sales_quarter', 'deinstall_date', // 6
			'opp_number', 'order_number', 'disposition_approval', 'est_arrival_date', 'installation_year', 'kanban_comments', // 6
			'sub_region', 'service_contract_number']; // 2. 25 total
// define the corresponding CSV offsets
$offsets = [0,1,2,3,4,5,6,7,8,9,10, // 11
			11,15,21,22,23,24, // 6
			25,26,27,54,56,77, // 6
			79,80,82]; // 3.  26 total
			
// note: you can combine the above two arrays, by using the CSV offset values as the keys in the 1st array. you would then change the $keys = ... line of code to be $keys = array_keys($columns);
// since there's no way to know where the error is in the above, this example code will produce a wrong result
// this example uses the PDO extension

$header_lines = 1; // define the number of header lines to discard from the CSV file

// read the lines from the file
$lines = file($input_file,FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES);

// remove any header lines
$lines = array_slice($lines,$header_lines);

// create an array with the defined list(array) of CSV offsets as keys
$keys = array_flip($offsets); // change this line to $keys = array_keys($columns); if the $columns and $offsets arrays are combined

// produce a formatted set of prepared query place-holders for one row of data
$place_holders = '('.implode(', ',array_fill(0,count($columns),'?')).')';

$values = []; // array to hold each set of formatted VALUES ... for the query
$params = []; // array to hold the prepared query data values for the query

// process the data
foreach($lines as $line)
{
	// convert each CSV line to an array
	$row = str_getcsv($line, "\t");
	
	// filter out un-used elements
	$row = array_intersect_key($row, $keys);
	
	// trim the data
	$row = array_map('trim',$row);

	// add the formatted VALUES ... for one row of data
	$values[] = $place_holders; 

	// add the data for one row
	$params = array_merge($params,$row);
}
	
// build the sql query - because the column names could be anything, surround each with back-ticks
$sql = "INSERT INTO report (`".implode('`, `',$columns)."`) VALUES " . implode(', ',$values);

// examine the result for debugging
echo $sql; echo '<pre>'; print_r($params);

// using PDO, prepare and execute the query
$stmt = $pdo->prepare($sql);
$stmt->execute([$params]);
Sponsor our Newsletter | Privacy Policy | Terms of Service