import materials excel materials csv file vendor list vendor materials faq can I import materials can I import plants how do I import materials How Can I Import Materials from a List or Spreadsheet

Step One: Create the import file

You can import materials into the LMN estimating catalog for two purposes:

Adding New Materials to your Catalog

Before you begin creating your own list of materials, ask your suppliers if they can provide one to you.  You need their material list in an Excel spreadsheet in order to easily import the item.  Most suppliers will be happy to provide one, as it will increase their chances of getting orders from your company.  If possible, ask them to include a SKU number or Part number in the import file.  A unique number to identify the material(s) will mean future price updates can be done in seconds, using the SKU/Part number to uniquely identify each material for update.

If your supplier cannot provide you a file, it can be faster to create your own file and import rather than entering numbers one at a time.  Use Microsoft Excel (or similar) to create a spreadsheet file. 

Updating Existing Costs and Other Information

You can also update your catalog information using an import file.  For instance, as your suppliers update their pricing, you can re-upload their updated pricing into your database.  Price + cost updates will be completed in mere seconds.

In order for updates to work, the catalog needs to use an ID number to uniquely identify the material to be updated.  A typical example of an ID number is a SKU number or Part number/ID.  Without an ID number, the import cannot guarantee an accurate match on materials and thus will treat every material without an ID number as a new material to be added to your catalog.

 

Step Two:  Format the import file

Everyone’s files look different, so we need to apply some standard rules to ensure the material catalog recognizes and imports everyone’s files accurately. 

Delete any titles, supplier names, etc. from the top of the import file.  The first row of the import file must be used for headings.  Your headings must exactly match the headings described in the list below.   Any columns with an unrecognized heading will be ignored during the import.  If your supplier has used their own heading names – you will have to change their headings to match the list below.  You do not need to have all the headings listed below – your items need only a Name to be imported successfully.  However, most users find it faster to enter information in Excel and thus, the more information you can add to your import file, the better.

Click here to download a Sample Import File

Your column headings can be in any order – you do not have to have your columns in the same order, just as long as your heading names match the list below.

Heading Names to be used in your Import file

 

Column Heading Name

Optional?

What Goes In the Column

Name

Required

The name of the material.  NOTE:  materials must have a name or the import will ignore the row when importing.

ID

Optional, but required for Update

A unique combination of letters and/or numbers that will identify the material, like a SKU number or part number.  The import can make use of the ID number later, using it to update information instead of adding new items (like updating a price list).  Assign all of your imported items an ID number to make future updates easy.  If your supplier does not provide ID numbers, you can make up your own.

Type

Optional – default value is Hardscape

You can choose the material to be either a Hardscape or a Softscape item.  The following values (use only one of the following) will set the material to be a Hardscape:  H, HS, Hardscape.  Any of the following values (use only one of the following) will set the material to be a Softscape:  S, SS, Softscape.

Size

Optional

The size of the material (e.g.: 1 GAL, 12”x4’)

Units

Optional

How the material is measured (e.g.: sq. ft, tons, yards, 20lb bags, etc.)

Round

Optional

(For use with assemblies)  Entering a number in the round column will help assembly calculations.  The estimating software will round the material up to the nearest rounding factor when calculating quantities in assemblies. Examples:

If you purchase the material in 10’ lengths, enter ‘10’ for the rounding factor.

If the material is purchased in skids of 75 sq. ft, enter ‘75’ for the rounding factor. 

Cost

Optional -recommended.  Default is 0

The cost-per-unit your company pays to purchase the material.

Price

Optional - default is blank

Enter a price in this column ONLY if you want to set a fixed price for an material.  It's not recommended as LMN is designed to calculate a price for you, but if you want to force a specific unit price, fill that unit price in this column.

FixedPrice

Optional - default is blank.

Enter a 'Y' or 'YES' in this field if you want to force LMN to use a specific unit price (entered in the PRICE column).  If you want LMN to calculate the price for you, just leave this column empty.

Profit

Optional, but required to set custom profit margin

If you wish your materials to be assigned a profit margin different than your budget default, enter the desired profit margin % here.  Your profit should be expressed as a percentage (e.g. 15%) or as a decimal (.15).  You can skip this column if you wish everything to be priced according to your budget default profit margin.

Ship

Optional – default is 0

Enter a cost percentage factor for shipping.  If shipping charges are typically 5% of the total value of the materials, enter ‘5%’.

Warranty

Optional – default is 0

Enter a warranty cost factor as a percentage.  If you expect to warranty 3% of your installations, enter ‘3%’.

Internal

Optional

Use this field to specify notes about the material only visible to estimator and/or crews (such as Vendor Name)

Category

Optional – default is Uncategorized

Enter the name of the category to which you want the imported material(s) to be assigned.  If the category does not already exist, the import tool will add it for you.  Remember to check your spelling – the import tool looks to find an exact match of existing categories.  If it does not match an category

Description

Optional

A description of the material.

 

Step Three:  Check Your Import File For Errors

Once again, your heading names must match the above list or the information contained under that heading will be ignored.

Now check your rows and look for the following:

  1. Delete any blank rows before importing. They will be ignored anyway.
  2. Delete rows that contain headings (other than the first row in the file). If your file has rows that divide your import file into sections, delete all of these extra heading rows.  The category column is used to assign categories to materials.  Only the first row can be used to setup headings. 
  3. Delete any extra (unnecessary) information from the spreadsheet. When ready to import, your spreadsheet should only contain:
    1. Row 1: Column Headings that match the list above
    2. All Other Rows: Material information to be imported
  4. Files must contain less than 1000 rows. Information past the 1000th row will be ignored.  In order to import more than 1000 items, you need to split your file into two (or more) files, each containing up to 1000 materials, but no more.
  5. Double-check your information. If you wish, add more information to the items by adding columns that might not exist in your supplier’s spreadsheet.  For instance, if your standard profit margin is 10%, but you make 30% on plant material, then make sure you add a Profit column and set the values to 30%.  You can make changes to a lot of items very quickly in Excel, saving you many hours of manually updating material information one material at a time.

When your import file is complete (the formatting stage), you need to save the file as a .csv file.

 

Step Four:  Save the import file as a .csv

  1. To save the import file as a .csv (or comma separated values file), click the File button and select “Save As”.  This will open the “Save As” dialog. Browse to the location where you wish to save the file.
  2. Enter a file name in the “File name” field.
  3. Click the “Save as type” drop-down list and select the “CSV (Comma delimited) option.

 

Step Five: Import the import file

  1. Click the Import button to re-open the import screen (if its not already open)
  2. If you're importing a vendor's file for the first time, disregard this step.  If you're re-importing a vendor file or re-importing a file you exported to update pricing, make sure you turn on the following options
    1. When a match is found, update the existing item
      1. Do not add new items, only update existing items
      2. Only update cost/price information (optional)
  3. Click the Choose File button and locate the .CSV file on your computer (note: the import file must be in .CSV format, .XLSX format will not work!)
  4. Click OK to import the file

When finished, LMN will display a count of the number of items added, updated, skipped, etc.

 

 

Whoops!  I made a mistake!

If you made a mistake in your file, and you’ve imported a bunch of materials incorrectly, fixing your mistake is easy.  The import routine comes with a Rollback feature.  You can always go back and undo any of your imports – the system will delete all materials added during the import.  You can fix your import file, then re-import the corrected version.

  1. Click the Import button to re-open the import screen (if its not already open)
  2. Search for the file you imported in the dropdown beside the Rollback button
  3. Select the file and click Rollback to un-import/delete any items imported in that file

 

Comments

0 comments
Please sign in to leave a comment.