Hi Linda, On recent borrower import using the staff-client interface I found that the following formatting worked for patron-attributes: ,,,"SID:EVE99001234","COURSE:M7","DEPARTMENT:LIBRARY" Although the staff interface loader usually gives good diagnostics I found there were some problems with data that failed silently (wish I could remember which) so I now often prepare borrower data as sql for loading mimicking the format of data from mysqldump. Ian On 08/11/2010 14:28, Linda Culberson wrote:

Sorry, I just *thought* it was solved. The patrons loaded but the patron attributes did not. So there is still a problem.

On 11/5/2010 11:25 AM, Linda Culberson wrote:

...

I think I've got it now. It seems that it didn't like the single quotes in the header nor the double quotes in the patron-attributes field

On 1:59 PM, Linda Culberson wrote:

...

I'm having trouble bulk loading patrons files into Koha 3.2. I used the same file I used in 3.1 with no success, so I did one manually, and then set everything to match that. I got the same errors in 3.2. both times: In the staff client, I get that the record was " not imported because they are not in the expected format" and then Critical field "surname" missing (borrowernumber: UNDEF; surname: UNDEF). I have headerrow copied from the "Import Patrons" page: 'surname', 'firstname', 'title', 'othernames', 'initials', 'streetnumber', 'streettype', 'address', 'address2', 'city', 'zipcode', 'country', 'email', 'phone', 'mobile', 'fax', 'emailpro', 'phonepro', 'B_streetnumber', 'B_streettype', 'B_address', 'B_address2', 'B_city', 'B_zipcode', 'B_country', 'B_email', 'B_phone', 'dateofbirth', 'branchcode', 'categorycode', 'dateenrolled', 'dateexpiry', 'gonenoaddress', 'lost', 'debarred', 'contactname', 'contactfirstname', 'contacttitle', 'guarantorid', 'borrowernotes', 'relationship', 'ethnicity', 'ethnotes', 'sex', 'password', 'flags', 'userid', 'opacnote', 'contactnote', 'sort1', 'sort2', 'altcontactfirstname', 'altcontactsurname', 'altcontactaddress1', 'altcontactaddress2', 'altcontactaddress3', 'altcontactzipcode', 'altcontactcountry', 'altcontactphone', 'smsalertnumber', 'privacy', 'patron_attributes' My record is: 'Crawford', 'Taz', '', 'Tazmo', '', '', '', '150 Our Street', '', 'Yazoo City, MS', '39194', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'DAH', '01', '11/05/2010', '11/05/2011', '', '', '', '', '', '', '', '', '', '', '', '', 'Tazmo', '', '11111', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'CountyCode:yz,PhotoID:Tx VetId 0042201,StateCode:01'

