If the writing systems (measurement systems, software, etc..) cannot provide measurement files according to the Q-DAS standard (Q-DAS ASCII transfer format), there are several options for importing "non-Q-DAS" formats.
|
Data acquisition - alternative ways
This document lists and explains the different options. |
English:PDF |
Table of Contents
1 Foreword
There are multiple options in the software for importing non-Q-DAS formats. If the writing systems (measurement systems, software etc..) cannot provide DFQ files (Q-DAS ASCII transfer format), these options might be used for import of the data. In this document the different options are listed and explained.
These formats and options are available for the import:
CSV/TXT files
XLS files
Import using the converter
Manual data transfer by copy/paste function
However, we have to address the various disadvantages that are coming from using these options.
The CSV / TXT / XLS import should only be used as an exception. Permanent use of these import variants should be prevented. A clear and automated data flow is necessary for optimal use of Q-DAS software.
There is no further development of the two variants, even if each one offers more or less possibilities. Technical development is no longer an option.
Furthermore, it is not possible to save the selected settings. Each individual file must be opened separately, and the settings must be readjusted for each file.
Microsoft Excel must be installed for the XLS / XLSX import. The language settings of Excel must match the language settings of the operating system (the computer).
Import using a converter at least allows the process to be automated. However, the creation of the converter, as well as its testing in the project, is a service for which a fee is charged. And unfortunately, these are usually not one-time costs. If the original format is changed in any way, the converter must also be changed, another testing phase must be done and that means new order and more costs.
More than 20 years ago, Q-DAS defined the Q-DAS ASCII transfer format as an exchange format from measuring machine to processing software. This standard of the industry is not only demanded by the working group AQDEF (Advanced Quality Data Exchange Format) but is currently also under review by ISO as an international exchange format for SPC data. For this reason, even converters should be questioned, and the producer of the writing system should be required to create DFQ files.
2 CSV, Text Import
CSV or TXT files might be imported using this option. There are some basic requirements that must be followed:
The measured values must be in one column.
Additional data must be in one column.
The maximum header data that can be taken over is the characteristic description. It must be written in the same column as values in the upper cell. Other header data such as specification limits cannot be taken over.
Formats: .CSV and .TXT
2.1 How to call the dialog
The dialog for importing CSV and text files can be found in File | File | Import (*.csv, *.txt)
Clicking on the option, the software will ask the user to select the file that should be imported.
After selecting the file for import, new dialog will appear. Explanation for this dialog is in the next subchapter.
2.2 Settings import dialog
The dialog is divided into multiple sections.
In this section, the path to selected file for import (read) is shown and the path and name to a DFQ file that will be created from the imported file (write).
If wrong file for import was selected in the first place, it can be changed here using the button right next to the path.
If the DFQ file should be saved somewhere else, or should it have different name, it is possible to change that using the button next to the path.
In this section users can determine the separator used in the CSV or TXT files used for import.
In this section, the delimiter for thousands and decimal can be set.
In this section, presentation of the rows is shown.
In this section, setting for the import of the information can be changed:
Line for characteristic description Number of line where name of the characteristic is present in the file.
Import starts in line The line number from where the Q-DAS should start to take over the values.
Number of lines for analysis The total number of lines considered for the import of values.
By clicking inside one of the columns in the table, the setting for this column can be changed.
The options are as follows:
No takeover The whole column will not be imported in any way.
Value The column is imported as a characteristic with values. Integers and decimal numbers are allowed. The text is not allowed.
Nominal value The column is imported as nominal characteristic.
Time / Date Time and date can be taken over. The format must correspond to the settings of the computer.
Batch number / Text / Part ID number / Production number / Order Additional data fields which can be imported. Text and number are allowed. The information will be associated with all the characteristics in the DFQ file.
|
The nominal characteristics will not be explained in the document. |
After all necessary setting in the dialog, click on OK button in the lower left corner. The qs-STAT will open the created DFQ file with imported information from the source file.
3 Excel - Import
Excel files might be imported using this option. There are some basic requirements that must be followed:
The measured values must be available in one column or row.
The only header data that can be taken over is the characteristic description and specification limits.
It is not possible to take over additional data.
Formats: .XLS, .XLSX and .CSV
In some cases, only the first tab of the source document is displayed and can be imported. This is depending on the version of the excel and Q-DAS. Please, use always only the first tab to be sure that data can be imported.
When using the import function on the terminal server, the Excel must also be installed on the terminal server.
3.1 How to call the dialog
The dialog for importing Excel files can be found in File | File | Excel Import (*.xls, *.xlsx)
Clicking on the option, the software will ask the user to select the file that should be imported.
After selecting the file for import, new dialog will appear. Explanation for this dialog is in the next subchapter.
3.2 Settings import dialog
|
|
The import dialog might look different based on the version of MS Office is used. Only one possible scenario is described in this document. |
The dialog is open together with a blank excel file in the background. Users can see the cells from the first tab but cant do anything yet. Right mouse click in the table and choose the option Open.
The file to be imported should pop up in the blank excel that was opened before. Now it is possible to define a setting for the import.
The setting is divided into 4 sections:
1. This setting depends on the structure of the file to be imported. If the characteristics are sorted in lines, choose option Characteristics by line. If the characteristics are sorted in columns, choose the option Characteristics by column.
2. These are the characteristic information that can be taken over. First the cell, containing the information, must be selected in the excel sheet. Then clicking on the button in the setting, the information will be marked for import.
3. This is a measured value area. First all the cells with the measured values must be selected in the excel sheet. Then clicking on the button in the setting, the values will be marked for import.
4. When all settings are done, click on this button will import the file in to the qs-STAT working space.
When all the setting are done, it can look like this:
As can be seen in the screenshot above, the user must mark the cells in the excel sheet.
After importing the file, the working are of qs-STAT will show the default window layout with the imported data.
|
|
The imported data set is not automatically saved. If it is intended to be saved, user must do this manually. |
3.3 Troubleshooting
3.3.1 Language problem
During the import of excel files, error related to the language setting might occur.
If the following message appears during the import, the operating system and the office package used have different languages.
Microsoft says the following about this:
You run an English version of Excel. However, the regional settings for the computer are configured for a non-English language.
If the client computer runs the English version of Excel and the locale for the current user is configured for a language other than English, Excel will try to locate the language pack for the configured language. If the language pack is not found, the error is reported.
Install the Multilingual User Interface Pack for your version of Office.
The solution for this problem is to either change the language of the operating system or the language of the Office.
3.3.2 Hidden columns / lines
When the excel file contains some hidden lines or columns, these will be imported as well.
Lines between 7 and 13 were hidden. It can not be seen during the import.
However, the hidden lines are imported as well as rest of the cells picked. In our case, 7 values were taken over.
3.4 Unreadable formats
There are some formats of data in excel sheet that import will not take over. Here is a lost of some of them:
1. Cells merged together
During creation of the excel sheet, use only 1 cell per information (value / description). If the information will be stored in the cell that was merged, import will try to take data over but in most cases, the data will be imported in the incorrect way.
2. Side by side subgroups
Measured values must be in 1 row or 1 column per characteristic. It is not possible to import data collected in the separate tables for each characteristic. The import will be done in incorrect way.
3. Write-protected cells and pages
When cells, columns or lines are write protected, this could cause also the problem, that an import is not able.
4 Converter
Sometimes, measurement data are saved using special formats, which cannot be easily imported using traditional Q-DAS import options. For such cases, special converters can be developed and used in manual or automatic way.
|
|
To create the new converter or make changes to the existing one is a subject for fee-based workshop with Q-DAS engineer. For more information, please contact the local Q-DAS office. |
Since it is not possible to explain all functions a converter can have, the example of DFI converter will be used to roughly explain the basics.
DFI converter is a very old option in our software. It was used for converting the data in the DFI/DFT format which was the old Q-DAS format from DOS versions. This converter is no longer needed for practical purposes, but it is a good example for explaining the basics.
In the default installation of Q-DAS, there are 2 different converters. One is used for manual conversion (Conv_DFI.pas) and the other is used for the automatic conversion using the upload in the Q-DM (Upload_DFI_DFT_K00xx_Files_Conv.pas).
|
|
When ordering a new converter, it must be taken in an account, whether it is supposed to be used manually or automatically by Q-DM. |
4.1 How to call the dialogs
4.1.1 The converter settings dialog window
To display the dialog for setting up a converter, go to File | File | Converter settings
The dialog window itself will be roughly explained in the next chapter.
4.1.2 The conversion dialog window
To display the dialog for conversion, go to File | File | Conversion
4.1.3 The converter settings dialog window Q-DM
It is possible to call the converter settings dialog window directly in the module Q-DM Data management. This might be more convenient for the systems with automatic conversion.
The window is called using Data source | Conversion | Conversion from template
4.2 The converter settings
The possibilities in this dialog window will be only roughly explained in this document. When customer buys the new converter, the development team gives the information about settings that are needed after the delivery of the converter file.
There might be more then 1 configuration for multiple converters if it is necessary. Using the button New, the configuration can be created.
The Q-DAS transfer format must be chosen. In todays day and age, it is not necessary to use anything else then DFQ.
Location of the Q-DAS script (converter file) must be given. Use the 3 dots on the right side to search for the file within the system.
After all necessary settings, use the button Save at the lower left corner to save the configuration.
4.3 Import conversion procedure
4.3.1 Manual conversion
For manual conversion, the converter and file to be converted must be chosen using the dialog for conversion.
After selecting the file, click on the OK button in the lower right corner. The files will be converted and opened in qs-STAT working area with default window.
The savings of the converted file must be done manually by the user.
4.3.2 Automatic conversion
Automatic conversion takes place in Q-DM Data management module. Whitin the upload job, the conversion must be set. If everything is set correctly according to the instructions from our development department, the files will be converted and saved to the database automatically during the upload job.
|
|
No further explanation will be given in this document. For more information, please contact the local Q-DAS office. |
5 Manual data transfer by copy/paste function
One-time easy copying of the data might be desired in some cases. Users can always use the copy/paste function to transfer the data manually. This chapter will show tips and tricks for this task.
5.1 Header data transfer using the characteristics table
A little-known option is the creation of further characteristics using the characteristics table. As an example, the table in the screenshot below is to be used.
The set of characteristics is considered unknown. For that reason a new characteristic (only one) is created in qs-STAT.
After opening the characteristics table, it initially has the standard configuration:
The table of characteristics must be configured in the same view as in the Excel sheet.
Characteristics will be set in columns:
Selection of the same information fields, as in the excel sheet, must be done. Use the right-mouse click inside the table and select the option Selection of columns of display:
The selection is done in the special dialog window:
Once the configuration is done, the characteristics table has the same rows as the Excel sheet:
If the complete content of the characteristics data in the Excel sheet is copied and pasted into the first cells of the characteristics table, a question appears asking whether further characteristics should be created.
The other characteristics will be created and the measurement data per characteristic can be inserted:
5.2 Measured value transfer
Measured value transfer using copy/paste option can be very easy task. However, there are few things readers has to consider before doing so.
5.2.1 Measured values from variable characteristics
To copy and paste just the measured values from excel sheet to value mask is pretty easy. Open the excel sheet, the value mask inside of the qs-STAT and make sure that copied values are within the right column (characteristic).
5.2.2 Measured values with additional data alpha numeric fields
It is also possible to paste the additional data together with measured values.
The excel sheet might, for example, look like this:
To be able to paste this information in the value mask, the setting of the view of additional data in the value mask must be done. Using right-mouse click inside of the value mask and select the option Display / Hide additional data fields. In the new window, choose the needed additional data fields, set it for all characteristics and click on the OK.
The data that was copied will be stored in correct order:
It is not always possible to have the correct order since the data in the excel sheet might be in the different. One of the possible scenarios is 1 column of 1 type of additional data for all characteristics together:
|
|
Only this one simple example will be explained in this documentation. However, there are many combinations that can be used. |
In the value mask, the respective additional data field must be shown. In our example only for the last characteristic. The special setting Common additional data fields should also be activated:
Thanks to this setting, the additional field is automatically taken over by the all characteristics:
5.2.3 Measured values with additional data catalogue fields
All the possibilities of copying the additional data is the same, even for catalogue fields. However, the user must be aware of how the catalogue fields are working within the Q-DAS system.
|
|
For information about the catalogue fields and catalogues itself, please read the document Use of catalogues that can be found in the help section under Handling and configuration | Catalogues | Use of catalogues. |
The catalogue additional data in the excel sheet must be written using internal consecutive numbers, the description numbers or the the description of the respective record in the catalogue.
One or combination of these options can be used, depending on the settings of the value mask.
If the Internal key is used in the value mask, only the records using the internal consecutive numbers can be taken over:
If the Alpha-numerical number is used in the value mask, both internal consecutive numbers and description numbers can be taken over.
|
|
Be careful when using plain numbers as the description numbers inside of the catalogue. In such case, use either strictly consecutive numbers setting in the value mask (internal key) or description for copy / paste option. |
If the Description is used in the value mask, both internal consecutive numbers and descriptions can be taken over
In case the user would use the records that does not match the value mask settings or does not match the catalogue records, error will occur:
When this error occur, the additional data that triggered the error will not be taken over.
The error can be ignored. In such case no more information will be given by the software. If the user acknowledge the error, using the OK button, information about the problem will be given in the new dialog window.
The error description can be saved in the Export tab of the new dialog window.
5.3 Attributive measured data transfer
Attributive characteristics can have only 2 results OK and NOK. In the value mask, it is displayed as the plain text. However, when user wants to copy and paste the attributive measurements, the internal numbers 0 and 1 must be used. 0 is for OK and 1 is for NOK.
When the plain text is pasted, the error will occur, and the invalid records will not be taken over.
It does not matter whether the error is acknowledged or ignored. No more information will be given by the software.
5.4 Ordinal and nominal data transfer
Ordinal and nominal characteristics are kind of attributive characteristics but possibly with more results then just OK and NOK.
|
|
It is not the intention of this document to explain what ordinal and nominal type of characteristic is. For more information, please contact the local Q-DAS office. |
Strictly from copy and paste perspective, the ordinal and nominal characteristics has to be handled in the similar manner as catalogue additional data fields. In the subchapter 5.2.3 the behaviour of the copy / paste is explained.
Since it is common for nominal and ordinal catalogues to have the identical description numbers or descriptions (because of sub-catalogues), it is the best practice to avoid using those settings in the value mask and use only the internal key setting for copy / paste function.
