Importing CSV file into Nav. from excel to Nav.

kolaboy · 2007-12-22

Hi,

Is it possible to import the same Excel structure into navision:

K2557 01/07/1991 01/07/1991 13 176.89 01/07/1992 01/07/1992 25 176.89
A9638 01/08/1991 01/08/1991 14 176.89 01/08/1992 01/08/1992 26 176.89
A9638 01/09/1991 01/09/1991 15 176.89 01/09/1992 01/09/1992 27 208.74
A9638 01/10/1991 01/10/1991 16 176.89 01/10/1992 01/10/1992 28 187.51
A9638 01/11/1991 01/11/1991 17 176.89 01/11/1992 01/11/1992 29 187.51
A9638 01/12/1991 01/12/1991 18 176.89 01/12/1992 01/12/1992 30 187.51
A9638 01/01/1992 01/01/1992 19 176.89 01/01/1993 01/01/1993 31 187.51
A9638 01/02/1992 01/02/1992 20 176.89 01/02/1993 01/02/1993 32 187.51
A9638 01/03/1992 01/03/1992 21 176.89 01/03/1993 01/03/1993 33 187.51
A9638 01/04/1992 01/04/1992 22 176.89 01/04/1993 01/04/1993 34 187.51
A9638 01/05/1992 01/05/1992 23 176.89 01/05/1993 01/05/1993 35 187.51
A9638 01/06/1992 01/06/1992 24 176.89 01/06/1993 01/06/1993 36 187.51

I have never imported a data in this structure before. Its like this:
Import
K2557 01/07/1991 01/07/1991 13 176.89 .....
A9638 01/08/1991 01/08/1991 14 176.89......
A9638 01/09/1991 01/09/1991 15 176.89......
A9638 01/10/1991 01/10/1991 16 176.89......
A9638 01/11/1991 01/11/1991 17 176.89......
A9638 01/12/1991 01/12/1991 18 176.89......
A9638 01/01/1992 01/01/1992 19 176.89......
A9638 01/02/1992 01/02/1992 20 176.89......
A9638 01/03/1992 01/03/1992 21 176.89......
A9638 01/04/1992 01/04/1992 22 176.89......
A9638 01/05/1992 01/05/1992 23 176.89......
A9638 01/06/1992 01/06/1992 24 176.89......

the .... means its continues to import the next set for K2557 and A9638 employees.
Inorder word can one import taking the records for each employee in column instead of the row. this data is in columns
Any idea?
Thanks

[/code]

kolaboy · 2007-12-24

I have never imported a data in this structure before. Its like this:
For row 1, employee no, Frdate, Todate,entry No, amount, Frdate, Todate, entryNo, amount should be imported. The second row the same untill all the data have been imported.
Note the Frdate, Todate, EntryNo, Amount which are repeated in different column should all be imported in the same field in the contribution table
This is the Structure of the Data to be imported:

EmpNo Frdate Todate EntrNo Amount Frdate Todate EntryNo Amount
K2557 01/07/1991 01/07/1991 13 176.89 01/07/1992 01/07/1992 25 176.89
A9638 01/08/1991 01/08/1991 14 176.89 01/08/1992 01/08/1992 26 176.89
A9638 01/09/1991 01/09/1991 15 176.89 01/09/1992 01/09/1992 27 208.74
A9638 01/10/1991 01/10/1991 16 176.89 01/10/1992 01/10/1992 28 187.51
A9638 01/11/1991 01/11/1991 17 176.89 01/11/1992 01/11/1992 29 187.51
A9638 01/12/1991 01/12/1991 18 176.89 01/12/1992 01/12/1992 30 187.51
A9638 01/01/1992 01/01/1992 19 176.89 01/01/1993 01/01/1993 31 187.51
A9638 01/02/1992 01/02/1992 20 176.89 01/02/1993 01/02/1993 32 187.51
A9638 01/03/1992 01/03/1992 21 176.89 01/03/1993 01/03/1993 33 187.51
A9638 01/04/1992 01/04/1992 22 176.89 01/04/1993 01/04/1993 34 187.51
A9638 01/05/1992 01/05/1992 23 176.89 01/05/1993 01/05/1993 35 187.51
A9638 01/06/1992 01/06/1992 24 176.89 01/06/1993 01/06/1993 36 187.51

