Add File name to a CSV header

[IanWaterman]

I am using another Application (Crystal Reports) to generate a CSV file which has a header comprising a String of data 52 characters long. Crystal Reports Server then exports file with an automatically generated file name which includes
DateTime in seconds when file is created.

Due to timing issues I cannot replicate that exact time inside Crystal reports
Ie SQL is executed at 14:10:57 but file may be exported at 14:11:23
Header needs the CSV file name included.
File name will look something like

FRNEXCContactDetails20161104145857.csv
System creates 8 such files with different name eg ContactDetails, AddressDetails all with different date times each day

Does anyone one have any vbs code I can run against all 8 files which takes the file name generated and appends to the end of the header string.
On completion copies the files to another folder so that when next batch comes in next day only those are updated.

The other option if easier is to create file name in crystal and repeat in Footer2 and then use vbs to save file to another folder with new name and delete existing files.

Thanks in advance for your help

Ian

 

[sxschech]

This may be the opposite of what you want as this code will search for a value in the file and append it to the filename. I'm using it in access vba, maybe it is translatable to vbscript?

Function ExtractFieldFromCSV(strCSV As String, strField As String, AlternateDelim As String)
'Use this to get the EventCode from inside the csv in order
'to rename the csv file to include the eventcode in its name
'Depending on csv file may need to include extra quotes for
'strField so that it can find the string within it.
'[URL unfurl="true"]http://windowssecrets.com/forums/showthread.php/111019-Extract-a-variable-from-a-CSV-file-%28VBA-Word-2003-SP2%29[/URL]
'20160805
Dim f As Integer
Dim strLine As String
Dim arrLine1() As String
Dim arrLine2() As String
Dim i As Integer

On Error GoTo ErrHandler

' Open file
f = FreeFile
Open strCSV For Input As #f

' Get first line
Line Input #f, strLine
' Split
arrLine1 = Split(strLine, ",") ' Application.International(wdListSeparator)

' Find fieldname in header
For i = 0 To UBound(arrLine1)
If arrLine1(i) = strField Then
Exit For
End If
Next i

If i > UBound(arrLine1) Then
    MsgBox "Field " & strField & " not found in header.", vbExclamation
    stsql = "INSERT INTO tblProblemCSV ( FileName ) " & _
            "SELECT '" & strCSV & "' AS Expr1;"
    DoCmd.RunSQL stsql

Exit Function
End If

' Get second line
Line Input #f, strLine
' Split
'This line does not handle commas within quotes
'arrLine2 = Split(strLine, ",") ' Application.International(wdListSeparator)
'Try this instead
arrLine2 = splitLine2(strLine, AlternateDelim)

' Get element corresponding to field name
ExtractFieldFromCSV = arrLine2(i)

ExitHandler:
On Error Resume Next
Close #f
Exit Function

ErrHandler:
MsgBox Err.Description, vbExclamation
    stsql = "INSERT INTO tblProblemCSV ( FileName, ImportDate ) " & _
            "SELECT '" & strCSV & "-Err' AS FileName, now() AS ImportDate;"
    DoCmd.RunSQL stsql
End Function

 

[guitarzan]

What have you tried and where are you stuck? If you are new to vbscript, here as some methods that can achieve what you want:

You can traverse the csv files, and also retrieve their file names using GetFolder.
The contents of each file can be opened with the FileSystem Object's OpenTextFile and ReadAll methods to get the file contents into one variable.
Create an array of lines using the Split function as sxschech's sample shows, then append the text you want to the first element of that array.
Use the Join function to combine into a single variable again.
Use the Write to write that variable into the file, overwriting the original.
Use the MoveFile to move the file to another folder.

 

[IanWaterman]

Thanks for the tips

I am a complete novice with VBS.

I will take a look at the functions listed. Failing that I will need to find someone to set it up for me.

Thanks

Ian

 

[mikrom]

IMO it's a simple task to be done with a little shell script - no need for a bigger programming.
If you are interested, first download the standard unix utility called sed.
The port for windows is for example here:

http://gnuwin32.sourceforge.net/packages/sed.htm

Then create a batch script like this:

add_header_all.bat

@[COLOR=#008080]echo[/color][COLOR=#804040][b] off[/b][/color]
[COLOR=#804040][b]for[/b][/color] [COLOR=#6a5acd]%%[/color]f in (*.csv) do (
  [COLOR=#008080]echo[/color][COLOR=#ff00ff] Processing file: [/color][COLOR=#6a5acd]%%[/color][COLOR=#ff00ff]f[/color]

  [COLOR=#008080]rem[/color][COLOR=#0000ff] adding file name to end of header (i.e. first line)[/color]
  [COLOR=#008080]rem[/color][COLOR=#0000ff] output goes to *.processed[/color]
  sed [COLOR=#ff00ff]"1 s/$/ >>filename: [/color][COLOR=#6a5acd]%%[/color][COLOR=#ff00ff]f/"[/color] [COLOR=#6a5acd]%%[/color]f > [COLOR=#6a5acd]%%[/color]f.processed

  [COLOR=#008080]rem[/color][COLOR=#0000ff] removing original file[/color]
  [COLOR=#008080]del[/color] [COLOR=#6a5acd]%%[/color]f

  [COLOR=#008080]rem[/color][COLOR=#0000ff] renaming temporary file to original file[/color]
  [COLOR=#008080]ren[/color] [COLOR=#6a5acd]%%[/color]f.processed [COLOR=#6a5acd]%%[/color]f
)
[COLOR=#008080]echo[/color][COLOR=#ff00ff] Done.[/color]

I have created 2 example files:

file_01.csv

This is header of file 01
data line 01 of file 01
data line 02 of file 01

file_02.csv

This is header of file 02
data line 01 of file 02
data line 02 of file 02
data line 03 of file 02

Now when I run the script I posted above

C:\_mikrom\Work\csvfiles>add_header_all.bat
Processing file: file_01.csv
Processing file: file_02.csv
Done.

I get the result:

file_01.csv

This is header of file 01 >>filename: file_01.csv
data line 01 of file 01
data line 02 of file 01

file_02.csv

This is header of file 02 >>filename: file_02.csv
data line 01 of file 02
data line 02 of file 02
data line 03 of file 02