Mapping

QUERY > Mapping

Back to

Mapping

In the mapping stage of the query process, you map the fields of your query to the columns of your output file. Each column header corresponds to a field. Winshuttle Query provides easy drag-and-drop editing of fields. Output options in Query include Microsoft Excel, Microsoft Access, XML, text, SharePoint lists, and Microsoft SQL Server files.

You can map the fields of any data source available to QUERY.

The map pane before a query file is mapped. Click Create Mapping to map manually or click Auto Mapping to automatically map all the query fields.

In the mapping stage of the query process, you map the fields of your query to the columns of your output file. Each column header corresponds to a field. Query provides easy drag-and-drop editing of fields for queries that are already mapped.

To change query mapping

In the Mapping pane, click Update query mapping.

Mapping fields manually

To map fields manually

From the Map pane, click Create mapping. The Mapping screen appears, including the query field form and the empty preview of the results file.
Drag fields down from the form to the file preview area in the order you want.
From the Map pane, click Update mapping. The Mapping screen appears, including the query field form and the preview of the results file.
Drag fields down from the form to the file preview area in the order you want.

Auto map query fields to a data file

To map fields to an existing or new data file from the map pane (Excel or Access)

From the Map pane, click Auto Mapping.
The Auto Map File dialog box appears.
Specify the type of destination file for the data, whether Access or Excel.
Do one of the following:
- To create a new file, specify the path and name of the file.
-or-
- To map the data to an existing file, click the folder icon to locate the file. Click OK.

You can use Auto Mapping to automatically map all data fields in the query file to Excel worksheet columns or Access database fields.

To auto map query fields in the mapper (Excel, Access or SQL database)

In the mapper, click AutoMap.

The fields are automatically arranged in the result preview form.

The AutoMap button as it appears in the Mapping pane.

The result file preview

The result file preview consists of columns or rows into which you drag fields. With this form, you can see how your output file will look.

To change the type of output file

Click Change Destination Type . Click Excel, Access, XML, text, SharePoint list or Microsoft SQL Server.
Note: The use of Query with SQL Server requires Winshuttle SERVER. For more information, see the Winshuttle SERVER for QUERY section below. Winshuttle SERVER is part of Winshuttle Enterprise licensing.

The query field form


S. No	Row ID or sequence of fields for output
SAP field name	SAP name, usually a German acronym or abbreviation
Expression	The table name and field name
SAP Field Description	A description in English of the contents of the field
Field Type	Byte, decimal, integer, string, date or time
Field Size	Number of characters in the field contents
Padding	Controls automatic padding for numeric values of the string fields. Note: If you compare non-numeric data without padding, QUERY may return unexpected results.
Mapped to Column	The letter of the output column to which a field is mapped. Available for Microsoft Excel and Microsoft Access
Fields that apply only to Excel
Excel Expression	The excel formula you want to apply on corresponding field
Expression evaluation cell	The cell which will be used for interim calculations of excel formula
Transform original mapping	Option to overwrite the original data with transformed data
Expression evaluation description	Relevant header description for the evaluation cell

Mapping headers

To map fields to Excel column headers

Drag each selected field and drop it under a column header in the output file.

To map fields to Access tables

Drag each selected field and drop it under a column header in the output file.
Click Add Log Column (optional) to add a column into which the SAP log data can be written.

To modify fields in an Access table

To change the name of a field, right-click the field, and then click Rename.
To change the name of the table, right-click the Table tab .

Why use SharePoint lists as output?

You can output to any existing SharePoint site, not just CENTRAL SharePoint sites, and to any SharePoint lists with the same structure as your file, or to a new or autocreated list.

SharePoint lists are particularly useful when your data is part of a Winshuttle Workflow.

To map fields to XML, SharePoint, or text files

No preview file exists for XML, SharePoint, or text files, and you cannot preview or map results.

To sort mapper fields for XML destination

Click the Sorting button.

Transforming data before the download

By using Excel formulae during mapping stage, you can transform data before downloading it. This feature is available only when the destination type is Excel.

Click Update Mapping on the Mapping pane.
For the field whose output you want to transform, find the Excel Expression column, and then enter the Excel formula. In the example below, a formula is entered for the field GROSS WEIGHT. This formula will be applied to GROSS weight values that start from cell G2 and resultant value will be stored in column H from cell H2 onwards till the last record extracted. The cell H2 is mentioned in the expression Evaluation Cell (refer to step 4 below)
Enter the appropriate Excel cell value in the Expression Evaluation cell. This cell will be used to evaluate the excel expression and place the transformed data. It is mandatory to enter this information. In the example mentioned in step 2, the transformed data will be written into the column H starting from cell H2.
To validate the Excel formula, click Validate . Note that the validate option is supported only for formulae that are entered in English.
To choose to overwrite the original data with transformed data, select Transform Original Mapping. In the example below, the formula is applied to MATERIAL NUMBER and the transformed value is written back in column A. In this example, column I is used only for interim calculations and will be deleted after the calculations are finished.

You may also choose to give relevant header description for the evaluation cell in the column Expression Evaluation Description. This information is optional.

After you save the changes and run the query, you can extract the transformed data.

To open an existing result file

Click Open , and then select the Access or Excel file you want.
Mapping to an existing file does not change the headers in the file.
To add headers to an existing result file, ensure that Write header information on result file is selected on the Advanced Run Options pane. For more information, see Advanced Run Options.

To save a file

Click Save to save the mapping instructions to an Excel or Access file. Or, browse to an existing file to save the new mapping instructions.

Winshuttle SERVER for Query

With the Winshuttle SERVER, you can send extracted data to a SQL Server database. When you save the table, you connect to the server and select a database. Your new table is then created in the database. Winshuttle SERVER must first be connected to SQL servers. For more information, see Winshuttle SERVER Configuration.

To map fields to a SQL Server table

Drag each selected field and drop it under a column header in the output file. The changes can be seen in the field form.
If you want, click Add Log Column to add a column into which the SAP log data can be written.
Click Save.

To open an existing database table

Click Open .

The Connect to Server dialog box appears.
Type in the name of the server you want to connect to or select a server from the list.
Select the type of authentication to use, whether SQL Server Authentication or Windows Authentication.
Do one of the following:
- For SQL Server Authentication, type your SQL Server user name and password.
- For Windows Authentication, your user name and password are automatically captured.
Click Test Connection to verify that your user name is authorized.
To create a SQL auto logon file using those credentials, click Save. When prompted, enter your Windows password and click OK.
Select the SQL database and table. Click Connect.

To save a file to SQL Server table

Click Save to save the mapping instructions. The Connect to Server dialog box appears.
Type in the name of the server you want to connect to.
Select the type of authentication to use, whether SQL Server Authentication or Windows Authentication.
For SQL Server Authentication, type your SQL Server user name and password. Your details are automatically filled in case of Windows Authorization.
Click Test Connection to verify that your user name is authorized.
Select a database where information will be stored. Click Connect. The data movement happens only within the database so you do not see the file open. However, the status bar displays a message to show the file name, the server on which it was created, and the database in which it was stored .

To modify fields in an new SQL Server table

To change the name of a field, right-click the field, and then click Rename.
To change the name of the table, right-click the Table tab .
NOTE: You cannot rename a table or change existing fields in a SQL Server table. You can only add fields or a log column.