Any ideas please?

jonsan21 · 2007-12-24

Hi,

There's always the standard way by using a Dataport.. You can also use File.OPEN and File.READ

Rgds,

Jon.

Alisha · 2007-12-24

I would create a dataport for any table (Employee for example), with the properties

AutoSave = No
AutoReplace = No
AutoUpdate = No

Then import all the columns into variables, and on the trigger OnAfterImportRecord, fill in the data you need in the table/s you need...

kolaboy · 2007-12-24

Alisha Can you give me a sample code or dataport that i can use to do this importation.

Then import all the columns into variables, and on the trigger OnAfterImportRecord, fill in the data you need in the table/s you need...

Can you explain the above clearly for me. I don't understand what you mean by import into variable.

I created the dataport as indicated in the manual but this structure is strange to me.
Any help please?

Savatage · 2007-12-26

I think you need to be clearer about the data structure.

1)?Is each column always in a fixed length.
Once the Entry No becomes 100 or 1000 does all the data now move over 1 space?

2)How often do you import such data?

3)I see 1 cust # then two columns of different data. Will there ever be 3 column or is it always 2?

4)is this the ONLY way you can receive this data or can the person/system providing it change it to a simple 1 column format?

5) in this format does that mean entry 13 & 25 are for a diff employee (K2557) meaning the employee # appears once so anything on that LINE is for that employee or was that just a typo?

EmpNo Frdate Todate EntrNo Amount Frdate Todate EntryNo Amount
K2557 01/07/1991 01/07/1991 13 176.89 01/07/1992 01/07/1992 25 176.89

kolaboy · 2007-12-26

i want to import the data once.

I just give a sample, but it will be more than even three, somethging like 13.
The frdata and todate should all go in one column into navision, but as you can see in the structure they are in different columns, the sam thing applies to the amount and entry no.
The entry number is unique.

This is the only way i can get the data. Its a huge file that they can't give it to me in the samle column you are talking. In that way i can import but ythis format is difficult for me to import. This is why i am seeking assistants from you maybe one of you have once imported files in this format.
The entry on. 13 and 25 are for K2557. Each employee no ion a row correzsponse to the data on the row.
Any idea please.

Savatage · 2007-12-26

personally that's a lot of old data - are you open to entering old years like 1991 just as 1 complete number for the year instead of 1 for every day?

how many employees are we talking about then?
I would probably copy & paste each employee into their own excel sheet so employee no would not be an issue.

then select the 2 thur 13 goups of columns one at a time and paste them under the 1st group until I had 1 complete file for that employee.

