Data import using codeunit. CSV has a line >1024 characters

MatStephensSandA

HI.
I am trying to automate a data input process that updates one table with several different csv files in one 'hit'

I have created a form whereby the user inserts the relevent filepaths. This works ok.
I have also created a codeunit that runs one function per csv file. THis also works ok
I then import the csvfile to a text variable one line at a time so i insert the relevent data into a table and ignore the useless data. So far so good...

The issue i have is that the latest file has lines greater than 1024 characters long.

My code looks like this

//GET File and open it and read it
CLEAR(PaymentsFile);
PaymentsFile.TEXTMODE := TRUE;
PaymentsFile.OPEN(TheFile);
WHILE PaymentsFile.POS <> PaymentsFile.LEN DO BEGIN
  PaymentsFile.READ(Line);
  linedeliveryno:= COPYSTR(line,1,20);
  lineorderno   := COPYSTR(line,21,20);
  linelineno    := COPYSTR(line,41,20);
  lineitemno    := COPYSTR(line,61,20);
END;

Line is a variable of text of a size of 1024. 

I accept this might not be the best way to do it and the alternative is to use many dataports rather than one codeunit with many functions however spare dataports are sparce as are funds to purchase new objects.

As i said. If the line is longer than 1024 how can the csv line be imported ??

Any ideas please????

PS we use a native 2.6 DB with a 3.7 client.

Savatage

My first thought would to search the forum for '1024'

in titles only
here's one to start
viewtopic.php?f=23&t=29366

Also search for posts on BIGTEXT

I read this one long ago
http://msdn.microsoft.com/en-us/library/dd338712.aspx
&
http://techblog.byllemos.com/?p=53

klavin

Just branching off this question... for the sake of curiosity, I haven't done this before but maybe someone else knows the answer off hand before I test it...

Could you theoretically just use these variables in a dataport and since they seem to be fixed widths just fill in the StartPos and Width in the Dataport fields?

Just send the filename to the dataport beforehand via function, set it to import, and run it into some table? I'm guessing in this example they aren't trying to import it directly into a table; rather just use some information from it?

I'm curious here along with the solution's that Savatage recommended as well.

MatStephensSandA

Hi Peeps.

thanks for the replies. However as we run a 2.5db with 3.7 clients we do not have the facility to use the bigtext field type.
I am aware that this facility could be done via instreams and possibly even through a blob field. However i am a comparative luddite when it comes to coding.

We are a customer with a developer license so while i am relatively competent with most basic coding. Advanced things such as blob extrapalation (other than storing for a picture) and INSTREAM and OUTSTREAMS are very new and at first sight quite daunting. Someone has also suggested setting the textmode to false but again i have no or little understanding of the concept.

As for the example of the COPYSTR i used. Please excuse me as I just dumped any code in there so you got a flavour for what i was trying to do. The actual file is not fixed length but a regular variable length csv file. In reality i call a different function to handle the extrapilation of the fields from the csv file. The issue was how to do pass the line of the csv to a variable if the line is bigger than 1024 characters.

Thanks for the help and rest assured all that is offered is very much appreciated. until more is offered i will try to find out from the previous forum posts.

PS Savatage- in a previous post (viewtopic.php?f=23&t=24484&hilit=1024) you summed up my issue perfectly. You said

"So you're trying to import the entire csv line into one variable then break it up? there are easier ways of accomplishing this. You want to use a codeunit or dataport?"

. That is exactly what i am tring to do. Other than bigtext that i do not have or using many individual dataports (i do not have any spare) what are the easier ways of doing this??

thanks in advance