In the /var/log/koha/koha-error_log [Fri Nov 05 09:56:43 2010] [error] [client 10.8.5.33] [Fri Nov 5 09:56:43 2010] import_borrowers.pl: Use of uninitialized value $keycol in substitution (s///) at /usr/share/koha/intranet/cgi-bin/tools/import_borrowers.pl line 124, <fh00001mytest.csv> line 1., referer: http://koha.mdah.state.ms.us:8080/cgi-bin/koha/tools/import_borrowers.pl [Fri Nov 05 09:56:43 2010] [error] [client 10.8.5.33] [Fri Nov 5 09:56:43 2010] import_borrowers.pl: Use of uninitialized value $keycol in hash element at /usr/share/koha/intranet/cgi-bin/tools/import_borrowers.pl line 125, <fh00001mytest.csv> line 1., referer: http://koha.mdah.state.ms.us:8080/cgi-bin/koha/tools/import_borrowers.pl [Fri Nov 05 09:56:43 2010] [error] [client 10.8.5.33] [Fri Nov 5 09:56:43 2010] import_borrowers.pl: Use of uninitialized value $csvcolumns[0] in join or string at /usr/share/koha/intranet/cgi-bin/tools/import_borrowers.pl line 133, <fh00001mytest.csv> line 1., referer: http://koha.mdah.state.ms.us:8080/cgi-bin/koha/tools/import_borrowers.pl

So I looked at import_borrowers.pl and those lines are 122 foreach my $keycol (@csvcolumns) { 123 # columnkeys don't contain whitespace, but some stupid tools add it 124 $keycol =~ s/ +//g; 125 $csvkeycol{$keycol} = $col++; 126 } 127 #warn($borrowerline); 128 my $ext_preserve = $input->param('ext_preserve') || 0; 129 if ($extended) { 130 $matchpoint_attr_type = C4::Members::AttributeTypes->fetch($matchpoint); 131 } 132 133 push @feedback, {feedback=>1, name=>'headerrow', value=>join(', ', @csvcolumns)};

Any help would be appreciated.

-- Ian Bays Director of Projects PTFS Europe.com mobile: +44 (0) 7774995297 phone: +44 (0) 800 756 6803 skype: ian.bays email: ian.bays@ptfs-europe.com

Hi, Just to add a little detail: The header row I used does not have any quotes (single or double) and ends like this: ,altcontactphone,smsalertnumber,patron_attributes In the actual borrower rows apart from the patron_attributes the only fields I enclosed in quotes were those that had a comma or an apostrophe. The rest have no quotes. The quotes I used were double quotes. Some examples snipped that do not invade privacy: ...,"O'Connor",Anna,,,,,,... ...,Cosham,Portsmouth,"Hants, PO6 2PZ",,... ...,Sevenoaks,TN14 7AG,... ...,EXP,23/11/1999,,,,0,,,,,NOTE: LEFT 2000,,,... ...,,"SID:O'S98001234","DEPARTMENT:C&F","COURSE:M80" So the patron_attributes appear as separate "fields" on the end and are not grouped in quotes as a single item. I hope that helps... Ian On 08/11/2010 14:52, Ian Bays wrote:

Hi Linda, On recent borrower import using the staff-client interface I found that the following formatting worked for patron-attributes:

,,,"SID:EVE99001234","COURSE:M7","DEPARTMENT:LIBRARY"

Although the staff interface loader usually gives good diagnostics I found there were some problems with data that failed silently (wish I could remember which) so I now often prepare borrower data as sql for loading mimicking the format of data from mysqldump.

Ian

On 08/11/2010 14:28, Linda Culberson wrote:

...

Sorry, I just *thought* it was solved. The patrons loaded but the patron attributes did not. So there is still a problem.

On 11/5/2010 11:25 AM, Linda Culberson wrote:

...

I think I've got it now. It seems that it didn't like the single quotes in the header nor the double quotes in the patron-attributes field

On 1:59 PM, Linda Culberson wrote:

...

I'm having trouble bulk loading patrons files into Koha 3.2. I used the same file I used in 3.1 with no success, so I did one manually, and then set everything to match that. I got the same errors in 3.2. both times: In the staff client, I get that the record was " not imported because they are not in the expected format" and then Critical field "surname" missing (borrowernumber: UNDEF; surname: UNDEF). I have headerrow copied from the "Import Patrons" page: 'surname', 'firstname', 'title', 'othernames', 'initials', 'streetnumber', 'streettype', 'address', 'address2', 'city', 'zipcode', 'country', 'email', 'phone', 'mobile', 'fax', 'emailpro', 'phonepro', 'B_streetnumber', 'B_streettype', 'B_address', 'B_address2', 'B_city', 'B_zipcode', 'B_country', 'B_email', 'B_phone', 'dateofbirth', 'branchcode', 'categorycode', 'dateenrolled', 'dateexpiry', 'gonenoaddress', 'lost', 'debarred', 'contactname', 'contactfirstname', 'contacttitle', 'guarantorid', 'borrowernotes', 'relationship', 'ethnicity', 'ethnotes', 'sex', 'password', 'flags', 'userid', 'opacnote', 'contactnote', 'sort1', 'sort2', 'altcontactfirstname', 'altcontactsurname', 'altcontactaddress1', 'altcontactaddress2', 'altcontactaddress3', 'altcontactzipcode', 'altcontactcountry', 'altcontactphone', 'smsalertnumber', 'privacy', 'patron_attributes' My record is: 'Crawford', 'Taz', '', 'Tazmo', '', '', '', '150 Our Street', '', 'Yazoo City, MS', '39194', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'DAH', '01', '11/05/2010', '11/05/2011', '', '', '', '', '', '', '', '', '', '', '', '', 'Tazmo', '', '11111', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', 'CountyCode:yz,PhotoID:Tx VetId 0042201,StateCode:01'

In the /var/log/koha/koha-error_log [Fri Nov 05 09:56:43 2010] [error] [client 10.8.5.33] [Fri Nov 5 09:56:43 2010] import_borrowers.pl: Use of uninitialized value $keycol in substitution (s///) at /usr/share/koha/intranet/cgi-bin/tools/import_borrowers.pl line 124, <fh00001mytest.csv> line 1., referer: http://koha.mdah.state.ms.us:8080/cgi-bin/koha/tools/import_borrowers.pl [Fri Nov 05 09:56:43 2010] [error] [client 10.8.5.33] [Fri Nov 5 09:56:43 2010] import_borrowers.pl: Use of uninitialized value $keycol in hash element at /usr/share/koha/intranet/cgi-bin/tools/import_borrowers.pl line 125, <fh00001mytest.csv> line 1., referer: http://koha.mdah.state.ms.us:8080/cgi-bin/koha/tools/import_borrowers.pl [Fri Nov 05 09:56:43 2010] [error] [client 10.8.5.33] [Fri Nov 5 09:56:43 2010] import_borrowers.pl: Use of uninitialized value $csvcolumns[0] in join or string at /usr/share/koha/intranet/cgi-bin/tools/import_borrowers.pl line 133, <fh00001mytest.csv> line 1., referer: http://koha.mdah.state.ms.us:8080/cgi-bin/koha/tools/import_borrowers.pl

So I looked at import_borrowers.pl and those lines are 122 foreach my $keycol (@csvcolumns) { 123 # columnkeys don't contain whitespace, but some stupid tools add it 124 $keycol =~ s/ +//g; 125 $csvkeycol{$keycol} = $col++; 126 } 127 #warn($borrowerline); 128 my $ext_preserve = $input->param('ext_preserve') || 0; 129 if ($extended) { 130 $matchpoint_attr_type = C4::Members::AttributeTypes->fetch($matchpoint); 131 } 132 133 push @feedback, {feedback=>1, name=>'headerrow', value=>join(', ', @csvcolumns)};

Any help would be appreciated.

-- Ian Bays Director of Projects PTFS Europe.com mobile: +44 (0) 7774995297 phone: +44 (0) 800 756 6803 skype: ian.bays email: ian.bays@ptfs-europe.com

The error in the /var/log/koha/koha-error_log is import_borrowers.pl: Use of uninitialized value within @columns in pattern match (m//) at /usr/share/koha/intranet/cgi-bin/tools/import_borrowers.pl line 158

Here are those lines from import_borrowers.pl (hopefully with some context): 144 my @columns = $csv->fields(); 145 if (! $status) { 146 push @missing_criticals, {badparse=>1, line=>$., lineraw=>$borrower line}; 147 } elsif (@columns == @columnkeys) { 148 @borrower{@columnkeys} = @columns; 149 # MJR: try to fill blanks gracefully by using default values 150 foreach my $key (@criticals) { 151 if ($borrower{$key} !~ /\S/) { 152 $borrower{$key} = $defaults{$key}; 153 } 154 } 155 } else { 156 # MJR: try to recover gracefully by using default values 157 foreach my $key (@columnkeys) { 158 if (defined($csvkeycol{$key}) and $columns[$csvkeycol{$key}] =~ /\S/) { 159 $borrower{$key} = $columns[$csvkeycol{$key}];

So I think that error means that either @columns contains an undefined value, or that $csvkeycol{$key} isn't defined but we test that it is so that shouldn't be the problem. How can @columns contain an undefined value? Maybe if a line doesn't have enough fields? Right now I'd be checking that CSV file very very closely. Sorry I can't offer more help yet, -- MJ Ray (slef), member of www.software.coop, a for-more-than-profit co-op. Past Koha Release Manager (2.0), LMS programmer, statistician, webmaster. In My Opinion Only: see http://mjr.towers.org.uk/email.html Available for hire for Koha work http://www.software.coop/products/koha

On Mon, Nov 8, 2010 at 10:06 PM, Chris Nighswonger <cnighswonger@foundations.edu> wrote:

On Mon, Nov 8, 2010 at 9:54 PM, MJ Ray <mjr@phonecoop.coop> wrote:

...

So I think that error means that either @columns contains an undefined value, or that $csvkeycol{$key} isn't defined but we test that it is so that shouldn't be the problem.  How can @columns contain an undefined value?  Maybe if a line doesn't have enough fields?

Right now I'd be checking that CSV file very very closely.

One of the problems *is* the CSV, but that's because the instructions are not clear. Several fields in the borrowers table are NOT NULL which means that several columns in the CSV which advertise themselves as *not* mandatory are, indeed, mandatory. I'm in the process of correcting this along with making the patron import tool more informative as to errors which occur during attempted imports. I hope to have this work finished and submitted the first part of this week.

This patch should resolve these issues: http://git.koha-community.org/gitweb/?p=wip/koha-fbc.git;a=shortlog;h=refs/h... It applies against the current HEAD, but should apply against 3.2.x as well. Once it is pushed to HEAD, I'll back-port to 3.2.x. In the meantime, testing and sign-off would be appreciated. (CC' ing the devel list to get more coverage.) Kind Regards, Chris