Importing from a Comma Separated Values Flat File

Comma Separated Values (CSV) files, also known as flat files are a common way to exchange simple data between databases and applications. A CSV File contains columns and rows like a spreadsheet, but the column boundaries are defined by commas between the field values. CSV Files can be imported and exported by any row-column type application including databases and spreadsheets (i.e., Microsoft Excel), and of course SixBit.

An alternative to commas used by some programs is the tab character. Tab-delimited files are just like comma separated files, however, they use tabs in place of commas. SixBit imports both types of files in the exact same way. For simplicity sake, we refer to all of these files as CSV's.

Preparing Your CSV File

In order to import a CSV file, it must follow certain rules and conform to a particular format.

CSV is a delimited data format that has fields/columns separated by the comma character and records/rows terminated by newlines. Fields that contain a special character (i.e comma or newline), must be enclosed in double quotes. When enclosing a field with double quotes, any embedded double quotes can be escaped by placing another double quote character next to it. If a line contains a single entry which is the empty string, it may be enclosed in double quotes.

Requirements

Although not a requirement for CSV files in general, an import into SixBit does require that the first line in the file contain the column names for each field. Outlined is an example of the first line of a valid CSV file.

ProductID,Title,Description,"Starting Price",MSRP

You will notice that any column names may or may not be surrounded by quotes.

Also, for a CSV file to import new items into SixBit, we require a Title field. The title can be provided by mapping an input field to the Title field, providing a constant text string, or by specifying a default item template.

Format

After the column name row, as many rows as needed may be included. Outlined is a sample of the header row and one data row.

ProductID,Title,Description,Starting Price,MSRP

1234,1964 Lincoln Cent,This is a rare penny.,2.00,0

Outlined are some additional rules with examples:

Fields with embedded commas must be enclosed within double-quote characters. When using Microsoft Excel to create CSV files, it will handle adding the double quotes. You will not have to add them yourself.

1234,1964 Lincoln Cent,"This is a rare, unique penny.",2.00,0

Fields with embedded line breaks must be enclosed within double-quote characters.

1234,1964 Lincoln Cent,"This is a rare penny.

It was minted in Seattle.",2.00,0

When a field must be enclosed within double quotes, any double quotes within the field must be represented by a pair of double quotes.

1234,1964 Lincoln Cent,"This is a rare, ""unique"" penny.",2.00,0

Double quotes do not need to be doubled up when the field is not enclosed in double quotes.

1234,1964 Lincoln Cent,This is a "unique" penny.,2.00,0

Or...

ProductID,Title,Description,Starting Price,Length,MSRP

1234,1964 Lincoln Cent,This is a rare penny.,8",2.00,0

Fields may always be enclosed within double-quote characters, whether necessary or not.

"1234","1964 Lincoln Cent","This is a rare, ""unique"" penny.","2.00","0"

Importing the CSV File

SixBit provides CSV import capabilities for Items, Inventory, Suppliers/Consignors, Compatibility Sets, Orders, Shipments, Sales, and Buyers. Each type of data must be provided in a separate file, but the basic process for importing from CSV is the same regardless of the data type being imported.

To import items from a CSV file:

From the ribbon bar in the Sell Items mode (for importing Items/Inventory/Suppliers/Consignors/Compatibility Sets) , or the Ship Orders mode (for importing Buyers, Orders, Shipments/Sales) click the arrow under the Import button, then select Import from File followed by the type of data to be imported.
The Open dialog will appear. Navigate to your CSV file and click OK. The file will load into the Import from CSV window.

Any fields in your CSV that have the same name as a field in the SixBit database, have already been pre-mapped for you. In the image above, Title, Weight Major, etc. have already been mapped.
When importing Items, an Item Template can be specified to provide values for any fields that are missing a mapping. Before you start to map Item files yourself, you should Choose an Item Template to provide values for any unspecified fields:. By doing this, any fields in the SixBit database that aren't specifically mapped from your CSV file will get values from the item template. This can save you a lot of unneeded typing cleaning up of imported records. This field will not appear at the top when importing any other type of data.
The fields from the input file must be matched to the fields in the database. This is performed by choosing CSV fields from the grid on the left and then dragging and dropping each CSV Field that will be imported onto the appropriate Mapping Source field on the table on the right. If you have already performed a mapping in the past, you had the option to Save the mapping. If you have a saved mapping, use the Load button to load it now. Item Templates are saved with Map files, so when loading a Map file, the Item Template will be populated, but only if it hasn't already been set to an item template.

The table on the right contains multiple tabs for subtypes of data. For example, Item fields specific to eBay will be on the eBay tab, or Order fields that identify the buyer will be on the Buyer tab, etc. In the example above, the eBay Title field will be mapped to the Title field on the eBay tab.. In the image below, you will see that we have matched up all the remaining CSV fields with their appropriate SixBit fields.

The Mapping Source column shows [[ ]]'s around any value that will be replaced by the value of a mapped field during the import.

There is one final way that a database field can be mapped; this can be done by hard coding the value in the Mapping Source column. In the example below, we added "Imported from CSV" to the Notes field. During the import every imported record will have the hard coded text set in the Notes field. This is a good way to enter data that will be common for all records that are imported.
In the lower left corner, choose the type of import to be performed. See Importing_vs._Updating for more information.

