The Order Import Tool allows you to upload a specially formatted spreadsheet with a list of orders in order to fulfill orders on a large scale all at once. If the information in the spreadsheet is invalid, incomplete, or not able to be validated by our system, it will result in an error. 


Note: In the event that an error occurs with an upload, our system will allow you to re-download the spreadsheet with the error explanations marked in a column on the right side. For the fastest way to identify errors, please download the spreadsheet upon receiving an error.



If the error cannot be seen on the spreadsheet, this article will discuss the most common errors, and how to resolve them.


TABLE OF CONTENTS


Improperly Formatted Headers

Correct Order Import Tool spreadsheets should have specially formatted headers at the top that operate as identifier tags for our system to look for while processing a spreadsheet. Each column header helps the system identify the information in that column so it can properly map the information in the order when creating it in our system.



The headers are as follows:


  • Channel_Order_ID

  • Channel_Item_ID

  • Quantity

  • Shipping_First_Name

  • Shipping_Last_Name

  • Shipping_Organization

  • Shipping_Street_Line_1

  • Shipping_Street_Line_2

  • Shipping_City

  • Shipping_State

  • Shipping_Postal_Code

  • Shipping_Country

  • Contact_Email_Address

  • Contact_Phone_Number


For more information on what these headers mean, please see the article here.


Note: It is important not to edit or remove the underscores from these headers or it will result in an upload error. A correctly formatted Lulu Order Import Tool Template can be downloaded at the end of this article.


Improperly Formatted Text

Within Excel, Pages, Google Sheets and other popular spreadsheet programs, text can be formatted in special ways, such as scientific text, accounting text, financial, etc. 


Special formatting can affect the information such as converting strings of numbers into equations, adding special characters, or removing certain digits from strings entirely. This will cause an upload error.


Example of text set to Scientific Formatting:



Note: All text information in an Order Import Tool spreadsheet must be formatted to plain text. Be sure to highlight all text in every field in the spreadsheet, then change the formatting in the text settings to plain text.



Example Formatting for Google Sheets:



For more information on formatting text, please refer to the support site for the program you are using.


Correctly Formatting Addresses

When a file is uploaded, the system attempts to validate the addresses in each line. If information is not entered correctly, the system will not be able to validate the address, resulting in an upload error. 


Here is a list of common address mistakes to double-check:


Information in the Wrong Column

For example, entering the Last Name in the Organization column, or mistakenly entering the City in the State column, etc. Be sure to check that all information is in the correct column.


Improper State and Country Entry

Lulu’s validation system for cities, states/provinces, and countries follows the ISO 3166-1 alpha-2 standard. All States and Countries should not be fully spelled out, and should be entered as 2-characters.

Example:

California = CA
Texas = TX

New York = NY

United States = US

Germany = DE


If you are unsure, be sure to verify official state/province and country codes in accordance with the ISO 3166-1 alpha-2 standard.



Note: Addresses in the United Kingdom should not be entered as 'UK', as the UK refers to a group of countries (England, Scotland, Wales, and Northern Ireland). Instead, it is important to enter the 2-character country code for Great Britain, as 'GB'.


Address Length Error

Every field in the address including the First & Last Name fields, Organization, Street Address 1 and Street Address 2 must be less than 30 characters, with spaces included. 


In the event that one of the lines is over 30 characters, please shorten the address, if possible, or split the address between the Address Lines 1 & 2. If an address is so long it cannot be abbreviated under 30 characters, a different address will need to be used.


Example Abbreviations to Shorten Addresses:


Building - Bldg.

Drive - Dr.

Street - St. 

Suite - Ste. 

Room - Rm. 

Floor - Fl.

Apartment - Apt. 

Northeast - NE

Northwest - NW

Southeast - SE

Southwest - SW


For a full list of official abbreviations recognized by the US Postal Service please see here. 


Note: If the organization name is too long, you may be able to abbreviate it with an official acronym for that organization, i.e., the Young Men's Christian Association can be abbreviated as YMCA.


Missing Information

All addresses in an Order Import Tool Spreadsheet are required to have the following information:


  • Channel_Order_ID: A string of numbers that will become the order number.

  • Channel_Item_ID: A string of numbers that act as a code to help the system identify which product a customer purchased.

  • Quantity: How many of each book were purchased

  • First & Last Name

  • Street Address

  • City

  • State/Province

  • Postal Code

  • Country

  • Phone Number


If any of the addresses are missing the information above, it will result in an upload error. 


Phone numbers should be entered without spaces or special characters, and should include the area code.


Note: Lulu uses Google’s Address Validation process. If an address is unable to be validated on Google, it will not be able to be validated with us, and will either require an address correction, or a different address.