Importing data

While data can be entered into the dPos system using the forms, it is often more convenient to prepare the data in a spreadsheet program, such as Excel. Excel spreadsheets or csv files may be imported into dPos using from the Setup & Maintenance screen by pushing the Import data button.

 

 

The Import data dialog allows you to select which of dPos’s three main tables (Items, Customers or Suppliers) is to be imported.  You can then Browse for the file you are importing. If you are importing from an Excel Workbook you will also need to specify the name of the work sheet (the name on the tab at the bottom of the worksheet – Note that Excel worksheets do not have a space between the word Sheet and the number when the default names are used).

As indicated in the screen shot above, it is essential to make a backup of your database before attempting to import a table. If an import fails and you have chosen to overwrite the existing table, then you have lost all the data in the table.

You can import CSV files, Excel worksheets or XML files. For CSV files or Excel worksheets the names of the columns of data MUST be provided in the first row of the data, AND the column names must be correct, otherwise the import will fail.

Not all columns (and column names) are required. The following table gives the column names that must be provided. Other column names are optional, and the complete list of column names may be obtained by exporting the columns names as described in the Exporting data section.

 

Required column names for importing the Item_table

iName	the item’s name
iCode	Your code for the item
iBarcode	the item’s barcode numbers
iSell_price	the item’s selling price
iID	row number or empty

 

The iCode and iOrderCode columns are limited to 10 characters each, iTax_code, iStock_items, iReorderMin, iReorderQty, iSupplier, and iID are integer numbers (although the last item should normally be empty), iSell_price, iBuy_price, iCase_buy, iCase_sell are money itesm and may be provided as numbers to two decimal places. The remaining columns are limited to 50 characters each. Attempting to import data exceeding these limits will cause the data to be truncated and may generate a database error.

 

Required column names for importing the Customer_table

cName	the Customer name
cID	row number or empty

 

The cAcc_no column is limited to 10 characters, the cAcc_date must be in the local date format, cCard must be 0 or 1 and cID is an integer number – although normally should be left blank even thought the column name must be present. All other columns in the customer table are limited to 50 characters each. Attempting to import data exceeding these limits will cause the data to be truncated and may generate a database error.

 

Required column names for importing the Supplier_table

sName	the Supplier name
sID	row number or empty

 

sCode is limited to 10 characters, sAcc_data must be in the local date format, and sID is an integer number – although normally should be left blank even thought the column name must be present. All other columns in the customer table are limited to 50 characters each. Attempting to import data exceeding these limits will cause the data to be truncated and may generate a database error.

 

Of course if the address rows and account numbers are not provided when importing customers or suppliers, the ordering and invoicing systems will not be able to generate compete documents.

Note that it is retail barcodes are normally 12 or 13 digit numbers, and Excel is tempted to convert such numbers into scientific notation (eg 1.23457E+12). This must be avoided – by formatting the cells containing these values to have 0 decimal places.