Editing SME Import Export Process

SME and SMP5 Import and Export Process

SMP5 provides a very open architecture for importing and exporting any data from the database tables using comma separated value files which are easily read and written using Excel.  This guide provides some general guidelines on the import and export process.  Please note that there are many tables and fields in SMP and many different purposes for importing and exporting information, so this is only a guide to adapt to your specific situation.

'''Exporting data: Utilities>Utilities>Export Data, Run It.'''

<br>1.     Exporting data is harmless and any fields in any table can be exported.

<br>2.     Select the table you want to export

<br>3.     Select the fields to export by selecting each field and selecting the > button or select >> to export all fields from the table.

<br>4.     Select the output location and the filename.  I like to give a file name with a .csv extension so that it automatically opens with Excel.

<br>5.     Note that you can save a template for if you will be doing the same export multiple times.  Then just use load template to recreate the export template.    

<br>6.     Select Start to create the export file.

<br>7.     Once you get the export file, notice the various UID field.  These are used to uniquely identify the records and MUST be used in the import process to avoid duplicating data.

<br>8.     Open the created file in Excel and do data manipulation as required.

'''Import Data: Utilities>Utilities>Import Data, Run it.'''

'''NOTE: IMPORTING DATA CAN CAUSE CORRUPTION OF YOUR DATABASE OR CREATE DUPLICATE RECORDS THAT WILL BE VERY HARD TO EXTRACT.  ALWAYS MAKE A BACKUP OF DATA BEFORE ANY IMPORT!!!!!!'''

There are two type of import, one for bringing in new records (data) and one for updating existing records.

'''Bringing in New Records:'''

<br>1.     Make a backup of your data.

<br>2.     I’m serious, backup your data if you haven’t already, do you realize the power that import provides!

<br>3.     File preparation:
UID

{|
| style="padding: 0in 5.4pt; width: 65pt; height: 15pt" nowrap="nowrap" width="87" valign="bottom" |
<font color="black">Name</font>
| style="padding: 0in 5.4pt; width: 71pt; height: 15pt" nowrap="nowrap" width="95" valign="bottom" |
<font color="black">Number</font>
| style="padding: 0in 5.4pt; width: 137pt; height: 15pt" nowrap="nowrap" width="183" valign="bottom" |
<font color="black">Description</font>
| style="padding: 0in 5.4pt; width: 48pt; height: 15pt" nowrap="nowrap" width="64" valign="bottom" |
<font color="black">Cost</font>
|- style="height: 15pt"
| style="padding: 0in 5.4pt; width: 123pt; height: 15pt" nowrap="nowrap" width="164" valign="bottom" |
<font color="black">IN01/11/070528298900</font>
| style="padding: 0in 5.4pt; width: 65pt; height: 15pt" nowrap="nowrap" width="87" valign="bottom" |
<font color="black">GSD item</font>
| style="padding: 0in 5.4pt; width: 71pt; height: 15pt" nowrap="nowrap" width="95" valign="bottom" |
<font color="black">GSD Item</font>
| style="padding: 0in 5.4pt; width: 137pt; height: 15pt" nowrap="nowrap" width="183" valign="bottom" |
<font color="black">Item for testing</font>
| style="padding: 0in 5.4pt; width: 48pt; height: 15pt" nowrap="nowrap" width="64" valign="bottom" |
<font color="black">100</font>
|- style="height: 15pt"
| style="padding: 0in 5.4pt; width: 123pt; height: 15pt" nowrap="nowrap" width="164" valign="bottom" |
<font color="black">IN01/17/071058388901</font>
| style="padding: 0in 5.4pt; width: 65pt; height: 15pt" nowrap="nowrap" width="87" valign="bottom" |
<font color="black">Lift Rental</font>
| style="padding: 0in 5.4pt; width: 71pt; height: 15pt" nowrap="nowrap" width="95" valign="bottom" |
<font color="black">Lift Rental</font>
| style="padding: 0in 5.4pt; width: 137pt; height: 15pt" nowrap="nowrap" width="183" valign="bottom" |
<font color="black">Lift Rental</font>
| style="padding: 0in 5.4pt; width: 48pt; height: 15pt" nowrap="nowrap" width="64" valign="bottom" |
<font color="black">0</font>
|- style="height: 15pt"
| style="padding: 0in 5.4pt; width: 123pt; height: 15pt" nowrap="nowrap" width="164" valign="bottom" |
<font color="black">IN01/18/071012043361</font>
| style="padding: 0in 5.4pt; width: 65pt; height: 15pt" nowrap="nowrap" width="87" valign="bottom" |
<font color="black">Discount</font>
| style="padding: 0in 5.4pt; width: 71pt; height: 15pt" nowrap="nowrap" width="95" valign="bottom" |
<font color="black">Discount</font>
| style="padding: 0in 5.4pt; width: 137pt; height: 15pt" nowrap="nowrap" width="183" valign="bottom" |
<font color="black">Discount</font>
| style="padding: 0in 5.4pt; width: 48pt; height: 15pt" nowrap="nowrap" width="64" valign="bottom" |
<font color="black">0</font>
|}

<br>a.     To prepare the file, make sure the first line contains the field labels

<br>b.    Use UID from previous exports for any relational data.  For example to import inventory and have it assigned to a specific vendor, you would use that vendor’s unique identifier “VendorUID”.  You can also use the Match check box to match by a field, however UID matching is more robust.

<br>c.     Make sure you don’t have any blank lines as this may create blank data.

<br>4.     Now that you have backed up your data, you can proceed.  Go to Utilities>Utilities>Import Data and select Run it

<br>5.     Select the file that you are using to import.  Note the first line of the file should include the field labels that will be matched to data fields going into SME.  This labels will show in the left side of the import.    

<br>6.     Match the fields from SMP on the right side window using Move Up, Move Down and remove.  You can select multiple items by selecting the first and hold down the shift and select the last in order to remove a bunch of fields.

<br>7.     Save the template at this time and give it a name.

<br>8.     This is your last chance, make sure you have backed-up you data before this step.  Select Start.

<br>9.  Check the SMP and see if your import was successful, if not get everyone out of SMP and restore your backup.

 

Updating Existing Data: This process is more advanced and involves more data manipulation in excel.

<br>1.     New in SME6.3:  Now you can match existing records with the match checkbox.  This makes it easier to match than using UID’s.  However, make sure that the field you are matching is unique and setup the same in SME.  For example, if you want to match on Inventory names, make sure the name is EXACTLY the same in the import file and SME.

<br>2.     First backup your data.

<br>3.     Export the table that you will later be updating with the import.  This will provide you with various unique ID’s (UID’s) or the matching names so that you don’t replicate data.  See the export section.

<br>4.     Open the exported file in excel.

<br>5.     Update the fields you want to change.  There are many methods for updating the data that are excel use issues and beyond the scope of this document.  For example you can copy a list of prices from a price table and paste into the appropriate inventory item.

<br>6.     The most important thing to note is that you should retain the UID or the matched field of the record that you want to update so the record is not duplicated. 

<br>7.     You do not need to update all the fields, just update the ones that are changing and include the UID.

<br>8.     Follow steps 3 through 9 above in the “Bringing in New Records” section.