Hello,

I am trying to run some SQL and dump the contents of a table to a .csv file. I am able to use the source code to do this. However, I was then given a new requirement to include (as the first line of the .csv file) the column names. I figured that I would simply dump the contents of the SQL query using 'databasesave', and then open the file up, proceed to the start of the file and insert the column names in csv format. I can dump the original file. I then coded my script to open the 'dump' file and wanted to use the 'FileSetPos' cmd to put my pointer to the start of the file using the following code:

DATABASESAVE( REPORT_OUTPUT_FILE , "CSV" , ROWS_RETURNED )
FILEOPEN( REPORT_OUTPUT_FILE, "LINEMODE", "WRITE", "TRUE", FILE_NUMBER )
FILESETPOS( FILE_NUMBER , 1 , "START" )
FILEWRITE( FILE_NUMBER , "COL1,COL2,COL3\n" )

However, when I try to validate syntax, I continue to receive this error:

'(line number of the FileSetPos cmd): Bad Argument type, expecting variable for return value.'

What is it that I am doing wrong? I simply want to open a file that already has contents, proceed to the start of the file and then write one more line of code.

Please advise.

Thanks.

It will not work this way, because you cannot insert into beginning of the file while pushing down the rest of the file. What you want to do is to add headers either dynamically to an output data or add them to the result set using UNION. For details see my examples below.

Example 1 (concatenation):

Dim( temp_var, string )
DatabaseSave( REPORT_OUTPUT_FILE, "CSV", ROWS_RETURNED )
FileReadAll( REPORT_OUTPUT_FILE, temp_var )
Concat( " ", temp_var )
FileSave( REPORT_OUTPUT_FILE, temp_var )

Example 2 (UNION )

DatabaseRetrieve( "SELECT 'header 1', 'header 2' UNION ALL SELECT column1, column2 FROM my_table WHERE ', ROWS_RETURNED )
DatabaseSave( REPORT_OUTPUT_FILE, "CSV", ROWS_RETURNED )

Depending on the column data types and database type you may need to use certain data conversion functions in the SQL to convert all columns in the result set to varchar type, for example in SQL Server you could use 'convert' function, in Oracle you could use 'to_char'.

: Hello,
:
: I am trying to run some SQL and dump the contents of a table to a .csv file.
: I am able to use the source code to do this. However, I was then given a
: new requirement to include (as the first line of the .csv file) the column
: names. I figured that I would simply dump the contents of the SQL query
: using 'databasesave', and then open the file up, proceed to the start of
: the file and insert the column names in csv format. I can dump the
: original file. I then coded my script to open the 'dump' file and wanted
: to use the 'FileSetPos' cmd to put my pointer to the start of the file
: using the following code: DATABASESAVE( REPORT_OUTPUT_FILE ,
: "CSV" , ROWS_RETURNED )
: FILEOPEN( REPORT_OUTPUT_FILE, "LINEMODE", "WRITE",
: "TRUE", FILE_NUMBER )
: FILESETPOS( FILE_NUMBER , 1 , "START" )
: FILEWRITE( FILE_NUMBER , "COL1,COL2,COL3\n" )

: However, when I try to validate syntax, I continue to receive this error:
: '(line number of the FileSetPos cmd): Bad Argument type, expecting
: variable for return value.'

: What is it that I am doing wrong? I simply want to open a file that already
: has contents, proceed to the start of the file and then write one more
: line of code.

: Please advise.

: Thanks.

Sorry, the third line in Example 1 got lost during posting. It should be as the following:

Concat( "headers here", temp_var, temp_var )

: It will not work this way, because you cannot insert into beginning of the
: file while pushing down the rest of the file. What you want to do is to
: add headers either dynamically to an output data or add them to the result
: set using UNION. For details see my examples below.

: Example 1 (concatenation): Dim( temp_var, string )

: DatabaseSave( REPORT_OUTPUT_FILE, "CSV", ROWS_RETURNED )
: FileReadAll( REPORT_OUTPUT_FILE, temp_var )
: Concat( " ", temp_var )
: FileSave( REPORT_OUTPUT_FILE, temp_var )

: Example 2 (UNION )

: DatabaseRetrieve( "SELECT 'header 1', 'header 2' UNION ALL SELECT
: column1, column2 FROM my_table WHERE ', ROWS_RETURNED )
: DatabaseSave( REPORT_OUTPUT_FILE, "CSV", ROWS_RETURNED )

: Depending on the column data types and database type you may need to use
: certain data conversion functions in the SQL to convert all columns in the
: result set to varchar type, for example in SQL Server you could use
: 'convert' function, in Oracle you could use 'to_char'.