Importing an SQL Table from a non Navision SQL database

DKBGDKBG Member Posts: 5
edited 2009-02-13 in NAV Three Tier
Hi

I am importing data from a Non-Navision SQL database (From-Table) into the customer table in Navision.

In some of the records in the From-Table there are these little squares that stands for TAB - like when you copy something from Excel into Navision.

These little squares causes the import to break down and gives an error that the data type is not supported i C/SIDE.

Does anybody have an idea about how to remove these "squares" from the field value in the SQl table?

Answers

  • ara3nara3n Member Posts: 9,249
    How are you populating the Nav customer table? through dataport?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DKBGDKBG Member Posts: 5
    Hi :)

    I am running the below statement from a codeunit. Opens a connection to the database and executes the statement.

    It runs successfully on other tables and even on records without this little square ](*,)

    I put in a commit to see how far it got, which lead me to that it had to be something in the data, I tried to import...


    ltxtConnectionString:='Driver={SQL Server};'
    + 'Server=NAV-SRV-01;'
    + 'Database=XXX';

    IF ISCLEAR(lADOConnection) THEN CREATE(lADOConnection);
    lADOConnection.ConnectionString:= ltxtConnectionString;
    lADOConnection.Open;

    lvarActiveConnection := lADOConnection;

    IF ISCLEAR(lADOCommand) THEN CREATE(lADOCommand);
    lADOCommand.ActiveConnection := lvarActiveConnection;

    lADOCommand.CommandText := 'SELECT * FROM Stamkartotek';
    lADOCommand.Execute;

    IF ISCLEAR(bglADORecordset) THEN
    CREATE(bglADORecordset);

    bglADORecordset.ActiveConnection := lADOConnection;
    bglADORecordset.Open(lADOCommand);

    WHILE NOT bglADORecordset.EOF DO BEGIN

    Debitor.INIT;
    Debitor."No." := bglADORecordset.Fields.Item('Stam_nr').Value;
    NameTemp := ansi2ascii.Ansi2Ascii(bglADORecordset.Fields.Item('Navn').Value);
    Debitor.Name:= COPYSTR(NameTemp,1,50);


    Any ideas? :)
  • ara3nara3n Member Posts: 9,249
    when you turn on the debugger, where does it stop when it errors?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DKBGDKBG Member Posts: 5
    It stops here:
    NameTemp := ansi2ascii.Ansi2Ascii(bglADORecordset.Fields.Item('Navn').Value);
    

    I put it into a variable because the length of the field in the SQL DB is longer than customer name...
  • ara3nara3n Member Posts: 9,249
    Change your sql statement to
    lADOCommand.CommandText := 'SELECT * FROM Stamkartotek';
    


    SELECT LEFT(Navn,50)  as ShortName,* FROM Stamkartotek
    

    NameTemp := ansi2ascii.Ansi2Ascii(bglADORecordset.Fields.Item('ShortName').Value);
    
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DKBGDKBG Member Posts: 5
    Thanks - that solved it.

    I also got an Error on NULL values but got rid of that with "ActualSize"

    Thank you so much for your quick response :D:D:D
  • ara3nara3n Member Posts: 9,249
    You can change NULL is in your sql statement as well

    Select isnull(FIELDNAME, '') as NewFIELDNAME, * from tablename.




    You are welcome btw.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.