Uploading Text File and inserting in SQL

imclever1205
imclever1205 Member Posts: 94
edited 2012-05-07 in NAV/Navision Classic Client
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.

Answers

  • TonyH
    TonyH Member Posts: 223
    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
    jspopp Member Posts: 54
    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
    thegunzo Member Posts: 274
    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.
    ________________________________
    Gunnar Gestsson
    Microsoft Certified IT Professional
    Dynamics NAV MVP
    http://www.dynamics.is
    http://Objects4NAV.com
  • imclever1205
    imclever1205 Member Posts: 94
    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!!! :D

Categories