Ready to get started?

Learn more about Sage US Connectivity Solutions

Learn More

Transfer Data from Excel to Sage US

This article explains how to transfer data from Excel to Sage US using the Excel Add-In for Sage US.

The CData Excel Add-In for Sage US enables you to edit and save Sage US data directly from Excel. This article explains how to transfer data from Excel to Sage US. This technique is useful if you want to work on Sage US data in Excel and update changes, or if you have a whole spreadsheet you want to import into Sage US. In this example, you will use the Customer table; however, the same process will work for any table that can be retrieved by the CData Excel Add-In.

Establish a Connection

If you have not already done so, create a new Sage US connection by clicking From Sage US on the ribbon.

The Application Id and Company Name connection string options are required to connect to Sage as a data source. You can obtain an Application Id by contacting Sage directly to request access to the Sage 50 SDK.

Sage must be installed on the machine. The Sage.Peachtree.API.dll and Sage.Peachtree.API.Resolver.dll assemblies are required. These assemblies are installed with Sage in C:\Program Files\Sage\Peachtree\API\. Additionally, the Sage SDK requires .NET Framework 4.0 and is only compatible with 32-bit applications. To use the Sage SDK in Visual Studio, set the Platform Target property to "x86" in Project -> Properties -> Build.

You must authorize the application to access company data: To authorize your application to access Sage, restart the Sage application, open the company you want to access, and connect with your application. You will then be prompted to set access permissions for the application in the resulting dialog.

While the compiled executable will require authorization only once, during development you may need to follow this process to reauthorize a new build. To avoid restarting the Sage application when developing with Visual Studio, click Build -> Configuration Manager and uncheck "Build" for your project.

Retrieve Data from Sage US

To insert data into Sage US, you will first need to retrieve data from the Sage US table you want to add to. This links the Excel spreadsheet to the Sage US table selected: After you retrieve data, any changes you make to the data are highlighted in red.

  1. Click the From Sage US button on the CData ribbon. The Data Selection wizard is displayed.
  2. In the Table or View menu, select the Customer table.
  3. In the Maximum Rows menu, select the number of rows you want to retrieve. If you want to insert rows, you need to retrieve only one row. The Query box will then display the SQL query that corresponds to your request.
  4. In the Sheet Name box, enter the name for the sheet that will be populated. By default the add-in will create a new sheet with the name of the table.

Insert Rows to Sage US

After retrieving data, you can add data from an existing spreadsheet in Excel.

  1. In a cell after the last row, enter a formula referencing the corresponding cell from the other spreadsheet; for example, =MyCustomerSheetInExcel!A1.
  2. After using a formula to reference the cells you want to add to Sage US, select the cells that you are inserting data into and drag the formula down as far as needed. The referenced values you want to add will be displayed on the Customer sheet.
  3. Highlight the rows you want to insert and click the Insert Rows button.

As each row is inserted, the Id value will appear in the Id column and the row's text will change to black, indicating that the record has been inserted.