Uploading Text File and inserting in SQL

imclever1205

Dear All,

I have written a code to upload a Tab delimited text file for e.g let say
T86 15/03/12 19:10
T92 15/03/12 19:10

The file is being imported correctly when it is in this format.

My issue is if the text file contains some anomalies and starts with the following for e.g
15/03/12 19:10

or it it starts directly with
19:10
it is causing some errors.
Here is the code :

Date1:=0D;
CLEAR(Time1);
PaySetUp.GET;
chrtab:=9;
File1.TEXTMODE(FALSE);
File1.WRITEMODE(TRUE);
File1.OPEN(PaySetUp.FilePath);
File1.CREATEINSTREAM(StreamInTest);
Counter1:=0;
WHILE NOT (StreamInTest.EOS)
DO
BEGIN
i:=0;
int1 := StreamInTest.READTEXT(Text1,100);
Empno:=CONVERTSTR(Text1,FORMAT(chrtab),',');
empcode:=COPYSTR(Empno,1,(STRPOS(Empno,',')-1));
Empdate:=COPYSTR(Empno,(STRPOS(Empno,',')+1));
Empdate1:=COPYSTR(Empdate,1,(STRPOS(Empdate,',')-1));
Emptime:=COPYSTR(Empdate,(STRPOS(Empdate,',')+1));

"Time and Attendance".INIT;
IF (empcode ='') OR (Empdate1='' ) OR (Emptime='') THEN
"Time and Attendance".Error:=TRUE
ELSE
"Time and Attendance".Error:=FALSE ;


Counter1+=1;
"Time and Attendance".Description:=Text1;
"Time and Attendance"."User Id":=USERID;
"Time and Attendance"."Employee Code":=empcode;
"Time and Attendance"."Clocking Date":=Empdate1;
"Time and Attendance"."Clocking Time":=Emptime;


IF i=0 THEN BEGIN
IF NOT "Time and Attendance".INSERT
THEN "Time and Attendance".MODIFY;
END ELSE BEGIN

MESSAGE('ERROR');

END;
IF EVALUATE(Date1,"Clocking Date") THEN;

IF EVALUATE(Time1,"Clocking Time") THEN;
Day1 := COPYSTR(FORMAT(Date1), 1 ,2);
Month1 :=COPYSTR(FORMAT(Date1),4,2);
Year1:=COPYSTR(FORMAT(Date1),7,2);
IF Date1 <> 0D THEN
EVALUATE(DateSQL,FORMAT(Month1 +'/'+Day1+'/' + Year1));


//INSERT INTO THE SQL TABLE
ConnectionString := 'PROVIDER=SQLOLEDB;SERVER='+PaySetUp."Server Name"+';DATABASE='+PaySetUp."SQL Database Name"+';UID='+
PaySetUp."User ID"+';PWD='+PaySetUp.Password;

IF ISCLEAR(ADOConnection) THEN
CREATE(ADOConnection);
IF ISCLEAR(ADORecordSet) THEN
CREATE(ADORecordSet);
IF ISCLEAR(ADOStream) THEN
CREATE(ADOStream);

ADOConnection.ConnectionString(ConnectionString);
ADOConnection.Open;

SQLString :='INSERT INTO .[dbo]. ('+
'[EmployeeNumber],[DateClocking],[TimeClocking])';
SQLString2 :='VALUES('''+"Employee Code"+''','''+FORMAT
(DateSQL)+''','''+FORMAT(Time1)+''')';

window.OPEN(SQLString2);
SQLString3:=SQLString+SQLString2;
ADOConnection.Execute(SQLString3);
ADOConnection.Close;
END;
window.CLOSE;


Can someone please guide me how to proceed for the proper importing of the File.

I have search the forum and seen that many proposed to use a Dataport but in my case,the process will be run on a scheduler so using a dataport will not be appropriate.

Thanks and Regards.

TonyH

Are you inserting this data into a "NAV" table or a "SQL" table...?

Unless you can strictly control the triggers on a NAV table importing into the SQL backend and avoiding the NAV client in all ways is not recommended as it bypasses all NAV business logic.

I've known people to hear that advice and then ignore it anyway; they then import into the table an unsupported character and the NAV application crashes.

You say that you cannot use a dataport, as it needs to be on a schedule, however that's not necessarily true.

Each customer license for NAV comes with a NAS session (To be removed (kind of) in the next version)... you could and should really use that to schedule your tasks, either with the "Job Scheduler" functionality that already exists (Thought I think you have to buy the granule extra) or some custom scheduler.

jspopp

Hi, I agree with TonyH. Don't try to import into SQL directly - the NAV code is there for a reason! If you are using NAS, utilize a codeunit or report. Good luck!

thegunzo

Here is the method that I use to import a comma separated file with a heading row, blank row and then data rows into NAV.
I read the column names from the heading row and use a temporary table to store the data from the rows.

After the line

Empno:=CONVERTSTR(Text1,FORMAT(chrtab),',');

in your code you can handle the row as a comma seperated line.

---

NoOfColumns := CountCommas(LineToRead) + 1;
FOR i := 1 TO NoOfColumns DO
Column := SELECTSTR(i,LineToRead);

CASE ReadType OF
ReadType::Heading:
CASE Column[1] OF
'':
BEGIN
ReadType := ReadType + 1;
END;
ELSE
COPYARRAY(ColumnNames,Column,1);
END;

ReadType::Data:
BEGIN
LineNo := LineNo + 1;
ColumnBuffer."Line No." := LineNo;
FOR i := 1 TO NoOfColumns DO BEGIN
ColumnBuffer."Column No." := i;
ColumnBuffer."Column Name" := ColumnNames;
ColumnBuffer."Column Value" := Column;
ColumnBuffer.INSERT;
END;
END;



CountCommas(LineToRead : Text[1000]) Commas : Integer
FOR i := 1 TO STRLEN(LineToRead) DO
IF COPYSTR(LineToRead,i,1) = ',' THEN
Commas := Commas + 1;

Hope that this will help you.

imclever1205

Thank you guys for the respond,I will try your recommendations and will try as far not to bypass the Navision business logic and that is why before I insert the data into SQL,I am inserting through a temporary table that I created in Navision.

Now I will try the solution that was proposed and see the outcome of it.

Again Thanks!!!