Dataports

Sbhat

Hi Folks,

I have a excel .CSV file which has following fields

Item No. Description Unit Cost

100 Test One 12.00
200 Test Two 13.00
300 Test Three 14.00

I am writing a simple dataport to bring in the above information.
Is there any way i can import the above file without deleting the caption Item No, Description, Unit cost before i bring the information in. I want to skip the first line in my dataport. Any Ideas?

Thanks for your help.

SB.

kikkoman

i haven't tried it myself, but i think you can create a dataitem Integer and make sure the DataItemTableView = SORTING(Number) WHERE(Number=CONST(1))
which means to loop only once. you only want to go 1 time for the first line and then proceed to your next dataitem.
you can play around with the dataport fields, but i guess just create 3 dataport fields of type=text.

then create the next data item you need and process it the same way you would originally if you didn't have the header columns with 3 dataport fields.

this is how you would do it when you want to export column names, so it should work the same way going the opposite way.

http://www.mibuso.com/howtoinfo.asp?FileID=8&Type=howto

hope this helps!!!

Sbhat

Sorry, that wont work because when you import you are bringing in value and even if you put the currdataport.skip it wont skip the first line in the spreadsheet. There must be a way.

SB.

fb

I want to skip the first line in my dataport....

Here's another wild and crazy guess:

In the OnPreDataItem trigger, code the following:

IF CurDataport.IMPORT THEN BEGIN
  CurrFile.READ(txtFirstLine);
  // make sure the first line was a header...
  IF COPYSTR(txtFirstLine,1,8) <> 'Item No.') THEN
    CurrFile.SEEK(0);
END;

Sbhat

Hi fb,

No, it does not work. When i added your code it just goes through but no values are bought in.

SB.

DenSter

Add a global boolean type variable to your dataport called FirstLine. In OnPreReport add a line of code:

FirstLine := TRUE;

In the OnBeforeImportRecord trigger of your first dataitem, you program the following:

IF FirstLine THEN BEGIN 
  FirstLine := FALSE; // so it does it only once
  CurrDataport.SKIP;
END;

That should do the trick I think. I'm just making this up as I go, but that makes sense to me. Let me know if that does it.

You could even add the first line of code to each dataitem's OnPreDataitem trigger to take care of headers of other tables.

Sbhat

Even i thought that would work but Unfortunately it does not do the trick.

SB.

kikkoman

sorry for the first incorrect response...
hmmmm...maybe you can just create 3 variables with type text and bring them in like that, and when its not the first line, use EVALUATE(..) function to change the text variable to a decimal or integer that you need for your fields?

DenSter

Sorry that didn't work Suresh, it should just be a property of the dataitem don't you think .

Urmas

The direction is correct ;-)

First
The data itself has a flaw what can't be ignored -
You do not have a proper field delimiter. Currently it seems that you have 3 fields but as you have a space as a field delimiter, the system has no way to understand that - it assumes that the first three fields are (for first record) filled with 100, Test and One and naturally the parsing of the Unit Cost decimal will give an error.

Second.
In order to keep the caption you can use any inbuilt variable type. You can use a (text) variable(s) in dataport items of this variable where the caption fields will be imported. If the layout is similar as in your example,you do not need to limit the variable (number = x) as the blank line between the caption and data will be understood by system as division between two dataitems.

RobbieX

I wanted to do the same thing:
I wanted to import a CSV file which had originally been exported from Navision with headings for each column.
Extra data was added by members of staff who have no access to Navision, and then the file is returned to be imported back into Navision, so that the new data can be added to the relevant tables in Navision.
But I wanted to avoid having to open the file and delete the top 2 rows to get rid of the headings and the blank line.
I followed the steps described above and it worked fine. Thanks!
As above, it is a bit unclear, this is what I did:

1. To the dataport add an Integer dataItem above the others to deal with the top 2 lines.
2. In the properties for Integer, set the key to Number and Number=CONST(1)
3. Make a global text variable long enough to accept each of the headings.
4. For the Integer DataItem, use the same text variable as the dataport fields, using as many as you have headings/columns.

Thanks again for your help everyone!

gjvk70

<Sorry, but already solved>


I have a similar problem. I have a file exported from a non-Navision application with in the first line some general info on the import file, like totals etc. The following lines in the file are records to be imported into a G/L journal.

What I did is create a dataitem 'Integer' with in the tableview-properties Nummer=CONST(1) to run it once to capture the first line of the importfile and pass it to a custom-table.
The second dataitem contains the G/L Lines.

When running the dataport I get an error on the first dataitem which says "Table Integer is read only" (translated from Dutch version, so I don't know the English equivalent of this message).
Of course I am not trying to write to the integer table. In the code on this dataitem I want to insert a record to the custom table.

Any help appreciated.

Best regards,
Gerard

Marije_Brummel

gjvk70 wrote:

<Sorry, but already solved>

Is this solved or not :?

You should put No in the autosave/update/replace property.

Please start new topic if you have a question, this helps keeping things clear. 8)