-
Import CSV
Import facility is provided to allow for easy upload of equipment data into the database. Import is recommended for initial population of empty database however data can be imported into a database with data. It is user’s responsibility not to import identical data into database more then once. There no validation on duplicity, as it is perfectly legal to have multiple identical items. If duplicate equipment has been created use the remove duplicate equipment function.
IMPORTANT NOTES
- When experimenting with CSV files only import them into a empty database. When the CSV file is correct then import into the main database.
- It is recommended that you modify the example CSV on the WinPATS installation CD to import data.
When creating the CSV file the first line of the file contains all the field names that are formatted correctly. The file is located under the CSV Import folder. Import file must be in CSV (Comma Separated Values) format. MS Excel and other programs can generate CSV file by saving data as .csv
Example of data for import shown in a spreadsheet
Site
Location
Barcode
Description
Make
Next Test Date
Frequency
Test Category
Flinders
HospitalTheatre 1
00230
Portable
HeaterGEC
01/12/2006
Yearly
Small Appliance
Flinders
HospitalTheatre 2
00288
Anesthetic
MachULCO
01/12/2005
Monthly
Anesthetic
MachThe same data when saved as CSV file. CSV files can be viewed when opened in notebook. It is recommended that a CSV editing software is used to modify the CSV file.
Site, Location, Barcode, Description, Make, Next Test Date, Frequency, Test Category
Flinders Hospital, Theatre 1, 00230, Portable Heater, GEC, 01/Dec/2005,Yearly,Small Appliance
Flinders Hospital, Theatre 2, 00288, Anaesthetic Mach, ULCO, 01/Dec/2005, Monthly, Anaesthetic Mach
Note 1: Items might be surrounded by quotes, e.g. “Flinders Hospital”
Note 2: Dates might be shown with “#”, e.g. #1/3/2004#
Note 3: Sometimes the Barcode is shown as 230, as spread sheet program assumes 230 is a number and leading zeros are not shown. The import facility treats Barcode as a special case and restores leading zeros to be as specified in WinPATS™ menu item File > Options > Barcode.
The first line determines and describes what type of data is being imported. Order of columns is not important.
The case of text fields is not important for database storage, i.e. “Flinders” is the same as “FLINDERS”. Case supplied is what is stored and shown on screen and reports.Importing Sites and Locations Only
It is possible to populate database with site name and all site details. There are only 2 columns required; the rest of the data is optional. Compulsory items are Site and Location and the rest of the fields can be blank for individual records or not mentioned at all.
Tables show properties of imported fields. All fields are alphanumeric text. Column headings must be spelt exactly, the case is not important.
Compulsory Fields
Column Name
No of Characters
Note
Site
40
Location
40
If locations are unknown, the location "None" can be used
Optional Text Fields
Column Name
No of Characters
Note
Street
50
Suburb
30
State
3
Postcode
5
Contact Name
50
Contact Ph 1
15
Contact Ph 2
15
Contact Fax
15
Site Notes
50
Site Email
40
Site details are imported into database only when the site is being imported as new to WinPATS™ database. In other words, it is not possible to change Site details for an existing Site by using import function.
Importing Equipment Details
Import function allows user to populate WinPATS™ database with bulk of Equipment data. This a viable and efficient option when an user can obtain assets (equipment) information from another program and package as CSV file.
There are only some columns required; the rest of the data is optional. Compulsory items are Site, Location and Barcode and 2 out of 3 date related items. Rest of the fields can be blank for individual records or not specified at all.
Tables show properties of imported fields. All fields are alphanumeric text. Column headings must be spelt exactly, the case is not important.
The case of text fields is not important for database storage, i.e. “Flinders” is the same as “FLINDERS”. Case supplied is what is stored and shown on screen and reports.
Compulsory Fields
Column Name
No of Characters
Note
Site
40
Location
40
Barcode
20
At least 1 character, padded with leading zeros if needed, does not have to be unique however it is recommended within a site
Site and Location might already exist, or if new are created.
Compulsory Date Related Fields
To be able to report on equipment you need to populate test date/s and frequency of testing. Dates can be shown in any format that can be evaluated as a valid date. It is recommended to use a longer format with month spelt out to avoid confusion as dates might be treated differently on different computers. Use “01/MAR/2004” or check dates after the import if #1/3/2004# format is used.
Two out of the next three are needed for import
Column Name
Date
Note
Last Test Date
Any Date
Next Test Date
Any Date
Frequency
e.g. monthly/yearly
There are three scenarios with supplying dates.
Scenario 1: Last Test Date and Next Test Date are supplied. Frequency of testing id calculated as difference between dates
Scenario 2: Last Test Date and Frequency of testing supplied. Next Test Date is calculated as Last Test Date + Frequency
Scenario 3: Next Test Date and Frequency of testing supplied. Values are stored and Last Test Date is NOT calculated.
Scenarios are evaluated in order 1 to 3 and the first valid combination is accepted. If no valid combination is detected, the dates and frequency are not imported. This and other errors are shown in a log file located in LogFiles folder. LogFiles folder is in the same directory as WinPATS™ task
To find WinPATS™ folder
- Start > Search
- Search for All files and folders
- Enter WinpatsLite.exe into search field and click search
- When file is found, right-click and select Open Containing Folder
Column Name
No of Characters
Note
Description
30
Make
30
Model
30
Serial No
30
Comments
50
Import only allows 50 characters. Database field can store more by being updated manually
Asset No
10
Status
1
Value allowed: P for Pass, F for Fail
Test Category
30
See Note:
Note: Import can link equipment to an existing test category. Category must exist or it is ignored. Before import, populate database with test categories. This can save significant manual import.
Importing The Data
To Import data:
- Go to Administration > Import CSV
- Specify the full path of data to be imported. You can type in the file with path or click on the
button with three dots - Locate file and click open
- Click Validate Import Data
Validation Stage
During the validation process records are read and all fields are evaluated to comply with database specification. When an error is encountered, the user is given the option on how to resolve the situation.
Validation on Number of Fields
Records containing fewer fields than in headings: When the program detects fewer fields in the records than in the headings (line 1), the user is presented with several options.
- Accept This Line: If fields are missing at the end of the line, the record is still good valid and can be imported. It is up to the user to decide if the record is OK by visually examining content in fields. It is recommended to note the line number on import form and choose the Skip This Line option. When all error lines are noted and skipped, data can review at the source, e.g. open CSV file in Excel.
- Skip This Line: This option allows the user to ignore records that contain errors. This is used to find all errors during the validation stage.
- Skip All Errors: This option removes all records with any kind of detected error. The counter on the import form shows the number of lines in the CSV file and the number of records loaded into memory arrays. The difference between the counters shows the number of lines that have been skipped.
