Importing Csv into nav from excel

Business Central/NAV User Forum
1

Hi,

Is it possible to import this Excel structure into navision:



Quote:
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

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

2

Try to change the field seperator of your dataport to empty space instead of “,”. By default, the field separator is “,”.

3

Take a look at my Dataport:

OBJECT Dataport 50016 Migration 6
{
OBJECT-PROPERTIES
{
Date=22/12/07;
Time=16:52:41;
Modified=Yes;
Version List=;
}
PROPERTIES
{
FileFormat=Variable;
FieldStartDelimiter=None;
FieldEndDelimiter=None;
FieldSeparator=<>;
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table5207;
AutoReplace=Yes;
}
FIELDS
{
{ 1 ;20 ;“Employee No.” }
{ 22 ;7 ;“Entry No.” }
{ 30 ;11 ;“From Date” }
{ 42 ;11 ;“To Date” }
{ 190 ;12 ;Quantity }
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
}
}
CODE
{

BEGIN
END.
}
}

when i try to import with what you said, Its seems all the fields went into one cell which is the first one and itcause an error. Its say "The text K2557,13, 0107/1991… the code feild could only take a maxi of 20 character.

Any ideas.

thanks

4

The message implies you do have commas in the data. Open the file in notepad and have a look, or change the field spearator to a comma and see if it imports.

5

I have open the excel in notepad and removed all the commas and the mported, but it still gives me the same error.

I have changed the field separator back to comma but to no avail.

Any idea again

Thanks

6

Create a new file, one with just the fields you require, in the order they are in the dataport. Do not set start position or width, let the comma separation handle this. I have never personally worked with the starting position, fileds I have not needed I have pushed into a variable, you could try this approach.

7

Sorry. I think I have misunderstood your question. From your first post, I thought your data is seperated by empty space instead of comma. But, looking at your error message, your data is actually seperated by comma instead of empty space.

After reading your question again, i guess you want to import the first column only.

If you want to import the first column only, AdamRoue’s method is the way to go. You can use a global variable as the dataport field instead of the table fields for those column that you do not want to import.

8

I am not importing the first column only, instead am importing all the columns.My data is in CSV in excel. I make sure there is nothing like Commas in the excel.

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

Any ideas please?

9

So have you tried my suggestion?

Do not import the dates into the same field, that is pointless, push them into global variables.

Remove your start and end positions and use a comma separated file, your title of the post is that it is a CSV. Using commas and variables it will work, it is a fairly simple file

10

You are putting an “amount” in the “quantity” field of absence which looks a bit strange, but if you PM me the file, I will test it in the dataport I have just written! Not sure if you can attach in the PM area, but you can email me through my profile.

11

I will Send you the file. I would like you to send me the dataport object you have created so that i can test it ti see if it will work on my database.

Thanks.

12

Nothing yet, have you sent it? If you have I will PM you my email as it is not working!

13

Soory, can you send me your email address, i can’t see it. Can you use the sample in the Quote. I don’t know how to send you the file as an attachement. I have cheched your profile but i have not send your email address.

I would definately want to have the dataport you have created. I want to see how its done.

Thanks

14

I used the function to send the user an email agaainst your profile, you should have it by now!

15

Sorry, AdamRoue i have not seen what you send. Did you send me the dataport? Where did you send me the information. I have checked my profile but i don’t have ant message.

Can you send it through the forum i will get it then.

Thanks

16

I sent the email through the option against your profile, so it would have gone there. I have now sent you a PM.

17

I have sent you the file through the email you sent to me.

Thanks

18

Hi

Okay I see the initial issue, and I cannot do this through a dataport, I am not sure if a developer could, but what you are trying to do is import “x” lines into Navision from “1” line on the CSV.

My only suggestion would be to create “x” dataports, one for each line, and then push everything else into a global. You have 9 distinct records per row. I would ask for the data in a workable format, where one row is one record, but as I said I am not a developer.

For anyone interested imagine a simplified version of the file is as follows:

Employee Number
Entry Number
Date
Value
Entry Number
Date
Value
Entry Number
Date
Value

Essentially the file contains one row and in the above depiction, 3 separate records, so you have to retain the the employee record line and loop around collecting a new line record each time.

The file I have sent you works on one distinct record, so I removed the columns containing repetitive data on different dates, this resolves your issue originally stated.

You might want to start a new thread with the issue properly described so you may get some developer assistance.

19

Thanks. I have tried your suggestion but its not working. Do you make any headway with you dataport on the file i sent you. If you did, can i have i look. I am also not a developer, but i am trying to learn by getting assistanys from people like you and other developers.

Thanks

20

Hi
I am also not a developer. [:D]

In the reply I sent you I sent you a file structure that would work with the dataport. I also sent you the dataport I created.

I also advised you started a new post with the issue clearly described, it is only upon seeing the file that I understood your real issue, which is not with the dataport, but getting the structure of the file in using a dataport. This, if possible, would take coding, if you are not a developer you cannot do it with the structure the file is in, unless you created 9 separate dataports and ran it in 8 times, each dataport with differ pushing the non-used fields into global variables.

If you start a new post developers may read it, they are not reading this one as it is clearly a discussion between the two of us.