Chapter 22 Importing
Importing lets you use spreadsheet data to create or update your Big Business entries.
Import a price list from a vendor to update your item costs. Then, export these items, calculate new prices in a spreadsheet, and import updated prices. The steps are easy.
Importing Information
To import data:
1. Click the Import tool in the Maintenance toolbar.
2. Select the file you want to import, then click OK.
3. In the dialog box that appears, select the kind of data you’re importing, then click the OK button.
4. In the Import window, match text fields in the Fields Available for Insertion list with Big Business fields, then click the OK button.
Importing Information into Big Business
To make it easier to transition to Big Business, you can import information from other applications. For example, you can import a list of contacts from a contact manager. You can also import financial records, such as lists of transactions, general journal postings, and budgets. (Importing from payroll software follows a different pattern. See “Importing Payroll Information,” later in this chapter, for instructions.)
The information you import into Big Business must be in tab-delimited, text-only files. These files can be exported by most spreadsheet, database, and information management programs. A tab-delimited file is one where tabs separate individual items of data. For example, suppose you have a spreadsheet with a row for each customer and columns for name, address, and so on. When you export the file from the spreadsheet, each row becomes a paragraph and the each column becomes a text field separated by tabs.
When you import data from a text file, you use an Import window to match text fields in the file to fields in Big Business. Import windows have a Fields Available for Insertion list, which displays the text fields from the first paragraph in the file. If the first paragraph corresponds to a heading row, the headings are displayed. If the first paragraph is a data row, its text fields appear. The Fields list on the other side of the window shows the Big Business fields that you can import into. It’s possible that there will be fields in the file that don’t correspond to a Big Business field; it’s likely that the
file won’t contain a matching field for every Big Business field.
To match a text field to a Big Business field, click a field in the Fields Available for Insertion list, then click the matching field in the Insertion Order list, then click the Insert button. (You can also drag a Fields Available for Insertion field to its corresponding field.) The inserted field appears next to its match on the right side of the window. Continue until all matches have been made.
When you import customer information, Big Business will not import Accounts
Receivable categories that don’t match the defaults. The defaults are used instead.
Caution: Due to memory constraints, there is an upper limit to the number of records that can be imported simultaneously. For safety, you should import no more than 300 records at a time. (3108)
Importing Single-Item
Importing a single item or journal row is now allowed to support testing of importing.
Importing Mapping
Import Mapping helps you import data and create Custom Imports, all on one screen.
Map Customers shows data on left being mapped to the Customer Fields on the right. Contact Code is highlighted and could be mapped to Customer Code by clicking Insert.
Once the fields you want are inserted, set any Tag you want assigned and click Import. This will create new Customers from this contacts data and add the Tag to help search.
Importing Mappings
Big Business makes it super easy to set up repeat Imports and options to automatically connect to eCommerce, EDI, POS, Sales Tax, etc. by using the same Mappings.
If you have imported inventory changes from a spreadsheet, you can easily setup updates to your web store because it uses the same mapping tool.
If you are changing settings to put more fields in a mapping, check your existing mappings to ensure they are up-to-date.
This Solution gives you a quick list of Mappings arranged alphabetically. (6847)
Authorize.net connections are automatic.
Auto-Import uses Mappings for Orders.
BigCommerce Connect uses Mappings for Customers, Items, Invoices, Orders. Its other connections are automatic.
Import uses Mappings for Alternate Item Codes, Bins, Bills, Budgets, Builds and Bundles, Contacts, Customer Payments, Customers, Exchange Rates, General Journal, Invoices, Item Adjustments, Items, Misc. Bills, Misc. Checks, Orders, Purchase Orders, Qty Pricing, Quotes, Vendors.
Shopify uses Mappings for Customers and Orders. Its other connections are automatic.
Square uses Mappings for Customers and Orders. Its other connections are automatic.
TaxJar uses Mappings for Quotes, Orders, Invoices. Its other connections are automatic.
Web API uses Mappings for Orders. Its other connections are automatic.
Woo uses Mappings for Customers and Orders. Its other connections are automatic.
Importing Saving Mappings
BigBusiness lets you save your imports for reuse, putting them in the list of imports.
Once you have created your Mapping, click the Save As button and give it a name. It will then appear in the list of Imports you can select from when importing next time.
Importing Header
Don't Import Header is pre-selected for imports to skip importing of column headings.
In a spreadsheet, you should have a Header row with column names like Contact Code.
This first row of the import file then displays in the Available Fields to help with mapping.
If Fields Available does not show names, but actual data, un-check Don't Import Header. Otherwise, leave Don't Import Header checked to skip import of the first row of the import file.
Importing Tag
Don't Import Header is pre-selected for imports to skip importing of column headings.
In a spreadsheet, you should have a Header row with column names like Contact Code.
This first row of the import file then displays in the Available Fields to help mapping.
If Fields Available does not show names, but real data, un-check Don't Import Header. Othewise, leave Don't Import Header to skip import of the first row of the import file.
Importing Line Items
BigBusiness includes Line Item imports for bring in transactions with line items, like:
• Sales Order Line Items
• Invoice Line Items
This Solution provides some of the rules these imports follow for:
• Combining multiple line items on one transaction
• Separating transactions for the same customer
• Rebuilding Bill To and Ship To addresses
(5813)
BigBusiness includes imports for Sales Order Line Items and Invoice Line Items. In addition to importing from other systems, this allows importing from another copy of Big Business. Use the Save To Disk from the Invoice or Sales Order QuickSearch and Include Line Item Detail to see a good example of the format expected.
Each row of data that is imported is a Line Item. Data pertaining to the Invoice (or SO) should be repeated in the import file for each Line Item. When importing Other Charges, for example, which applies once to the transaction, the entry imported with the last Line Item is used. The only exception is the Customer Name which if blank is assumed to be the same Customer from the previous Line Item.
Line Items listing the same Customer Name (or blank) are assumed to be part of the same transaction, unless:
• Invoice Number or Sales Order Number is imported and is different and not blank
• PO Number is imported and is different and not blank
In other words, adjacent transactions for the same Customer Name will be combined unless the transaction number or PO number are imported to distinguish them. If importing from another system which generates an unique code for each order, import this to the PO# for future reference and to separate repeat orders.
When Ship To and Bill To are imported they are reconstituted. When you Save To Disk from Big Business is replaces the Carriage Returns found in the Bill To and Ship To addresses with Two Spaces (e.g. " "). When Importing a Bill To or Ship To address, the first three instances of Two Spaces are replace with Carriage Returns. From another system, export a four line address (up to 32 characters per line) by adding Two Spaces after each of the first three lines.
Importing Narration
Importing Narration shows you import progress listing entries as they are created.
Importing Customers
This document describes the Customer data which can be imported into Big Business, along with a description of action taken if the data does not meet the required format.
Details:
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. Each field to be imported must be separated by a tab, and each record must be separated by a return. This is easily done using a spread sheet.
Import Fields: Customers (3484)
This document describes the Customer data which can be imported into Big Business, along with a description of action taken if the data does not meet the required format.
Details:
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. Each field to be imported must be separated by a tab, and each record must be separated by a return. This is easily done using a spread sheet.
Customer Import (during Setup)
Customer Listing Text up to 64 characters in length Remove characters beyond 64
Company Name Text up to 32 characters in length Remove characters beyond 32
Customer Code Text up to 16 characters in length Remove characters beyond 16
Customer Type Text up to 24 characters in length Remove characters beyond 24
Salutation Text up to 8 characters in length Remove characters beyond 8
First Name Text up to 16 characters in length Remove characters beyond 16
Last Name Text up to 16 characters in length Remove characters beyond 16
Title Text up to 24 characters in length Remove characters beyond 24
Phone Number 1 (Work) Phone number up to 27 characters in length Remove characters beyond 27
Phone Number 2 (Fax) Phone number up to 27 characters in length Remove characters beyond 27
Phone Number 3 (Home) Phone number up to 27 characters in length Remove characters beyond 27
Phone Number 4 (Mobile) Phone number up to 27 characters in length Remove characters beyond 27
Credit Limit Any number up to two decimal places Number rounded to two decimal places
Terms Text up to 24 characters in length Remove characters beyond 24
Price Level “1, 2, 3, 4, or 5” Replace with “1”
Tax Status Text up to 32 characters in length Remove characters beyond 32
Resale # Text up to 32 characters in length Remove characters beyond 32
Shipping Method Text up to 24 characters in length Remove characters beyond 24
Receivables Category Any Receivables Category in the Account List Replace with default Receivables Cat.
Income Category Any Category established in the Account List Replace with default Income Category
Balance Any number up to two decimal places Number rounded to two decimal places
Salesperson Text up to 32 characters in length Remove characters beyond 32
Billing Name Text up to 32 characters in length Remove characters beyond 32
Billing Address 1 Text up to 32 characters in length Remove characters beyond 32
Billing Address 2 Text up to 32 characters in length Remove characters beyond 32
Billing City Text up to 16 characters in length Remove characters beyond 16
Billing State Text up to 3 characters in length Remove characters beyond 3
Billing Zip Zip Codes up to nine characters in length Remove characters beyond 9
Billing Country Text up to 16 characters in length Remove characters beyond 16
Shipping Name Text up to 32 characters in length Remove characters beyond 32
Shipping Address 1 Text up to 32 characters in length Remove characters beyond 32
Shipping Address 2 Text up to 32 characters in length Remove characters beyond 32
Shipping City Text up to 16 characters in length Remove characters beyond 16
Shipping State Text up to 3 characters in length Remove characters beyond 3
Shipping Zip Zip Codes up to nine characters in length Remove characters beyond 9
Shipping Country Text up to 16 characters in length Remove characters beyond 16
Importing Vendors
This document describes the Customer data which can be imported into Big Business, along with a description of action taken if the data does not meet the required format.
Details:
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. Each field to be imported must be separated by a tab, and each record must be separated by a return. This is easily done using a spread sheet.
Import Fields: Vendors (5158)
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. Each field to be imported must be separated by a tab, and each record must be separated by a return. This is easily done using a spread sheet application.
Vendor Import (during Setup)
Info to Import Limitation on data Action if limitations not met
Vendor Listing Text up to 64 characters Remove characters beyond 64
Company Name Text up to 32 characters Remove characters beyond 32
Vendor Code Text up to 16 characters Remove characters beyond 16
Vendor Type Text up to 24 characters Remove characters beyond 24
Salutation Text up to 8 characters Remove characters beyond 8
First Name Text up to 16 characters Remove characters beyond 16
Last Name Text up to 16 characters Remove characters beyond 16
Title Text up to 24 characters Remove characters beyond 24
Phone # 1 (Work) Number up to 27 characters Remove characters beyond 27
Phone # 2 (Fax) Number up to 27 characters Remove characters beyond 27
Phone # 3 (Home) Number up to 27 characters Remove characters beyond 27
Phone # 4 (Mobile) Number up to 27 characters Remove characters beyond 27
Credit Limit Any number up to two Number rounded to two
decimal places decimal places
Terms Text up to 24 characters Remove characters beyond 24
ETA Days Any # between 1 and 32,767 Replace with zero
Payment Account Any Bank Account Remove entered
in the Account List Payment Account
Send 1099 “True, False, Yes, No, 1, 0” Replace with “No”
Tax ID# Text up to 16 characters Remove characters beyond 16
Payables Category Any Payables Category Replace with default
in the Account List Payables Category
Expense Category Any Category established Replace with default
in the Account List Expense Category
Balance Number up to two Number rounded to two
decimal places decimal places
Mailing Name Text up to 32 characters Remove characters beyond 32
Mailing Address 1 Text up to 32 characters Remove characters beyond 32
Mailing Address 2 Text up to 32 characters Remove characters beyond 32
Mailing City Text up to 16 characters Remove characters beyond 16
Mailing State Text up to 2 characters Remove characters beyond 2
Mailing Zip Number up to 9 characters Remove characters beyond 9
Mailing Country Text up to 16 characters Remove characters beyond 16
Payment Name Text up to 32 characters Remove characters beyond 32
Payment Address 1 Text up to 32 characters Remove characters beyond 32
Payment Address 2 Text up to 32 characters Remove characters beyond 32
Payment City Text up to 16 characters Remove characters beyond 16
Payment State Text up to 2 characters Remove characters beyond 2
Payment Zip Number up to 9 characters Remove characters beyond 9
Importing Items
This document describes the Item data which can be imported into Big Business, along with a description of action taken if the data does not meet the required format.
Details:
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. Each field to be imported must be separated by a tab, and each record must be separated by a return. This is easily done using a spread sheet.
Import Fields: Items (5161)
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. This is easily done using a spread sheet application with columns matching the fields below, and each Item on a separate row. Then save the data from the spreadsheet using Save As... and selecting Text (tab-delimited) as the type.
Item Import Fields:
Item Code (18), Description 1 (24), Description 2 (24), Level 1 Price, Level 2 Price, Level 3 Price, Level 4 Price, Level 5 Price, Buy Item (T/F), Inventory Item (T/F), Sell Item (T/F), Expense Category, Asset Category, Cost of Goods Category, Taxable (T/F), Item Type (24), Vendor Name (VL), Vendor Part # (16), Order at Least, When Below, Ship Weight, Cost, Quantity on Hand*, Last Bought* (Date), Last Sold* (Date), Average Cost*, Total Cost*, Detail Description (Text), Custom Field 1 (32),...Custom Field 12 (32).
Notes:
* indicates a field that in available when importing during the initial Setup of your data file, but not during normal operation. Use the Item Adjustment imports to update quantities.
Formats:
(18) accepts up to 18 characters; extra characters removed.
(T/F) accepts True, False, Yes, No, 1, or 0; defaults to True.
(CA) accepts any valid selection from the Chart of Accounts; or else default.
(VL) accepts any valid selection from the Vendor Listing.
(Date) accepts the format "mm/dd/yy" only; defaults to blank.
(Text) accepts up to 32,000 characters.
Importing Misc Sales
This Solution describes the fields that can be imported as Miscellaneous Sales.
(5764)
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. This is easily done using a spread sheet application with columns matching the fields below, and each Item on a separate row. Then save the data from the spreadsheet using Save As... and selecting Text (tab-delimited) as the type.
Miscellaneous Sales Import Fields (and Formats):
Date (Date), Invoice# (10), Customer (CL), Amount, PO#/Comments (16), Department (DL), Salesperson (EL)
Formats:
(Date) accepts the format "mm/dd/yy" only; defaults to blank.
(10) accepts up to 10 characters; extra characters removed.
(CL) accepts any valid selection from the Customer Listing.
(DL) accepts any valid selection from the Department List.
(EL) accepts any valid selection from the Employee Listing.
Importing Invoices
This Solution describes the fields that can be imported as Invoice Line Items.
In Big Business 4.0.9 and earlier, this feature does not update Item Cards or Customer Balances correctly. Contact Technical Support for information on correcting these entries. This problem is fixed in Big Business 4.2 and higher.
Big Business 4.2.8 and 5.0.2 add a Paid amount to the import options. (5765)
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. This is easily done using a spread sheet application with columns matching the fields below, and each Item on a separate row. Then save the data from the spreadsheet using Save As... and selecting Text (tab-delimited) as the type.
Invoice Line Item Import Fields (and Formats):
Customer Name (CL), -Bill To (Text), -Ship To (Text), -Invoice# (10), Customer PO# (16) -Date (Date), Department (DL), -Salesperson (EL), -Ship Method (SL), -Terms (TL), Other Charges ($), Printed Comments (Text), Private Comments (Text), Item Code, -Description 1, -Description 2, Quantity(#), Quantity BackOrdered (#), -Price ($), Line Item Comment (Text) , -Paid($)
Notes:
- indicates an Optional Field, which will filled automatically (such as an existing Customer's Bill To address). Importing to an Optional Field will overwrite the automatic contents, even if you are importing a blank (no data).
Formats:
(Date) accepts the format "mm/dd/yy" only; defaults to current date.
(10) accepts up to 10 characters; extra characters removed.
(CL) accepts any valid selection from the Customer Listing.
(DL) accepts any valid selection from the Department List.
(EL) accepts any valid selection from the Employee Listing.
(Text) accepts text up to 32,000 characters
Importing Misc Bills
This Solution describes the fields that can be imported as Miscellaneous Bills.
(5807)
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. This is easily done using a spread sheet application with columns matching the fields below, and each Item on a separate row. Then save the data from the spreadsheet using Save As... and selecting Text (tab-delimited) as the type.
Miscellaneous Bills Import Fields (and Formats):
Date (Date), Invoice# (32), Vendor (VL), Amount ($), PO#/Comments (32), Department (DL)
Formats:
(Date) accepts the format "mm/dd/yy" only; defaults to blank.
(32) accepts up to 32 characters; extra characters removed.
(VL) accepts any valid selection from the Vendor Listing.
(DL) accepts any valid selection from the Department List.
Importing Item Adjustments
This Solution describes the fields that can be imported as Item Adjustments.
(5806)
Prior to importing information into Big Business, you need to prepare a tab delimited text file containing the necessary information. This is easily done using a spread sheet application with columns matching the fields below, and each Item on a separate row. Then save the data from the spreadsheet using Save As... and selecting Text (tab-delimited) as the type.
Item Adjustment Import Fields (and Formats):
Item Code (I), Description1 (I), Description2 (I), Adjustment (#5), New Quantity (#5), Unit Cost (#3)
Notes:
Import an Adjustment quantity, or import a New Quantity to have the Adjustment calculated automatically, but not both.
Formats:
(I) accepts any valid selection from the Item List.
(#5) accepts any number upto 5 decimal places.
Importing Built Items
Big Business includes sales and manufacturing features for creating Builds and Bundles. Built Items are inventoried items assembled from inventoried items. Bundled Items are non-inventoried kits that exist only to provide the bundling or kitting function of selling a group of items at once. Both are defined using the Build/Bundle tool found on the Item Card, and can now be defined using the Build / Bundle Line Items Import.
Use the Build/Bundle Line Items Import to:
- create Builds or Bundles
- update Builds or Bundles
- delete Builds or Bundles
- specify Components and Quantities for Builds or Bundles
- import Bill of Materials data
(5861)
Big Business lets you can create Built or Bundled Items. Built Items are inventoried finished goods, such as bicycles, that are assembled from inventoried components, like frames and wheels--which themselves may be built items. Bundled Items are non-inventoried kits which automatically add a group of component items to a Quote, Sales Order, or Invoice, and may or may not have special pricing.
Builds and Bundles can be defined using the Build/Bundle tool, and the Inventoried and Sold attributes, found on the the Item Card. Builds and Bundles can also be defined using the Build/Bundle Line Items Import.
To import Build/Bundle Line Items:
1. On the Maintenance toolbar, select the Import tool
2. Select a Tab-Text file to import
3. Select Build/Bundle Line Items to Import Into..
4. Insert fields to import to the following target fields:
• Build/Bundle Item Code - the item being built or bundled, e.g. bicycle
• Component Item Code - one component, e.g. a wheel
• Quantity - the quantity of the component item, e.g. two
5. Click Do Not Import Header if your import file has column headers
Build/Bundle Code is an optional import field that defaults to "1" and can be:
1 - a Built Item
2 - a Bundle Item using the price of the Bundle
3 - a Bundle Item using the price of the Components
Importing a Build/Bundle Item Code without a Component will delete any existing Components for the Item.
Importing Bill of Materials
The Build/Bundle Line Item Report allows you to:
• Find Built or Bundled Items that meet the Search criteria entered
• Report the Components for the Items found
• Export this Bill of Material information for editing in a spreadsheet
• Create a file for use with the Build/Bundle Line Item Import
This Solution describes the new report. (5927)
The Build/Bundle Line Item Report allows you to:
• Find Built or Bundled Items that meet the Search criteria entered
• Report the Components for the Items found
• Export this Bill of Materials information for editing in a spreadsheet
• Create a file for use with the Build/Bundle Line Item Import
The Build/Bundle Line Item Report is found in the Item Reports. The Reports tool is found on the InfoCenter toolbar or in the File menu.
When the report runs, an Item Search dialog appears. Enter your Seach criteria (e.g. Component Item Is Equal To...) and click OK or click All. The reports finds all Built or Bundled Items that meet the Search criteria. Use the criterion, Build/Bundle Item Is... (Build or Bundle) to isolate one or the other.
To Print the report, double-click the report, or select the report (single-click) then click the Print button. To view the report on screen, OK the Print Setup and on the Print dialog choose Preview On Screen in the lower left corner of the Print Dialog.
To Export the report, single-click on the report to select it, then click the Save To Disk button. Name and Save the report in a convenient location.
To Open the export in a spreadsheet, launch the spreadsheet program, go to the File menu and choose Open. You may need to change the file-type selector on the Open dialog to see the exported report. If an import wizard appears, click Finish. If you edit the report and want to import the changes to Big Business, be sure to save the file again in Tab-Text format.
To Import the exported report, use the Build/Bundle Line Items Import.