INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

Add File name to a CSV header

Add File name to a CSV header

(OP)
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

RE: Add File name to a CSV header

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?

CODE -->

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.
'http://windowssecrets.com/forums/showthread.php/111019-Extract-a-variable-from-a-CSV-file-%28VBA-Word-2003-SP2%29
'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 

RE: Add File name to a CSV header

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.

RE: Add File name to a CSV header

(OP)
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

RE: Add File name to a CSV header

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

CODE

@echo off
for %%f in (*.csv) do (
  echo Processing file: %%f

  rem adding file name to end of header (i.e. first line)
  rem output goes to *.processed
  sed "1 s/$/ >>filename: %%f/" %%f > %%f.processed

  rem removing original file
  del %%f

  rem renaming temporary file to original file
  ren %%f.processed %%f
)
echo Done. 


I have created 2 example files:

file_01.csv

CODE

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

file_02.csv

CODE

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

CODE

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

CODE

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

CODE

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close