- Andere Unternehmen von Fluke:
- Fluke
- Fluke Biomedical
- Fluke Networks
- Fluke Process Instruments
Using SQL to Import Excel CSV Files Daily
Note: this technical note is for advanced MET/TRACK software users only.
Problem
The customer has a Field (I4247 - defined as Department) that has Merge Informatoin that needs to be updated with new information from an Excel file on a regular basis (once a day to once every two weeks).
The calibration laboratory receives this new Excel file from the Human Resources/Quality department every so often, giving Point of Contact for department; New Phone number; New Pager; Manager; Manager's Phone; etc. The Merge table for Field 4247 needs to be updated with the new information from the Excel spreadsheet.
Solution
First, save the Excel worksheet as a CSV file (Comma Separated Variables). Then create a SQL script that is launched from a Shortcut to update all changed items. Create a shortcut with the following in the target line:
Note: that the paths below show the default MT password "pwd=mt." Your path should reflect your actual mt level password.
The path for version 6.11A would be
"C:\sqlany50\win32\isql.exe" -c "dsn= Calibration Data;UID=mt;PWD=mt" C:\Update_Mrg4247.sql
The path for version 7.00 would be
"C :\sybase\SQL Anywhere 8 \win32\dbisql.exe" -c "dsn= C alibration Data;UID=mt;PWD=mt" C :\Update_Mrg4247.sql
The SQL file
Use a text editor to create the following file and save as Update_Mrg4247.sql.
// Create a new table for importing the excel file into
Create Global Temporary Table Mrg4247_tmp
(
// add all the columns, types of columns, and length of column
Department_tmp Char(30), Department_Name_tmp Char(30), WWID_tmp Char(8), Coordinator_tmp Char(30), Phone_tmp Char(8),
Pager_tmp Char(8), Contact_Mail_Stop_tmp Char(12), Manager_tmp Char(30)
);
// input data from Comma Separated File (*.csv) into the temporary table.
// NOTE: you must name this file accordingly
input into Mrg4247_tmp from 'D:\Full Coord List1.csv';
//Update the Merge table 'Mrg4247' with the new information from the temporary // table.
update Mrg4247 set
// fields that you want updated
"Mrg4247"."Name" = “Mrg4247_tmp”.”Department_Name_tmp”, “Mrg4247”.”wwid” = “Mrg4247_tmp”.”WWID_tmp”, “Mrg4247”.”Key_Contact” = “Mrg4247_tmp”.”Coordinator_tmp”, “Mrg4247”.”Phone” = “Mrg4247_tmp”.”Phone_tmp”, “Mrg4247”.”Pager” = “Mrg4247_tmp”.”Pager_tmp”, “Mrg4247”.”Contact_Mail_Stop” =
“Mrg4247_tmp”.”Contact_Mail_Stop_tmp”, “Mrg4247”.”Manager” = “Mrg4247_tmp”.”Manager_tmp”
// from the temporary table we created. from Mrg4247_tmp
//where “Mrg4247”.”Merge” = “Mrg4247_tmp”.”Merge_tmp”;
where “Mrg4247”.”Merge” = “Mrg4247_tmp”.”Department_tmp”;
//delete temporary table when we are done drop Table Mrg4247_tmp;
Note: your CSV file must be in the same order as the fields in your SQL statement.
- Startseite
- Produkte
- Neue Produkte
- Elektrische Kalibrierung
- HF-Kalibrierung
- Datenerfassung und Testgeräte
- Temperaturkalibrierung
- Feuchtekalibrierung
- Druckkalibrierung
- Kolbenmessgeräte
- Controller/Kalibratoren
- Druckmonitore
- Druckwaagen
- Manuelle Kalibratoren und Monitore
- Handheld Pressure Calibrators
- Luftdaten-Kalibrierung
- Messgeräte zur Überwachung der Umgebungsbedingungen
- Druckkalibrierungszubehör
- Benutzerdefinierte Druckkalibriersysteme
- Software zur Druckkalibrierung
- Durchflusskalibrierung
- Prozesskalibratoren
- Kalibriersoftware
- Service und Support
- Alle Kalibriergeräte
- Hand-Messgeräte
- Kaufinfo
- Aktuelles
- Schulung/Veranstaltungen
- Literatur/Ausbildung
- Service/Unterstützung
- Service Request (RMA)
- Serviceprogramme
- Technischer Support
- Wissensbasis
- Akkreditierung
- Autorisierte Servicezentren
- Calibration Certificates
- Community Forum
- My MET/SUPPORT
- Produkthandbücher (Bedienungsanleitungen)
- Sicherheitsdatenblätter (SDS)
- Recyclingprogramm
- Safety, Service, and Product Notices
- Gewährleistungen
- Software-Downloads
- Über uns