Import Type	Description
Add Records	This option will only add new records when an existing record does not exist. No updating of existing records will be performed.
Update Records	This option will only update existing records. If a record from the input is not found, it will not be added.
Add and Update Records	This option will add any missing records and update any existing records in the database.

The next several steps only apply when importing Item information. When importing any other type of data, skip to step 12.

The database does not have separate fields for each item specific on an item, so when importing items, item specifics must be handled slightly differently. If any of the incoming fields are item specific fields, drag them into the Item Specifics drop box. During the import, SixBit will look for any item specifics in the item with the same name and import the values. In the example below, the input fields Brand, Country of Manufacture and Style are item specifics and are mapped accordingly.

If listings are being run for any items that will be updated, SixBit can update the listings as well during the import by checking Update Running Listings; this will require SixBit to make a call for each running listing updated and will make the import perform a little more slowly.
If you specified pictures in your import, you can Replace Existing Pictures by checking the box. By leaving this option unchecked, the pictures will be added to any existing items.
Pictures can be imported as images that will be hosted on your own or merely URL's that your listings will point to. Click Import URL's as Links if you just want to store the URL for the listing to point to.
Now would be a good time to save your mapped information into a map file. By saving a map file, similarly formatted CSV files can be imported in the future without having to define the map each time. To save the map, click the Save button and provide a name for your map file.
Optionally, you can click the Preview button to display the data in an editable grid on the Batch Edit window. This allows for reviewing the data or making bulk changes before importing.
When finished, click the OK to begin the import.

Importing vs. Updating

The import of a CSV file can actually be handled as either a straight import or as an update to existing data or both at the same time. Importing is useful to populate your database with new data, but it can also be useful to update existing data. For example, if you have an empty database and your supplier sends you a flat file of 20,000 items that you want input into SixBit. You can perform an import and SixBit will add the 20,000 records. Now, suppose that each month, your supplier sends you an updated list; it may have a few extra items added or it may have fixed some spelling errors in some titles or even updated the description or MSRP. In this case, you have several choices as to how the import will behave. The Add and Update Records option would update any existing records found and add any new ones that were not already in the database. If no more new records were desired, the Update Records option would perform an update on any records found, but will not add any new records. Finally, Add Records will only add any new records that are found and will not update any existing records.

Whenever you import from a CSV file, SixBit will first look to see if the record you are importing has the same unique identifier as one that is already in the database. Unique identifiers are fields like ItemID, SKU, SupplierID, OrderID, ExternalOrderID, BuyerID, BuyerEmail, etc. Basically, any field that can uniquely identify a record as being different than all other records of the same type. If SixBit finds a unique identifier match, it just updates the other fields on the existing record. When a data type has more than one unique identifiers, it will look for other identifiers in succession. For example, on an Items import, if an ItemID is not provided, then it looks for an existing SKU, and if a SKU is not provided, it looks for an existing ProductID. If none of those fields are provided or found in the database, then the imported record will be added as a new record.

Importing Custom Fields

Items, Orders, Shipments, Sales, Buyers and Suppliers can have custom fields. When custom fields are defined, they will appear in the SixBit Destination Field with a prefix of CF_. The prefix is used to uniquely identify custom fields and to ensure that any custom fields created by the user will not have the same name as a future field created by SixBit.

ID's vs. Names

SixBit keeps track of some fields by use of an internal numeric identifier in addition to a human readable name. Many fields can be entered through either an ID or a Name. For example, each Origin Template has a name, but internally SixBit also keeps track of it by a number. When importing from a flat file, the Origin Template can be specified by mapping the internal numeric identifier to the Origin Template ID field, or by mapping the name to the Origin Template Name field. Any destination field that has an ID/Name pair of input fields works the same way.

The internal number is the more reliable identifier since it will never change, however, the name is more common and easier to find. Users are not expected to know the internal identifiers of a field, but they are exportable. In scenarios where data is exported, changed, and then reimported, the unique identifiers will be more reliable.

Boolean Values

When importing boolean values (True/False), the text "True" or "False" can be used as well as 1 for True or 0 for False.

Ordinal IDs

Whenever present, the OrdinalID field is used to specify the order of display in the SixBit interface. If not provided, the default is the next available number. If two records have the same ordinalid, the display order cannot be guaranteed.

Math Calculations

When importing, it can often be useful to have access to math functions. For example, you may want to import the sum of two different fields into a single field. A MATH() function has been provided to allow for this. Simply place the equation inside the parenthesis and the resolved value will be imported..

Examples:

MATH(1+2) would resolve to "3"

MATH([[SalePrice]]+3) would resolve to the sale price plus 3 dollars

MATH([[SalePrice]]*[[QuantitySold]]) would resolve to the extended price

MATH(([[SalePrice]]*[[QuantitySold]])+[[SalesTax]]) would resolve to the Total Amount Due

The order of precedence of the operators is power(^), Multiply(*), Divide(/), Add(+), and Subtract(-).

Special Handling Rules

Although the import process is the same for all types of data, the fields are different. Sometimes a field will have special rules related to the data import. The topics below discuss the special rules for each type of data import.

See Also Importing from Turbo ListerExchanging DataImporting from XMLImporting from eBayImporting_from_eBay_BlackthorneAdvanced Database Automation