Extracting data from CSV file

@bashy any idea's, seen as you helped on many issues I've had recently.

@lstables, I have used http://csv.thephpleague.com/ in the past for this.

It makes it pretty simple.

That's what I'm using and everything uploads but I'm wanting to know once upload how to extract certain data from a certain column.

You could use the each method and iterate through it or query it using filtering methods.

https://github.com/thephpleague/csv/blob/master/examples/filtering.php

Yes I understand that, but I mean is not using this package, just extract data from this specific column and match to categories table

maybe you can share how you read the data.

Not using the package, extract the CSV data, loop through it, grab that data and match it to the column. You can query the db to get the column info.

You're probably going to be better off using the package because it does all of that leg work for you in a nice clean interface.

Apologies here's the code

 // CSV File upload of Books
    public function upload()
    {

        if (Input::hasFile('file')) {

            $file = Input::file('file');
            $name = time() . '-' . $file->getClientOriginalName();
            $moved = $file->move(public_path() . '/uploads/csv', $name);

            DB::table('books')->truncate();
            DB::table('authors')->truncate();

            $csv = new Reader($moved);
            $csv->setOffset(1);
            $nbInsert = $csv->each(function ($row) use (&$sth) {

            // Insert everything from the CSV file to the books table
            DB::table('books')->insert(

                array(
                    'ref' => (isset($row[0]) ? $row[0] : ''),
                    'date' => (isset($row[1]) ? $row[1] : ''),
                    'sold' => (isset($row[2]) ? $row[2] : ''),
                    'author' => (isset($row[3]) ? $row[3] : ''),
                    'title' => (isset($row[4]) ? $row[4] : ''),
                    'place_date' => (isset($row[5]) ? $row[5] : ''),
                    'description' => (isset($row[6]) ? $row[6] : ''),
                    'price' => (isset($row[7]) ? $row[7] : ''),
                    'keywords' => (isset($row[8]) ? $row[8] : ''),
                    'classification' => (isset($row[9]) ? $row[9] : ''),
                    'cost' => (isset($row[10]) ? $row[10] : ''),
                    'notes' => (isset($row[11]) ? $row[11] : ''),
                    'isbn' => (isset($row[12]) ? $row[12] : ''),
                    'publisher' => (isset($row[13]) ? $row[13] : ''),
                    'pub_date' => (isset($row[14]) ? $row[14] : ''),
                    'binding' => (isset($row[15]) ? $row[15] : ''),
                    'condition' => (isset($row[16]) ? $row[16] : '')
                )
            );

            // Insert author name and book id into authors table
            DB::table('authors')->insert(
                array(
                    'name' => (isset($row[3]) ? $row[3] : ''),
                    'book_id' => (isset($row[0]) ? $row[0] : '')
                )
            );

            return true;
            });
        }

        return Redirect::to('admin/books')->with('flash_success', 'Upload completed & new data inserted.');
    }

So in the first array I need the classification column to then put what ever data is in there into separate strings, so I can then match to my other table.

In this column it has data like sports & pastimes: angling so how would I get that and make it into 2 entities.

do you mean from "sports & pastimes: angling" into array("sports & pastimes" , "angling")?

^^ and do they actually go into separate columns or is category a related table?

yes, something like that, has the CSV file has it has "sports & pastimes: angling" so obviously that's not what I need. But something like array("sports & pastimes" , "angling") would work then i can use that as 2 separate entities

@dberry I have a categories table that has a relationship yes

As you can see from this http://cl.ly/image/1A1M1I0Y2P3u

You can see that it's not just one but many and these are the categories each book belongs to.

$categories =  explode(":", $value);

will this do?

Where does the $value come from? @faisal_arbain

...
'classification' => (isset($row[9]) ? explode(":",$row[9]) : ''),
...

this is the column you want to split right?

Yes

but then it will return array. I dont think you want to store array in the table. where do you intend to store the array and what information this table store? or is the other table will store ref id for this record?

Basically I want to show books associated with the author, but because that table has this classification column rather than and ID or something it won't work

For example, let me show everything I have currently.

Models

class Authors extends \Eloquent {

    protected $guarded = ['id'];

    public $table = 'authors';

    public $timestamps = false;

    public function books()
    {
        return $this->hasMany('Books', '');
    }
}

class Books extends \Eloquent {

    protected $guarded = ['id'];

    public function author()
    {
        return $this->belongsToMany('Authors');
    }

}

Then just call in controller like so:

public function index()
    {
        $authors = Authors::all();

        return View::make('authors.list', compact('authors'));
    }

Which then I call in my view like so:

 @foreach ($authors as $author)
            <tr>
                <td>{{ $author->name }}</td>
                <td>{{ $author->books->first()->title }}</td> // This line produces the error below
            </tr>
            @endforeach

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'books.authors_id' in 'where clause' (SQL: select * from `books` where `books`.`authors_id` = 2) 

Because there's no matching ID just the text we've talking about.

There's no authors_id column its the classification column I need and need to be able to explode has you mentioned before to separate them

1. Sorry. I a bit confius.

in your migration script... you insert book_id into your authors table. This indicate that 1 author may have only 1 book.

            // Insert author name and book id into authors table
            DB::table('authors')->insert(
                array(
                    'name' => (isset($row[3]) ? $row[3] : ''),
                    'book_id' => (isset($row[0]) ? $row[0] : '')
                )
            );

but then in your Author eloquent model, you set that author may have many books.

     public function books()
    {
        return $this->hasMany('Books', '');
    }

and...

in you Book eloquent model, you said that book can belongs to many author.

    public function author()
    {
        return $this->belongsToMany('Authors');
    }

can you clarify on that.

1. is there any particular reason you migration script did not use eloquent model to insert?

Don't know, just something I used before that worked.

ok. please correct me if im wrong. your modelling is as below:

• author can have many book
• book only can belongs to only one author
• book have many specification/category

Yes that's correct

So where am I going wrong?

warning: this is untested code. if you like to try, please make sure you create new branch.

1. Books Model

i change author relationship to belongsTo instead of belongsToMany, then I add new categories relationship

class Books extends \Eloquent{

    public function author()
    {
           return $this->belongsTo('Authors');
    }

    public function categories(){
          return $this->hasMany('Category');
    }

}

1. Category model

class Category extends \Eloquent{

   public function book(){
       return $this->belongsTo('Books');
   }
}

1. you migration script...

...

$nbInsert = $csv->each(function ($row) use (&$sth) {



    $book = Book::create(
        array(
            'ref' => (isset($row[0]) ? $row[0] : ''),
            'date' => (isset($row[1]) ? $row[1] : ''),
            'sold' => (isset($row[2]) ? $row[2] : ''),
            ...
        )
    );

    if(isset($row[9])){
        $categories = array();
        foreach(explode(":", $row[9]) as $category){
            $categories[] = new Category("name" => $category);
        }

        $book->categories()->saveMany($categories);
    }

    $author = Author::findOrCreate(array("name" => (isset($row[3]) ? $row[3] : '')));
    $author->books()->save($book);
}
...

Thanks, but I'm not wanting to insert into a categories table, think we need to take a step back here.