0

I am new to Peopletools and Peoplecode.

I have a SQL statement i want to gather the output (all data from the SQL) and create a CSV file.

Can you provide a sample CSV file code?

DO i need to create the file layout for the App engine process?enter image description here

qyb2zm302
  • 6,458
  • 2
  • 17
  • 17
Steve
  • 1
  • 1
  • Does this answer your question? [How can I convert SQL output to a CSV file?](https://stackoverflow.com/questions/66772881/how-can-i-convert-sql-output-to-a-csv-file) – qyb2zm302 Mar 28 '21 at 15:39

1 Answers1

1

You don't necessarily need a file layout to create a csv - you just have to be careful with the data you're putting into the CSV as any columns with a comma in the data would start a new column etc.

&filename = "YourFilePath";
&newFile = GetFile(&filename, "w", "a", %FilePath_Absolute);
&newFile.WriteLine("id" | "," | "type" | "," | "streetAddress" | "," | "addressLine2" | "," | "addressLine3" | "," | "locality" | "," | "postalCode" | "," | "region" | "," | "country" | "," | "source" | "," | "requester");
&rsActiveEmp = CreateRowset(Record.RECORD_TO_PROCESS);
&rsActiveEmp.Fill("where processed = 'N'");

For &i = 1 To &rsActiveEmp.ActiveRowCount
   &emplid = &rsActiveEmp.GetRow(&i).RECORD_TO_PROCESS.EMPLID.Value;
   Local TST_PERSON:PERSON &person = create TST_PERSON:PERSON(&emplid);
   Local array of string &address = &person.getMaxEdAddress();
   &newFile.WriteLine(&person.id_ | "," | "Mailing Address" | "," | &address [1] | "," | &address [2] | "," | "" | "," | &address [4] | "," | &address [5] | "," | &address [6] | "," | &address [7] | "," | "1111111" | "," | "FictitiousPerson");
End-For;
&newFile.Close();

You'll need to manually write the header row and then loop through your dataset to write the rest of the rows. In my example the TST_PERSON is a custom app class I use to create "person" objects for commonly used bio-demo data retrieval. In your case you could just use a SqlExec statement to gather that data for each row.

Dunnage
  • 99
  • 7