We help IT Professionals succeed at work.

How Do I Escape Strings in the Header Row of an Upload File for either CSV or TXT

Ridgejp
Ridgejp asked
on
121 Views
Last Modified: 2017-03-25
Hi,

I'm using the following to import data into MySql Database via PHP, but how do I Escape Strings that are found in the header row of the data itself? In the example below the first header $order-id contains "-" which stops the import rountine.

How the best way to get around this without having to change the header rows?

while(($fileop = fgetcsv($handle,10000,",")) !== false)
        
            {
            
			$order-id			= $mysqli->real_escape_string($fileop[0]);

Open in new window

Comment
Watch Question

Dmitriiconsultant
CERTIFIED EXPERT

Commented:
PHP manual (http://php.net/manual/en/language.variables.basics.php) states:

Variable names follow the same rules as other labels in PHP. A valid variable name starts with a letter or underscore, followed by any number of letters, numbers, or underscores.
— no any «-»

Author

Commented:
So is there a way around the "-" or not?
Most Valuable Expert 2011
Author of the Year 2014

Commented:
This is a data-dependent problem.  Please post a small sample of the test data, so we can run a few tests and show you exactly how to do this.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
You often have to skip the first (header) row of a CSV file when you are importing it when some of the columns are numeric types and all you have in the header is text.  It is a data type mismatch.  It is not usually an issue with '-'.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Another thing to try.  Change $order-id to $order_id.  This is what Dmitrii was getting at.

Or you might try something encapsulated in curly braces.

Author

Commented:
Hi,

I tried wrapping the $order-id in '$order-id' and "$order-id" and ($order-id) and {$order-id} to no avail ... I'll take a bash at changing it from $order-ID to $order_ID tomorrow or later if I get chance.

In every case above I get the following error: -

Parse error: syntax error, unexpected '=' in /***/***/***/***.php .... this presumably relates to the "=" in the following: -

$order-id = $mysqli->real_escape_string($fileop[0]);

Remove the "-" in $order-ID and it works perfectly so my current work-around is to wash it through google-sheets (doing a find/replace on the header row) prior to re-outputting a revised downloaded csv.

J
Dmitriiconsultant
CERTIFIED EXPERT

Commented:
Ridgejp,
how do you construct MySQL query?

I really cannot imagine situation when a php variable's name must be equal to a MySQL table field's name or any "heder row".

Author

Commented:
Hi Dmitrii,

The file I'm importing into my database is from a 3rd party website which contains orders for products we sell through them. The 'header row' of the CSV file that I wish to map into my database is determined by the 3rd party website not ourselves.

A selection of the header rows is as follows:-

order-id
order-item-id
purchase-date
payments-date
buyer-email
buyer-name

Many if not all of the header rows contain "-" ... hope that additional information helps.

J
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Let me try this one again...

This is a data-dependent problem.  Please post a small sample of the test data, so we can run a few tests and show you exactly how to do this.  

The basic concept of test-driven development is valuable, and you should take advantage of our offer to show you tested and working code samples.  Free of charge.  All you have to do is show us some of your test data!

Author

Commented:
Apologies Ray must have missed that one - send it up shortly!

Author

Commented:
File enclosed - the data is completely made up for obvious reasons, however the content and file structure is exactly what we'd expect to receive.Download---Test-File.txt
Most Valuable Expert 2011
Author of the Year 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Ray,

Thanks very much for looking at this - I'll take a look and come back shortly.

J
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.