Bulk Upload Issues

How are you preparing the CSV files? Excel is *notorious* for “misformatting” CSV files. Check the files with a *plain text editor* before uploading. Note for some fields (notably the barcode field) UTF-8 characters will be problematical.

I am currently working with the file in Google Refine to clean up the data. Do you still recommend checking it with a plain text editor? Thank you.

Yes, a final check with the text editor is always a good idea.

Hello,

I have went through and cleaned up all of the data in OpenRefine and double checked the format in Notepad++. Everything appears to be formatted correctly but when I try to bulk upload it says that it is adding them but nothing shows up in the collection. I changed all of the barcode numbers to 0 and had one entry populate in the collection. Then I changed all of the numbers to a series (1-7,890) and nothing is populating. Do you have any other recommendations on how to proceed?

Thank you!

If you are not actually going to use a bar code reader and put bar codes on your books, you probably should just not include the barcode column. The bulk upload with generate unique bar codes. Setting all of the bar codes to the same value will prevent the items from being uploaded.

Question: Does the CSV file include a header line? That is, does the first line itentify the columns and do you enable the header processing? Unless you have the columns in the *correct* order, things are going to work poorly, so it is probably best to use the header processing and include a header line.

I don’t know if Notepad++ has a strict 7-bit ASCII mode, but it is important that the separators (,) and quote marks are the *ASCII* comma and double quote and NOT the UTF-8 characters.

Otherwise, I don’t know. Do you have a short sample from your CSV file that I can look at and/or try to upload to my test site?

I deleted the barcode column as I don’t need it. I went and changed the order of the columns to reflect the order in the system. I do have the header line with the needed column headers. I converted the file to ASCII through a plug in converter on Notepad++.

I tried uploading 6 records and only one was successfully uploaded. After looking at the differences between the records I noticed there is a significant character difference in the title and author sections-the one that was uploaded had a shorter title and author section. Is there a title and author character limit? If so, would I be able to expand this, a lot of the collection has very long titles and authors listed.

Here is a sample from the csv file:
title author subject description publocation pubdate edition isbn type callnumber
Fishes of the Salish Sea : a compilation and distributional analysis,”Pietsch, Theodore W., Orr, James Wilder, United States and National Marine Fisheries Service.”,Auxiliary Sciences of History,”(iii, 106 pages): illustrations (some color).”,”Seattle, Washington : United States Department of Commerce, National Oceanic and Atmospheric Administration, National Marine Fisheries Service, Scientific Publications Office, 2015.”,Jan/2015,N/A,979930870,Book,C 55.25 NMFS 18
“An Assessment of the Bottom Fauna and Sediments of the Western Basin of Lake Erie, 1979″,Ontario Ministry of the Environment,Physical geography,N/A,Ontario Ministry of the Environment. Harry C. Parrott. Graham W.S. Scott,Jan/1979,N/A,977070946,Book,GB1627.G83 M5 1979
Developing Rare and Exotic Plant Geographic Information System Databases for Indiana Dunes and Sleeping Bear Dunes National Lakeshores,”Pavlovic, Noel B., Quinlan, Karen M. and Ford, Thomas”,Physical geography,N/A,”USGS, Biological Resources Discipline”,Jan/2005,N/A,,Book,GB634 .15 .P3 2005
The creative communicator : 399 tools to communicate commitment without boring people to death! / Barbara A. Glanz.,”Glanz, Barbara A.”,Industries. Land use. Labor,”xviii, 228 p. : ill. ; 24 cm.”,”Homewood, IL : Business One Irwin, c1993.”,Jan/1993,N/A,27382264,Book,HD30.3 .G56 1993
“When fish fly : lessons for creating a vital and energized workplace from the world famous Pike Place Fish Market / John Yokoyama, Joseph A. Michelli.”,”Yokoyama, John”,Commerce,”viii, 158 p. ; 22 cm.”,”New York : Hyperion, c2004.”,Jan/2004,N/A,52806137,Book,HF5549.5.M63 Y65 2004
Study and Interpretation of the Chemical Characteristics of Natural Water. (3rd Ed.),”Hem, J D.”,Government Publication,263 pages + 3 maps + 1 overhead transparency,Government Publication,Jan/1985,N/A,631706463,Book,I 19.13.2254
Office, 2015. Jan-15 N/A 979930870 Book C 55.25 NMFS 18

Thank you for your help!

The header line does not have any commas.

The field widths are:

barcode varchar(16)
title varchar(128)
author varchar(64)
subject varchar(128)
description text
category varchar(36)
media varchar(36)
publisher varchar(36)
publocation varchar(36)
pubdate date
edition varchar(36)
isbn varchar(20)
type varchar(16)
thumburl varchar(256)
callnumber varchar(36)

Is changing the field widths as simple as increasing the number in the ‘includes’>’database_code.php’ code section?

When I copy-pasted your sample CSV file, the quote marks were UTF-8, not ASCII. I don’t know if that was a side effect of copy and pasting from a webpage or not (I’m guessing yes).

I think we’re having the issue because of the character length. Would changing the character limit be as a simple as below? I would need to do it for all of them but would this work?
Example:

original-
title varchar(128) not null check (title<> ”)

edited-
title varchar(300) not null check (title<> ”)

OK, there was a bug. The WP DB code *quietly* fails to insert records when fields are over length. (No error is reported!). I updated my code to check and truncate over length fields. You should be able to update the plugin and things will work better.

Yes, but now you are going to have to go down into the upload_csv() function and change them there. Basically you will need to fork the code. You should probably have a good PHP programmer to help you with that and be available to back-port any other bug fixes that come down the line.

Oh, and you will have to fix things in the set_* functions as well, since they all perform a string truncate.

Thank for you getting that update so quickly! It seems to be working as intended, which is great.

The only problem is that for our catalog, we need the extra character space rather than having the author and title being cut off/truncated. The longest title that we have is 461 characters and the longest author that we have is 210. Am I able to adjust the code in the set_*functions, upload_csv()function, and the database code section to these numbers? I’m curious if the front end view will allow these longer characters or if it will mess everything up. Unfortunately, we don’t have an experienced PHP programmer and are trying to figure this out as we go.

Thank you for your quick responses and assistance!

The frontend will probably be fine, but the back-end edit screen *might* have issues, because the text fields might not be large enough. You might also need to hunt down any validation checks and make sure they are not imposing the wrong length checks. You might want to look into hiring a local PHP programmer to help you out, possibly on a part-time, as-needed basis. There are some on-line resources, like Guru.com, that you might want to look at.

I wonder if really makes sense to actually have the full long title and/or author list. I mean would anyone really put a 461 character title on the spine of a book? Or list all 42 authors there either? For example, the “fat” SciFi anthologies I have (which might have 20-30 stories by 20-30 different authors) only put the editor(s) on the spine and cover. The *description* supports an un-limited amount of text. Maybe a *brief* title and the 2-3 principle authors (or the editor?) can go into the title and author fields and the *description* field can go into greater detail, with the full, long title and a full author list. This might be a simplier solution, one that avoids a whole pile of problems.