You are not logged in.

Important Note: This forum is being archived and will be completely locked down on the 7th of February. The new forum can be found at http://laravel.io.

Announcement

    Laravel 4 Advanced Architecture: Book Now On Sale

#1 2013-10-09 22:34:55

fungku
Artisan
Registered: 2013-03-17
Posts: 54

Seeding DB with CSV help

I've got a 50MB CSV file that I am trying to use to seed my DB.

Started trying to write the code for it but this gives me just an indexed array but I think I need array keys to match my table columns.

Is there an easy way to do this?

<?php

class PostalcodesTableSeeder extends Seeder {

	public function run()
	{
		// Uncomment the below to wipe the table clean before populating
		// DB::table('postalcodes')->truncate();

		$postalcodes = array();

		$file = fopen("zipcodes.csv","r");
		
		while(! feof($file)) {
			$postalcodes[] = fgetcsv($file);
		}
		
		fclose($file);

		// Uncomment the below to run the seeder
		DB::table('postalcodes')->insert($postalcodes);
	}

}

Last edited by fungku (2013-10-09 22:47:58)

Offline

#2 2013-10-09 23:01:27

BrunoG
Artisan
From: Portugal
Registered: 2012-04-10
Posts: 408

Re: Seeding DB with CSV help

Hi,

Does your csv file contain the columns at the top? If yes, it should be quite easy to accomplish that.


@ GitHub
@ Twitter
@ Skype: bruno.fgaspar

Offline

#3 2013-10-09 23:10:36

fungku
Artisan
Registered: 2013-03-17
Posts: 54

Re: Seeding DB with CSV help

BrunoG wrote:

Hi,

Does your csv file contain the columns at the top? If yes, it should be quite easy to accomplish that.

First four lines of my CSV:

"A0A0A0","47.055640","-53.201979","Gander","NL"
"A0A1A0","46.950525","-52.943911","Cape Broyle","NL"
"A0A1B0","47.417446","-53.197852","Avondale","NL"
"A0A1C0","47.318728","-52.810695","St. John's","NL"

Last edited by fungku (2013-10-09 23:11:02)

Offline

#4 2013-10-09 23:12:08

fungku
Artisan
Registered: 2013-03-17
Posts: 54

Re: Seeding DB with CSV help

I tried this but it just runs forever and doesn't seem to end:

<?php

class PostalcodesTableSeeder extends Seeder {

	public function run()
	{
		// Uncomment the below to wipe the table clean before populating
		// DB::table('postalcodes')->truncate();

		$postalcodes = array();

		$file = fopen("zipcodes.csv","r");
		
		$i = 0;

		while(! feof($file))
		{
			$row = array();
			$row = fgetcsv($file);

			$postalcodes[$i]['postalcode'] = $row[0];
			$postalcodes[$i]['lat'] 		= $row[1];
			$postalcodes[$i]['lng'] 		= $row[2];
			$postalcodes[$i]['city'] 		= $row[3];
			$postalcodes[$i]['province'] 	= $row[4];

			$i++;
		}
		
		fclose($file);

		// Uncomment the below to run the seeder
		DB::table('postalcodes')->insert($postalcodes);
	}

}

Last edited by fungku (2013-10-09 23:18:29)

Offline

#5 2013-10-09 23:14:13

BrunoG
Artisan
From: Portugal
Registered: 2012-04-10
Posts: 408

Re: Seeding DB with CSV help

Try adding the columns at the very top of your csv file like

postalcode, lat, lng, city, province

Then try to use this code

$han = fopen('customers.csv', 'r');
$customers = array();
$columns = array_map(function($col) { return Str::slug($col, '_'); }, fgetcsv($han));
while (($row = fgetcsv($han)) !== false)
{
	$customers[] = array_combine($columns, $row);
}

I don't have a csv file to really test at this very moment, but i used this to read a csv file, populate the array with the column = value


@ GitHub
@ Twitter
@ Skype: bruno.fgaspar

Offline

#6 2013-10-09 23:31:04

fungku
Artisan
Registered: 2013-03-17
Posts: 54

Re: Seeding DB with CSV help

That seems to also just run forever. I've been cancelling the actions after probably about 10-15 minutes.

There are 913,172 rows and 5 columns. I don't think it should take over ten minutes.

Should I just let it run?

Last edited by fungku (2013-10-09 23:35:18)

Offline

#7 2013-10-09 23:46:28

BrunoG
Artisan
From: Portugal
Registered: 2012-04-10
Posts: 408

Re: Seeding DB with CSV help

What is taking forever?

Inserting the data to the database? Or just reading the csv into an array?


@ GitHub
@ Twitter
@ Skype: bruno.fgaspar

Offline

#8 2013-10-10 00:09:07

fungku
Artisan
Registered: 2013-03-17
Posts: 54

Re: Seeding DB with CSV help

When I comment out the line

DB::table('postalcodes')->insert($postalcodes);

then it just takes several seconds. When I uncomment that line it takes forever.

Last edited by fungku (2013-10-10 00:09:41)

Offline

#9 2013-10-10 00:10:58

BrunoG
Artisan
From: Portugal
Registered: 2012-04-10
Posts: 408

Re: Seeding DB with CSV help

Do a var_dump on your $postalcodes, see if everything is correct, see if you have the exact same columns on each row, i never had issues with it hanging but i know if i have one row that has or doesn't have a column it insertion fails


@ GitHub
@ Twitter
@ Skype: bruno.fgaspar

Offline

#10 2013-10-10 00:19:08

fungku
Artisan
Registered: 2013-03-17
Posts: 54

Re: Seeding DB with CSV help

I did a var_dump and skimmed over and all looks fine except for every item is a string.

would that mess up inserting into the table if the lat and lng columns are decimal?

Last edited by fungku (2013-10-10 00:19:37)

Offline

Board footer

Powered by FluxBB

');