Asked  11 Months ago    Answers:  5   Viewed   15 times

I'm trying to parse CSV files uploaded by the user through PHP, but it's not working properly.

I've uploaded several properly formatted CSVs and it worked fine, however; I have many users trying to import CSV files exported from Excel and they are having problems. I've compared the files to mine and noticed that the Excel files all lack quotes around the entries. Aside from that, they are identical. If I open it and save it with Open Office, without making any changes at all it works. So I'm fairly certain it's related to the quotes.

My question is; how do I read these improperly formatted CSVs?

UPDATE: Cause has been found!

This is specific to the Mac version of Excel. Line breaks are handled differently on Macs for some arbitrary reason, so before using fgetcsv, you should do this;

ini_set('auto_detect_line_endings',TRUE);

 Answers

1

This is specific to the Mac version of Excel. Line breaks are handled differently on Macs for some arbitrary reason, so before using fgetcsv, you should do this;

ini_set('auto_detect_line_endings',TRUE);
Thursday, July 29, 2021
4

I think you need to define a text qualifier, this is called enclosure in fgetcsv. eg:

$values = fgetcsv($f,8098,'"'); 
// the last part is: singlequote doublequote singlequote,
// you could also use an escape """ 

This is to tell the parser that it shouldn't take the comma inside the text area.

You should be able to do:

$values[0] == '"Total"'

UPDATE:

You problem with the file opening oddly, is probably related to the files encoding, by creating a new file, you created it with a new encoding maybe ANSI/UTF-8/Unicode something the original file wasnt, and then copied the content in there, so now it was saved in that format instead.. im not sure why this would give you the problems you are experiencing, but im assume the parser somehow cant handle the wrong encoding.

try opening notepad and notice when you click save as, you can choose encoding in the buttom, try with a few different files with different encoding if you want to make sure that was it?

Saturday, May 29, 2021
 
Exoon
 
5

OK, solved.

This is what everyone was suspecting: the encoding of the file was messed up. I could not know which encoding this was, but LibreOffice proposed me Unicode whenever I tried to open the CSVs.

I had to open them with nano to realize there was indeed an encoding problem. Gedit, vim or any other tool I had on my computer raised no errors. When opened with nano, an @ symbol was inserted between every other characters and line feeds were not read correctly.

It seems there are some encodings that are not well supported by fgetcsv. To solve the problem, I recreated the files from nano (copy-paste from another tool that did not display the @).

Saturday, May 29, 2021
 
jcubic
 
5

Just working from memory, try something like

<?php
  $display = 100;
  $counter = 1;
  $country = $_GET['country'];
  $storedcountries = array();//Store countries that have already been read
  echo "<ol>";
  $file_handle = fopen("csv/file.csv", "r");
  while (($line_of_text = fgetcsv($file_handle, 1024, ",")) !== false) {
      if ($line_of_text[13] == $country && !in_array($storedcountries, $line_of_text[13]) {//Make sure the country is not already stored in the $storedcountries array
          echo "<li>City:" . $line_of_text[15]) . "</li>";

          $counter++;
          if ($counter == $display) {
              break;
              echo "</ol>";
          }
          $storedcountries[] = $line_of_text[15];
      }
  }
  fclose($file_handle);
?>
Saturday, May 29, 2021
 
SkyNet
 
3

If your are on Scala 2.13 then you should use the Using object:

import scala.util.Using
val a: Try[Int] = Using(new FileInputStream("/tmp/someFile")) { fileInputStream =>
  // Do what you need in fith you fileInputStream here.
}

It takes two functions. The first one is a function that can create or provide the closable resource, and the second function is the one that takes the closable resource as a parameter, and can use it for something. Using will then in simple terms do the following for you:

  1. Call the first function to create the closable resource.
  2. Call the second function, and provide the resource as a parameter.
  3. Hold on to the returned value of the second function.
  4. Call close on the resource.
  5. Return the value (or exception) it got from the second function wrapped in a Try.

Using can be used on many other things than Classes that implements AutoCloseable, you just have to provide an implicit value, telling Using how to close your specific resource.

In older versions of scala, there is no directly support for javas try-with-resources construct, but your can pretty easy build your own support, by applying the loan pattern. The following is a simple but not optimal example, that is easy to understand. A more correct solution is given later in this answer:

import java.lang.AutoCloseable

def autoClose[A <: AutoCloseable,B](
        closeable: A)(fun: (A) ⇒ B): B = {
    try {
        fun(closeable)
    } finally {
        closeable.close()
    }
}

