[Resolved] re-organising data

matbennett

Tried searching for a solution to this (all morning) but to be honest am not really sure what to search for.

I'm loading data from a CSV file into mySQL - no problems there. However the data is in a really annoying format and I want to "rotate it" before inserting the data.

Let me explain with examples. Here's the CSV (well - a very simplified version):

1111,2222,3333,4444
aaaa,bbbb,cccc,dddd
AAAA,BBBB,CCCC,DDDD

This isn't too useful. What I am trying to get is a table in mySQL that looks like:

1111 : aaaa : AAAA
2222 : bbbb : BBBB
3333 : cccc : CCCC
4444 : dddd : DDDD

I am guessing that I need to cycle through the data from the CSV, as I am now, but then do something clever with the array before I insert data. It's that clever bit that has me stuck!

If anyone can point me in the direction of an example - or even suggest what I should be searching for this would be a great help.

Ta'

abc

In your example...

you have (4) fields per row

field 1 = 1111
field 2 = 2222
field 3 = 3333
field 4 = 4444

So create an array with the number of fields that will be in a real row!

Then loop each row explode and trim each value adding it to your array! Then loop the result, imploding each array to build your "SINGLE" insert query!

simple example....


<?

$hold = array_fill ( 0, 4, array () );

$io = fopen ( 'the_csv_file.txt', 'r' );

$ln = 0;

while ( ! feof ( $io ) )
{
	if ( ( $line = trim ( fgets ( $io, 4096 ) ) ) )
	{
		$fields = explode ( ',', $line );

		for ( $x = 0; $x < sizeof ( $fields ); $x++ )
		{
			$hold[$x][$ln] = trim ( $fields[$x] );
		}

		$ln += 1;
	}
}

fclose ( $io );

for ( $y = 0; $y < sizeof ( $hold ); $y++ )
{
	$hold[$y] = "( '" . implode ( "', '", $hold[$y] ) . "' )";
}

$sql = "INSERT INTO table_name VALUES " . implode ( ', ', $hold );

mysql_query ( $sql ) or die ( my_sql_error () );


?>

abc

matbennett

Cheers for the response.

I did actually manage to get this working - although a lot less elegantly than your solution (with about 100 lines more code as well!).

I'll certainly give the above a go - if nothing else it will be quicker next time!

thanks.