(Unless your talking about 100's of employees) :roll:
Then I would record a macro for one employee and use that for the others. Always leaving enuf room for the largest number of rows (like a buffer zone) you can always sort it later to get rid of the blank rows.

Hope that made sence.

remember excel can olny have 65500 rows (give or take)

kolaboy · 2007-12-27

Only 1991 and 1992 won't fit in excel if i copy and paste.
We are talking about over 75000 employees.

Its already put in macro and sorted to get rid of the blank spaces.

Now the huge data is now in the format mentioned above. We just trying to get a way that can let us import the file in this format. The data can only be in this format due to the magnitude of the file.

Any suggestions or sample dataport that i can use please?
Thanks

Savatage · 2007-12-27

75000 employees?
do you have all of these entered into your system already?

see here!
http://dynamicsuser.net/forums/t/21131.aspx

in times like this - I either get a bunch of these 75000 employees to help me with this project or convince whomever wants all this data in Navision that it's better to summize all these entries into one number by year for these employees and start fresh for 2008.

If details are needed you can always look back into the old system.

kolaboy · 2007-12-27

All the 75000 employees are already in the database.

As for the issue of referring to the old system, they said they are going to abaandon the old system completely because its has lots of loop holes.

anyway i convinced the to give me the data in this format:

K2557 1 01/07/1990 01/07/1990 176.88 K2557 13 01/07/1991 01/07/1991 176.89
A9638 2 01/08/1990 01/08/1990 176.89 A9638 14 01/08/1991 01/08/1991 176.89
A9638 3 01/09/1990 01/09/1990 176.89 A9638 15 01/09/1991 01/09/1991 176.89
A9638 4 01/10/1990 01/10/1990 176.89 A9638 16 01/10/1991 01/10/1991 176.89
A9638 5 01/11/1990 01/11/1990 176.89 A9638 17 01/11/1991 01/11/1991 176.89
A9638 6 01/12/1990 01/12/1990 176.89 A9638 18 01/12/1991 01/12/1991 176.89
A9638 7 01/01/1991 01/01/1991 176.89 A9638 19 01/01/1992 01/01/1992 176.89
A9638 8 01/02/1991 01/02/1991 176.89 A9638 20 01/02/1992 01/02/1992 176.89
A9638 9 01/03/1991 01/03/1991 176.89 A9638 21 01/03/1992 01/03/1992 176.89
A9638 10 01/04/1991 01/04/1991 176.89 A9638 22 01/04/1992 01/04/1992 176.89
A9638 11 01/05/1991 01/05/1991 176.89 A9638 23 01/05/1992 01/05/1992 176.89
A9638 12 01/06/1991 01/06/1991 176.89 A9638 24 01/06/1992 01/06/1992 176.89

i succeeded in importing in this format, but i needed the entry no to be generated automatically by the dataport instead humans doing it. This is because the data is too huge

Any idea in how to do this.
can anyone modify the dataport to do that for me

OBJECT Dataport 50019 Data Migration Port
{
  OBJECT-PROPERTIES
  {
    Date=27/12/07;
    Time=13:31:10;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table5207;
      }
      FIELDS
      {
        {      ;     ;"Employee No."       }
        {      ;     ;"Entry No."          }
        {      ;     ;"From Date"          }
        {      ;     ;"To Date"            }
        {      ;     ;Quantity             }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      FromDateVar@1000000000 : Date;
      ToDateVar@1000000001 : Date;
      EntryNoVar@1000000002 : Integer;

    BEGIN
    END.
  }
}

Thanks
[/quote]

Savatage · 2007-12-27

did you see the dataport posted here for your question.

Now just repeat the formula for the # of groups you have (13 you said?)
http://dynamicsuser.net/forums/t/21131.aspx

OBJECT Dataport 60000 Import Employe Entry
{
  OBJECT-PROPERTIES
  {
    Date=27/12/07;
    Time=16.23.33;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    Import=Yes;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table60000;
        AutoSave=No;
        AutoUpdate=No;
        OnAfterImportRecord=BEGIN
                              IF Quantity01 <> 0 THEN
                              BEGIN
                                "Employee Entry".INIT;
                                "Employee Entry"."Employe No" := EmployeNo;
                                "Employee Entry"."Entry No" := EntryNo01;
                                "Employee Entry"."From Date" := FrDate01;
                                "Employee Entry"."To Date" := ToDate01;
                                "Employee Entry".Quantity := Quantity01;
                                "Employee Entry".INSERT;
                              END;
                              IF Quantity02 <> 0 THEN
                              BEGIN
                                "Employee Entry".INIT;
                                "Employee Entry"."Employe No" := EmployeNo;
                                "Employee Entry"."Entry No" := EntryNo02;
                                "Employee Entry"."From Date" := FrDate02;
                                "Employee Entry"."To Date" := ToDate02;
                                "Employee Entry".Quantity := Quantity02;
                                "Employee Entry".INSERT;
                              END;
                              IF Quantity03 <> 0 THEN
                              BEGIN
                                "Employee Entry".INIT;
                                "Employee Entry"."Employe No" := EmployeNo;
                                "Employee Entry"."Entry No" := EntryNo03;
                                "Employee Entry"."From Date" := FrDate03;
                                "Employee Entry"."To Date" := ToDate03;
                                "Employee Entry".Quantity := Quantity03;
                                "Employee Entry".INSERT;
                              END;
                            END;

      }
      FIELDS
      {
        { 1    ;20   ;EmployeNo            }
        { 22   ;7    ;EntryNo01            }
        { 30   ;11   ;FrDate01             }
        { 42   ;11   ;ToDate01             }
        { 54   ;12   ;Quantity01           }
        { 67   ;7    ;EntryNo02            }
        { 75   ;11   ;FrDate02             }
        { 87   ;11   ;ToDate02             }
        { 99   ;12   ;Quantity02           }
        { 112  ;7    ;EntryNo03            }
        { 119  ;11   ;FrDate03             }
        { 131  ;11   ;ToDate03             }
        { 143  ;12   ;Quantity03           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      EmployeNo@1130000 : Code[20];
      EntryNo01@1130001 : Integer;
      EntryNo02@1130002 : Integer;
      EntryNo03@1130003 : Integer;
      FrDate01@1130004 : Date;
      FrDate02@1130005 : Date;
      FrDate03@1130006 : Date;
      ToDate01@1130012 : Date;
      ToDate02@1130011 : Date;
      ToDate03@1130010 : Date;
      Quantity01@1130007 : Decimal;
      Quantity02@1130008 : Decimal;
      Quantity03@1130009 : Decimal;

    BEGIN
    END.
  }
}

Anna did it for 3 groups of columns you'll have to add more.
With the new data you'll have to change the EmployeeNo
to
EmployeeNo1
EmployeeNo2
EmployeeNo3
..etc, etc

kolaboy · 2007-12-27

i have seen the post, i even tried it but giving me errors. "You can't enter K2557 in integer"

Now i just wanted somebody to help me out in getting the entry no. generate automatically.
Any ideas please.
Thanks

Savatage · 2007-12-27

Now you have changed the datafile. It's obvious that the datafile is either NOT CONSENT or you have the Variable for EmployeeNo type of Integer instead of type code.

Now the New data is it

Employee No
Entry No
From Data
Start Date
Amount
*Repeated*
again & again

& how many columns of data is there. We can't help if you don't provide accurate information.

kolaboy · 2007-12-27

i have 62 columns of data
Here is the dataport:

OBJECT Dataport 50019 Data Migration Port
{
  OBJECT-PROPERTIES
  {
    Date=27/12/07;
    Time=13:31:10;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table5207;
      }
      FIELDS
      {
        {      ;     ;"Employee No."       }
        {      ;     ;"Entry No."          }
        {      ;     ;"From Date"          }
        {      ;     ;"To Date"            }
        {      ;     ;Quantity             }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      FromDateVar@1000000000 : Date;
      ToDateVar@1000000001 : Date;
      EntryNoVar@1000000002 : Integer;

    BEGIN
    END.
  }
}

i just need the entry no be be generated automatically. any suggestion or modifications of the above dataport
Thanks

Savatage · 2007-12-27

here's a datatport that will import at least 15 columns of 5 field grouped data.

5207 - Employee Absence table

It's assuming you are using a CSV text file.

It imports
-Employee No
-Entry No
-From Date
-To Date
-Quantity

Please let us know if the data IS NOT in the above format repeated!

As if it was on a single line 15 times.
Fob: http://savatage99.googlepages.com/50030 ... ewYear.fob
Text: http://savatage99.googlepages.com/50030 ... ewYear.txt

I'm not seeing hoe your dataport works? it seems it will skip lots of data.

incase you want to mess around with the dataport creating the entry no. simply import the Entry No into a varible But then Never assign it to anything. Instead you put the code on after get record

"Entry No." := "Entry No." + 10000;

kolaboy · 2007-12-28

Thanks Savatage for the Quick response. I will test this and give you feedback later
Thanks

Dries · 2007-12-28

Hi,

Can you send me your file you wanted to import into Navision and I will create a dataport for you?

Just specify your creteria and into which table needs to be imported.

idrissb@gmail.com

Easy busy.
Idriss.

kolaboy · 2007-12-28

Edriss, i have sent the file to your email.
Thanks

Dries · 2007-12-31

The dataport is created and sent to you.

I tested it on my side and it worked fine.

NB: Please make sure to save your excel file as TAB delimited before you import it into Employee Absence table.

Success.
Idriss

kolaboy · 2008-01-04

Thanks everyone who has given me ideas in helping me solve this issues, especially Idriss.
The dataport is now working the way i wanted it.
Thanks.

Howdy, Stranger!

Quick Links

Categories

Importing CSV file into Nav. from excel to Nav.

Answers