Cognos script - copy 2 csv files to one csv file

[galaxy0815]

Hi,

I have an issue with the Cognos script language (Version 7.4.2025.0). The issue I have is that I have 2 .csv files saved in a folder which I want to copy with the help of Cognos script language automatically to one final .csv file. All my tests failed till now. The last code I tried was the following:

Sub Main()

Dim readfile as string
Dim writefile as string
Dim moveline as string

Dim x as Integer
readfile = "C:\filecopy\file1.csv"
writefile = "C:\filecopy\file2.csv"

Open writefile For APPEND As #1
Open readfile For INPUT As #2

Do While Not Eof(2)
Line Input #2, moveline
Write #1, moveline
Loop

End Sub

Can somebody let me know how I can copy 2 csv files together in one .csv file with the help of Cognos script language? The best would be that the header row would just appear once in the final file.

Thanks in advance for your help,

Regards,
Martin

 

[drlex]

Martin,
Your script doesn't work as it will consider each row as a single string and therefore bork your columns.

If the file names were always the same, and they were in the same place, you could use a batch file with the copy/b command (copy/b c:\filecopy\file1.csv+c:\filecopy\file2.csv c:\filecopy\file.csv) and call it with the Shell function.
Downside is that you can't exclude the second file's header from the output.

If you can't hard-code the file names and locations, and you have Excel on the machine, you can do the join within Excel by opening the first file in Excel and then appending the second. This can be set so as to exclude the header row on the second file. Downside is that it's a dozen lines of code and is much slower.

soi la, soi carré

 

[galaxy0815]

Hi,

Thanks for the reply. The issue is that both ways do not work for me.

1) Does not work because I run several reports within my cognos script (All reports have different names) and I need to use variables in the "copy files together" part. So no fix filenames can be used

2) Does not work to use excel because some reports have more than 65000 lines. So excel cannot be used. At the end I want to receive one csv file, containing the data from all reports I want to copy together.

Is there no way that I can do it in cognos script to copy 2 csv files together 2 one?

(Maybe I can do it with the shell comand: What is the code that needs to be written in the batch file and how can I call it in cognos script? (I just had the idea to temporary save my reports with the same name so that the batch file can copy them together))

Thanks in advance

 

[drlex]

Martin,

The shell command is easy:

Shell("C:\BATfolder\Mergefiles.bat"

Mergefiles.bat would simply be

copy/b c:\filecopy\file1.csv+c:\filecopy\file2.csv c:\filecopy\file.csv

It is possible to use CognosScript, but you'd need to specify the columns (and their type) to ensure a match.

Example using only two columns, one integer and one string

Sub Main()

Dim readfile as string
Dim writefile as string
Dim firstcolumn as integer
Dim secondcolumn as string
Dim headerline as string
Dim x as Integer

readfile = "C:\book2.csv"
writefile = "C:\book1.csv"

Open writefile For APPEND As #1
Open readfile For INPUT As #2
Line Input #2, headerline      'as header line not required

Do While Not Eof(2)
Input #2, firstcolumn,secondcolumn
Write #1, firstcolumn,secondcolumn
Loop

End sub

lex

soi la, soi carré

 

[galaxy0815]

Thanks very much for this. I will test it and let you know if its working.

Thanks!

 

[galaxy0815]

Hi,

I have tested the solution with the shell comand and it works perfectly!

Thanks very much for this.

One last question:
To get my script fully working, I need to know the cognos functions for the following:

- Delete just one file
- Create an empty file
- Delete the content of an existing file

Can somebody please help me with this? I searched already in the documentation for these functions, but I cannot find anything that can be used for the above topics

 

[drlex]

Martin
The cognos script is light on this functionality - I use the SHELL command to access the DOS 'DEL' function in a batch file to remove files (like the FILECOPY example prior) and use the cognos script FILECOPY function to copy a blank version in place of the unwanted version.

e.g.

dummyfile = "C:\Brains_Reports\Blank WDP Version.csv"
sourcefile = "C:\Brains_Reports\World Domination Plans.csv"
FileCopy dummyfile,sourcefile

soi la, soi carré

 

[galaxy0815]

Thanks for this.

Now my really last question on this issue?

Is it possible to write a string to a file without the "-signs around it:

Example:

I have the following code:

strTemp1 = "H:\Report_Copy\Files\Final_File.csv"
strTemp2 = "H:\Report_Copy\Files\Tempfile.csv"

'ceates the above mentioned copy batch file comand
strmergefile = "copy/b " & strTemp1 & "+" & strTemp2 & " " & strTemp1

mergebatfile = "H:\Report_Copy\Macro\Mergebatfile.bat"
Open mergebatfile For APPEND As #1
Write #1, strmergefile

The issue with this is that the final file looks like this:

"copy/b H:\Report_Copy\Files\Final_File.csv+H:\Report_Copy\Files\Tempfile.csv H:\Report_Copy\Files\Final_File.csv"

So the command line in the file is covered in "" signs. How can I input this line in my file without the "" signs?

Like this I am able to create the dos files dynamically by the cognos script language

Thanks in advance

 

[drlex]

Martin,
I'm unaware of a way to do this, but have not needed to, as one can have a generic merge batch command and just change the files before and after calling it.

Using the original batch example

Sub Main()

Dim readfile as string
Dim writefile as string

readfile = "C:\More Shopping.csv"
writefile = "C:\Shopping List.csv"

Kill "C:\filecopy\file1.csv"
Kill "C:\filecopy\file2.csv"
'Not necessary
Filecopy writefile, "C:\filecopy\file1.csv"
Filecopy readfile, "C:\filecopy\file2.csv"
SHELL("C:\mergefiles.bat")
For x = 1 to 100000
Next x

Filecopy "C:\filecopy\file.csv", "C:\Complete Shopping List.csv"

End sub

The for...next is a mere delay to allow the merge to complete, otherwise one can't copy the result. It may be tidier to use sleep if one sets a link to libraries -

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

and then call with

Sleep {time in ms}

soi la, soi carré

 

[galaxy0815]

Hi,

Thats for this. I solved it with the general batch command file as per your explanation above. One really last issue I have with my script is the following (After this it should work as expected):

How can I check if a file exsits on the server?

Examplefile: c:\tempfolder\filetocheck.csv

How can I check if this file exists with cognos script?
(For a folder I have found a solution, but not for a file)

Thanks in advance.

 

[drlex]

The DIR function is what you want - the help file has a good example to aid understanding.


Slightly off-topic, I'm guessing you're too young to have learned DOS (or similar variants)?

soi la, soi carré

 

[galaxy0815]

Thanks you all for your help. The script is working now as expected.

Thanks!