Page 1 of 1

Field size and import stuff [SOLVED]

PostPosted: Wed Mar 10, 2010 8:27 am
by cantlep
Hi All,

I'm probably asking noddy questions here that someone has probably answered before but I'm struggling to find them.

1) Is there anyway in the csv import function to strip out the field/column titles (in this instance from excel) before the data is imported?

2) Whenever I export excel to csv and then import it, a blank entry always appears in the table that I have imported the data to. Does anyone know why?

Here's a snippet from the function - Have I missed something that would cause the extra empty row?

Code: Select all
$record->setValues(
array(
'CompanyReg'=>$CompanyReg,
'CompanyName'=>$CompanyName,
'CustomerType'=>$CustomerType,
'BusinessAddress_1'=>$BusinessAddress_1,
'BusinessAddress_2'=>$BusinessAddress_2,
'BusinessCity'=>$BusinessCity,
'BusinessCounty'=>$BusinessCounty,
'BusinessPostcode'=>$BusinessPostcode,
'AccountManager'=>$AccountManager,
'TotalSites'=>$TotalSites,
'TotalVolume'=>$TotalVolume,
'ProductsContracted'=>$ProductsContracted
)
);


3) Is there a way to change the width of a column in the "list" view? I was hoping one of the widget:atts configs would have worked but it doesn't seem to change it for the "list" view.

Thanks

Paul

Re: Field size and import stuff

PostPosted: Wed Mar 10, 2010 6:34 pm
by strast
I can't comment on your issue 1 and 2 as I've not explored that part of Xataface.
However, on number 3, there is indeed a way to display what you want to see in the list view.

In the table delegate class, you can write methods to define the display of row fields. Here's an example..

In this app, the event_date field wraps in the cell using a common date format. It shows like this:

Sat Feb
06,2010

Unacceptable.

So I wrote a table delegate class method that will specify the output code. The %field%__renderCell will do this for you.
The field is called event_date so the method is named event_date__renderCell. The HTML code in my
delegate method is sent to the cell which has a nowrap attribute defined in the style, so it shows on a single line.

Code: Select all
function event_date__renderCell(&$record){
      return '<div style="white-space:nowrap">'.date('D M d, Y',strtotime($record->strval( 'event_date' )).'</div>');
}


This displays as: Sat Feb 06, 2010
-Steve

Re: Field size and import stuff

PostPosted: Thu Mar 11, 2010 9:03 am
by cantlep
Hi Steve, Thanks muchly for the reply. I'll give that a go later on tonight.

I've sorted out issue 2. I found an entry in the forum regarding it. Whilst their solution didn't work for me, I've added something that does. In case anyone else is interested in removing that CR at the end of an excel CSV import. Here's what I did.

Assuming Field1 is always going to have something in it (in my case, it will) and assuming your table is called "DataTable"

Code: Select all
list($Field1, $Field2, $Field3) = explode(',', $row);
if ($Field1 != "") {
$record = new Dataface_Record('DataTable', array());
// We insert the default values for the record.
$record->setValues($defaultValues);
// Now we add the values from the CSV file.
$record->setValues(
array(
'Field1'=>$Field1,
'Field2'=>$Field2,
'Field3'=>$Field3
)
);
       // Since there is value, now add the record to the output array.
        $records[] = $record;
        unset ($record);
}
}

// Now we return the array of records to be imported.
        return $records;
}
}


Cheers

Paul

Re: Field size and import stuff

PostPosted: Thu Mar 11, 2010 6:04 pm
by cantlep
hmmm, now another CSV import issue.

For some reason, when I import (again, excel converted to csv) there's a slight issue as far as the DB is concerned. In this instance the CompanyName column in the table called SiteData is what's having the issue.

Here's what a normal
Code: Select all
select CompanyName from SiteData;
looks like
Code: Select all
+-----------------------+
| CompanyName           |
+-----------------------+
| Newsquest Media Group |
| Newsquest Media Group |
| Newsquest Media Group |
| Rok Group                     |
| Newsquest Media Group |
| Newsquest Media Group |
| Newsquest Media Group |
| Newsquest Media Group |
+-----------------------+


Now, with the imported ones, (see the second_to_last entry), they look like this.

Code: Select all
+------------------------+
| CompanyName            |
+------------------------+
| Newsquest Media Group  |
| Newsquest Media Group  |
| Newsquest Media Group  |
| Rok Group                      |
|Newsquest Media Group
| Newsquest Media Group  |
+------------------------+


It hasn't shown up well in the code section but the second to last entry is indented (as is the | character before it and there is no | character at the end). Now, if I go into PHPMyAdmin and edit the field and literlly, select "go" without doing any editing, it corrects the indent. Also, in Datacentre, if I edit the endty and select the CompanyName from the list (which is already selected) and hit perform update, it also fixes the problem.

Anyone got any clues as to why this is happening?

Cheers

Paul

Re: Field size and import stuff

PostPosted: Fri Mar 12, 2010 9:39 am
by cantlep
All sorted on this now. I got rid of the carriage returns (\n) forgetting windows likes to use \r grrrr.

Just need to strip out the titles now... :-)