Export grouped data to CSV

harm.poelen

Hi there,

I'm trying to find out how I could write information about shipments (one line per address) to a CSV file. This file then needs to be imported into the shipping program of the customer. What I can find out is that I can not use dataports because you are not able to setup a grouping level. What would be the most elegant solution? I am thinking along two possible ways to go:

1. just creating a custom tabel and retrieve all information from the Sales order / shipping lines that I need and generate the shipping lines I need to export (then I could use a dataport I think);
2. or using a temporary table to hold the data and export this table in some way....

What is the best course to follow? Any suggestions?

Dean_Axon

Hi Harm,

Why not just use a processing report and write the lines to a text file using the TEXTFILE.WRITE command ? At least then you can use multiple data items and group accordingley.


Dean.

RobertMo

To define a data structure for a temporary table you need to specify a real table which should be base for temp table.
So you have to create real table anyway unless you can use some existing table as base for custom table.

Instead of Dataport, you could just create the file in C/AL code and fill the file manually with needed data...

harm.poelen

I ended up creating a fixed table which can also be maintained by the client through a form. Based on the shipping lines I write a line to this table per shipping address. After that the client can change some information and add some information that is not available from the sales orders. After that they can export the file. So then no more grouping is neccecary.

This is the code that manages the export:
Declarations: MyOutStream As OutStream
SHIPExportFile As FILE
ExportString As Text(250)


SHIPExportFile.CREATE('O:\SHIP\Zendingen.TXT');
SHIPExportFile.CREATEOUTSTREAM(MyOutStream);
IF SHIPLines.FIND('-') THEN REPEAT
ExportString := '';
ExportString := ExportString + PADSTR(SHIPLines.ShipToCustomer, 10, ' ');
ExportString := ExportString + PADSTR(SHIPLines.Name, 30, ' ');
ExportString := ExportString + PADSTR(SHIPLines.Address1, 30, ' ');
ExportString := ExportString + PADSTR(SHIPLines.ZipCode, 8, ' ');
ExportString := ExportString + PADSTR(SHIPLines.City, 30, ' ');
ExportString := ExportString + PADSTR(SHIPLines.Country, 2, ' ');
ExportString := ExportString + PADSTR(SHIPLines.Attn, 30, ' ');
ExportString := ExportString + PADSTR(SHIPLines.WhsShipCode, 10, ' ');
ExportString := ExportString + PADSTR(SHIPLines.SalesOrderNr, 10, ' ');
ExportString := ExportString + PADSTR(STRSUBSTNO('%1', SHIPLines.Collo), 10, ' ');
ExportString := ExportString + PADSTR(STRSUBSTNO('%1', SHIPLines.Weight), 10, ' ');
ExportString := ExportString + PADSTR(SHIPLines.Packing, 10, ' ');
MyOutStream.WRITETEXT(ExportString);
UNTIL SHIPLines.NEXT = 0;
SHIPExportFile.CLOSE;
Just one more problem to tackle... and that is that all lines are written in one long first export line. If anybody could give me a pointer on how to insert a CR/LF as the last character in the outputstring, I would be greatfull. I think I am going to experiment with the ArrayIndex and just put the 10 and 13 Char's in there... A bit ackward... but it seams to be the only way...? :-k

harm.poelen

...and indeed... you can just assign the integer value of the character that you are looking for to the string (array):


ExportString[10+30+30+8+30+2+30+10+10+10+10+10+1] := 13;
ExportString[10+30+30+8+30+2+30+10+10+10+10+10+2] := 10;

toshitapori

Hi All,

I want to export data in a text file in a format where i would have on the first line 6 characters and on the second line of the text file some more descriptions and on the third line something and then repeating the whole thing in a similar way. I am trying to do it with a codeunit.

Can somebody pls tell me how should i break the line on text file that it should go on the next line. Please help

Regards,
Toshita

Shenpen

If f is a FILE variable then after f.TEXTMODE(TRUE) all f.WRITE('some text') automatically starts in a new line.

toshitapori

Hi ,
Thanks for the reply. I got few more questions related to it. Now in the spreadhseet format i got a field lines which is of type integer and its length in the spreadsheet is 4. How shud i write it on the text file

format(lines,4) is that correct ? and secondly gota decimal field whose length is defined as 5(7).5(2). How should that be written on text file.?

please advise

regards
toshita

toshitapori

Hi All,

Moving it up a bit

Cheers,
Toshita