This defines a reusable method, that works pretty much like a try-with-resource construct in java. It works by taken two parameter. First is takes the a subclass of Autoclosable instance, and second it takes a function that takes the same Autoclosable type as a paremeter. The return type of the function parameter, is used as return type of the method. The method then executes the function inside a try, and close the autocloseble in its finally block.

You can use it like this (here used to get the result of findAny() on the stream.

val result: Optional[String] = autoClose(Files.lines(Paths.get("somefile.txt"))) { stream ⇒
    stream.findAny()
}

In case your want to do catch exceptions, you have 2 choices.

  1. Add a try/catch block around the stream.findAny() call.

  2. Or add a catch block to the try block in the autoClose method. Note that this should only be done, if the logic inside the catch block is usable from all places where autoClose is called.

Note that as Vitalii Vitrenko point out, this method will swallow the exception from the close method, if both the function supplied by the client, and the close method on the AutoCloseable throws an exception. Javas try-with-resources handles this, and we can make autoClose do so to, by making it a bit more complex:

  def autoClose[A <: AutoCloseable,B](
      closeable: A)(fun: (A) ⇒ B): B = {

    var t: Throwable = null
    try {
      fun(closeable)
    } catch {
      case funT: Throwable ⇒
        t = funT
        throw t
    } finally {
      if (t != null) {
        try {
          closeable.close()
        } catch {
          case closeT: Throwable ⇒
            t.addSuppressed(closeT)
            throw t
        }
      } else {
        closeable.close()
      }
    }
  }

This works by storing the potentially exception the client function throws, and adding the potential exception of the close method to it as a supressed exception. This is pretty close to how oracle explains that try-with-resource is actually doing it: http://www.oracle.com/technetwork/articles/java/trywithresources-401775.html

However this is Scala, and a lot of people will prefer to program in a more functional way. In a more functional way, the method should return a Try, instead of throwing an exception. This avoids a side effect of throwing an exception, and makes it clear to the client that the response may be a failure that should be handled (as pointed out in the answer by Stas). In a functional implementation, we would also like to avoid having a var, so a naive attempt could be:

  // Warning this implementation is not 100% safe, see below
  def autoCloseTry[A <: AutoCloseable,B](
      closeable: A)(fun: (A) ⇒ B): Try[B] = {

    Try(fun(closeable)).transform(
      result ⇒ {
        closeable.close()
        Success(result)
      },
      funT ⇒ {
        Try(closeable.close()).transform(
          _ ⇒ Failure(funT),
          closeT ⇒ {
            funT.addSuppressed(closeT)
            Failure(funT)
          }
        )
      }
    )
  }

This could them be called like this:

    val myTry = autoCloseTry(closeable) { resource ⇒
      //doSomethingWithTheResource
      33
    }
    myTry match {
      case Success(result) ⇒ doSomethingWithTheResult(result)
      case Failure(t) ⇒ handleMyExceptions(t)
    }

Or you could just call .get on myTry to make it return the result, or throw the exception.

However as Kolmar points out in a comment, this implementation is flawed, due to how the return statement works in scala. Consider the following:

  class MyClass extends AutoCloseable {
    override def close(): Unit = println("Closing!")
  }

  def foo: Try[Int] = {
     autoCloseTry(new MyClass) { _ => return Success(0) }
  }

  println(foo)

We would expect this to print Closing!, but it will not. The problem here is the explicit return statement inside the function body. It makes the method skip the logic in the autoCloseTry method, and thereby just returns Success(0), without closing the resource.

To fix that problem, we can create a mix of the 2 solutions, one that has the functional API of returning a Try, but uses the classic implementation based on try/finally blocks:

    def autoCloseTry[A <: AutoCloseable,B](
        closeable: A)(fun: (A) ⇒ B): Try[B] = {

      var t: Throwable = null
      try {
        Success(fun(closeable))
      } catch {
        case funT: Throwable ⇒
          t = funT
          Failure(t)
      } finally {
        if (t != null) {
          try {
            closeable.close()
          } catch {
            case closeT: Throwable ⇒
              t.addSuppressed(closeT)
              Failure(t)
          }
        } else {
          closeable.close()
        }
      }
    }

This should fix the problem, and can be used just like the first attempt. However it shows that this a bit error prone, and the faulty implementation has been in this answer as the recommended version for quite some time. So unless you trying to avoid having to many libraries, you should properly consider using this functionality from a library. I think that there is already one other answer pointing to one, but my guess is that there is multiply libraries, that solves this problem in different ways.

Tuesday, September 14, 2021
 
Thomas
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :