2004-10-15JL
Conversion of ENQ842
Program Transform842.exe writen to convert fixed width files (Oxy,temp,salt and station) to csv files.
Files bulk loaded to OWL..ConvertTmp 

BULK INSERT tblOxy FROM 'd:\TmpShared\ru\oxygen.csv' WITH (FIRSTROW=1,FIELDTERMINATOR = ',',ROWTERMINATOR = '\n');
BULK INSERT tblTemp FROM 'd:\TmpShared\ru\temperature.csv' WITH (FIRSTROW=1,FIELDTERMINATOR = ',',ROWTERMINATOR = '\n');
BULK INSERT tblSalt FROM 'd:\TmpShared\ru\salinity.csv' WITH (FIRSTROW=1,FIELDTERMINATOR = ',',ROWTERMINATOR = '\n');
BULK INSERT tblStation FROM 'd:\TmpShared\ru\station.csv' WITH (FIRSTROW=2,FIELDTERMINATOR = ',',ROWTERMINATOR = '\n');

Ship names converted to ship codes. See shipn2c.sql.
Data Merged and exported to Merged.csv using SQL Query Analyser:

SELECT RTRIM(Ship)+','+cast((cast(St.StnID as int)%10000) as varchar(4))+','+RTRIM(Lat)+','+RTRIM(Lon)+','+[Date]+','+RTRIM([Time])+','+RTRIM(Sound)+','+RTRIM(T.Depth)+','+isnull(RTRIM(T.Value),'-9')+','+ isnull(RTRIM(S.Value),'-9')+','+ISNULL(RTRIM(O.value),'-9') FROM tblStation ST LEFT JOIN 
tblTemp T ON T.StnID=ST.StnID LEFT Join 
tblSalt S ON S.StnID=ST.StnID AND S.Depth=T.Depth Left JOIN 
tblOxy O ON O.StnID=ST.StnID AND O.Depth=T.Depth
Order by cast(ST.[Date] as datetime), ST.[Time], St.StnID, cast(t.Depth as float)

See also MakeTables.sql and "bulkLoad and Extract ENQ842.sql"


Oxygen assumed to be ml/l.
Station number set to 'absnum' (see station.dat header) modulus 10000.

csv file produced by SQL above converted to .ice using icescon.exe (csvbottle).







