Do you need to import contacts from a prior version or another source into Office Management? Here are some tips.
If upgrading from a prior version of 3D 11.4, one's prior database may simply be converted so you have all your prior contacts already handy. Usually that's a one-time process when you initially upgrade from a version prior to 12.
Also if you are importing new jobs using an automatic method via an online scheduler service, or adding new jobs directly into 3D, your contact collection in 3D will generally builds up automatically over time.
(Note: For information about exporting contacts out of 3D for use elsewhere, see separate thread on that subject).
But if you want to import contacts from another program into 3D, since the database is stored in SQL, Microsoft provides a "Import and Export Data" wizard that can import from common delimited or database or spreadsheet formats. Usually you'll find it under Start > All Programs > Microsoft SQL Server 2012 > Import and Export Data (32-bit). However in order for fields to be able to import into the structure 3D uses, you'll need to adjust or export your data into appropriate items or columns that can map properly.
Before importing information though, we recommend using the Backup All Files function in 3D to backup your current database, in case something does not get imported properly or as expected- that way you can always restore your last good backup if need be.
Here is an example set of steps, used to successfully import a few test contacts into 3D. There are also a few preparatory steps that definitely make a difference. Hopefully this will give you a good outline to start from.
HOW TO IMPORT CONTACTS FROM ANOTHER SOURCE INTO 3D
If you import contacts from another source, the SQL Import and Export wizard maps the fields in your import file to the existing fields in the InspectionData database stored in SQL, but you must fine-tune your Excel file for importing to work properly. This includes using an appropriate worksheet name, labeling your field columns properly for mapping to work correctly, including GUIDs for the ContactIDs, and using a TenantID that matches the one used by your 3D company database. Here is an example of how you may use Excel to label the columns and create appropriate GUIDs prior to importing.
PREPARING YOUR EXCEL FILE
Step A) First create your table to export from.
Name your worksheet "tblContact" (without the quotes) so it can be imported into the table of that name in your 3D database.
Use these column Header labels as applicable (TenantID, ContactID, and search Name are required to contain info for each record, while other fields may optionally contain data), so the field mapping will match:
TenantID
ContactID
Name
LastName
FirstName
Honorific
WorkExtension
HomePhone
HomeFax
Cellular
Pager
Salutation
Custom1
Custom2
Email
Address
City
State
PostalCode
Phone
Fax
ExternalLinkID
Custom3
Custom4
HERE is an example Excel table layout (right-click and Save As to your hard drive to view) that could be used as a base to start from. Or simply to see the general structure to use.
Populate or export your worksheet with the desired contacts information.
Step B) Add a Macro to Excel to generate GUIDs for the ContactID column.
(Note: If you are using the ContactsImportExample.xls as your base worksheet and putting data into it, it already contains the GetGUID macro, so you would not want to add it again as a duplicate module name will prevent it from working. Instead you would just enable Macros)
e.g. To add a Macro, in Excel, press Alt+F11 and Insert > Module. Add code to generate a GUID:
Close to save module.
Next in the tblContact worksheet, add this formula to the ContactID column to generate a GUID:
Copy the formula to all rows in the ContactID column. e.g. Click first cell with formula, double-click plus in lower right corner. See also
http://best-excel-tutorial.com/59-tips-and-tricks/189-apply-formula-entire-column
https://answers.yahoo.com/question/index?qid=20070511080900AA2Rwqv
Step C) ADD STATIC Tenant ID GUID VALUE TO ALL ROWS
First find your current TenantID for your 3D database this way:
Use Report Writer Preferences > Preferences, File locations to Open Folder for the Location of user and temporary files. Once the folder is open in File Explorer, you may cancel out of Preferences.
Open the config.ini file located in the user and temporary files folder in Notepad.
Find the <ConnectionSQLTenantID>xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx</ConnectionSQLTenantID> value.
Copy that value (represented by the xs above) that falls between the <ConnectionSQLTenantID> and </ConnectionSQLTenantID> tags (but not the tags themselves) and copy it and paste it into the TenantID column for the first record in your excel file (Note: This item is currently empty in the example excel file). Add braces characters { } to the beginning and end of the GUID, so it is in the format {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
The format should now look similar to one of the GUIDs for the ContactID items if you have previously run the macro to generate those.
Next copy your TenantID GUID to the remaining rows in TenantID Column so the same GUID is present for each contact record row (paste into the first one, add braces characters, and then copy to the remaining rows in that column.)
Basically the TenantID GUID for every contact should be the same and should match the one used by the 3D database, while the ContactID GUIDs should each be unique for each contact.
Save your prepared Excel file.
Step D) Cleaning up any empty rows.
You must also not have any empty rows in your spreadsheet, as that will prevent proper importing.
First of all, if there are any blank rows between items, you'll want to remove those. e.g. https://www.ablebits.com/office-addins-blog/2013/10/01/remove-blank-rows-in-excel/
OR http://helpdeskgeek.com/office-tips/delete-blank-lines-in-excel/
Note that simply removing empty rows between data apparently will not reduce the amount of available rows in your worksheets. If a row is removed, a new row is automatically added by Excel at the bottom of your worksheet.
So finally, one would want to make sure there are no "empty" rows remembered at the end of the worksheet. Use the steps here to get rid of any extra rows or columns past your data: https://computertrainer.wordpress.com/2009/07/28/permanently-remove-empty-rowscolumns-at-the-end-of-an-excel-spreadsheet/
Note: In older versions of Excel, you may have to Save, then close and re-open the file before the used range is reset.
IMPORTING THE PREPARED FILE
First, before importing anything into your 3D database, always make sure you have a current File > Backup All Data backup made from inside 3D, and that the program is closed.
When ready to import, use start in windows to locate and run the "Import and Export Data (32-bit)" wizard.
1. Choose a Data Source in the wizard and select source file or item.
Data source: Microsoft Excel
Excel file path: [Browse to select your xls file]
Next.
2. Choose a Destination-
Destination: "SQL Server Native Client 11.0"
Server Name: "[Computer]\THREED"
Database: "InspectionData"
Next.
3. Specify Table Copy or Query. Copy data from one or more tables or views. Next.
4. Select Source Tables and Views-
In the Source column select the worksheet containing your data
In the Destination column make sure it lists [dbo].[tblContact] as the table to import to. If the destination is ANY other name, then it will not add your contacts to the table 3D will "see". If needed, click into the table name and use the pull down menu that appears to select [dbo].[tblContact] from the list of existing tables.
(You may wish to "Edit Mappings" to see the field matching and verify that "Append rows to the destination table" is selected- if not available, then you may not have chosen the existing destination table correctly so it can append to that)
5. Review Data Type Mapping. Next.
6. Complete the wizard and import your data. (Note that any Validation "truncate" messages generally will not hurt anything as they are just notices that it can only fill the info into the field size available in 3D. Any other errors or notices may or may not need to be addressed.)
When finished open your 3D program and use the main screen Contacts view to view your contacts to verify they imported as desired. Or search for one of the contacts you imported to check for its presence.
If you need assistance with exporting content from your source in the format needed, a local IT or Excel expert familiar with SQL should be able to assist. Sometimes local college students are handy contacts for this sort of task or project.
Need Support