I have about 200 csv files in a directory.

For example, C:\myfolder\amd.csx, C:\myfoler\bux.csx, C:\myfolder\abc.csx ....

The csv files have the following format:

AMD, 12-Apr-00, 1.4, 1.3, 1.22, 1.5, 627044
AMD, 13-Apr-00, 1.33, 1.23, 1.45, 1.33, 667788
AMD..etc..

First value is the same as the file name, second value is a date
(dd-mmm-yy), the rest are numbers.

Now I have a .txt file. The format is:

AMD, 091012, 1.88, 1.67, 1.45, 1.23, 345678
BUX, 091012, 11, 11.35, 10.9, 11.2, 627044
ABC, 091012, 10.86, 10.89, 10.75, 10.75, 476009
etc.

The first value is the name of the csv file. The second value is the date
(yymmdd). The rest are numbers.

I get one such txt file per work day.

I want to open the text file, then open each corresponding csv (if found).
The first value of each line tells me which csv file to open. Now update the
csv file with the info in the .txt file. ie. it appends the relevant line
from the txt file to the csv file.

Thanks in advance!

Lightly tested. When you test it, copy a few of the .csv files to a test folder
and run against that.

If it blows up, you don't want to destroy your real data.

Option Explicit
Sub testme()
Dim TxtWks As Worksheet
Dim CSVWks As Worksheet
Dim TxtFileName As String

Dim myCell As Range
Dim myRng As Range

Dim myPath As String
Dim DestCell As Range

myPath = "C:\myfolder\"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

'make sure it has a .txt extension
TxtFileName = "C:\myfolder\textfilenamehere.txt"

Workbooks.OpenText Filename:=TxtFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, xlGeneralFormat), _
Array(2, xlYMDFormat), _
Array(3, xlGeneralFormat), _
Array(4, xlGeneralFormat), _
Array(5, xlGeneralFormat), _
Array(6, xlGeneralFormat), _
Array(7, xlGeneralFormat))

Set TxtWks = ActiveSheet

With TxtWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Set CSVWks = Nothing
On Error Resume Next
Set CSVWks = Workbooks.Open _
(Filename:=myCell.Value & ".csv").Worksheets(1)
On Error GoTo 0

If CSVWks Is Nothing Then
MsgBox "No CSV file named: " & myCell.Value & ".csv"
Else
With CSVWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
'copy the entire row
myCell.EntireRow.Copy _
Destination:=DestCell
'dates are funny, so do extra work
With DestCell.Offset(0, 1)
.NumberFormat = "dd-mmm-yy"
.Offset(0, 1).Value2 = myCell.Offset(0, 1).Value2
End With
'save the CSV file
.Parent.Close savechanges:=True
End With
End If
Next myCell

TxtWks.Parent.Close savechanges:=False

End Sub

It's important that the text file has an extension of .txt. If it has an
extension of .csv, then VBA can't control how that date field comes in.

The CSV files are fine (I think). The date field is only slightly ambiguous. I
would have used a 4 digit year -- just to make sure there's never a doubt.

Diana wrote:

I have about 200 csv files in a directory.

For example, C:\myfolder\amd.csx, C:\myfoler\bux.csx, C:\myfolder\abc.csx ....

The csv files have the following format:

AMD, 12-Apr-00, 1.4, 1.3, 1.22, 1.5, 627044
AMD, 13-Apr-00, 1.33, 1.23, 1.45, 1.33, 667788
AMD..etc..

First value is the same as the file name, second value is a date
(dd-mmm-yy), the rest are numbers.

Now I have a .txt file. The format is:

AMD, 091012, 1.88, 1.67, 1.45, 1.23, 345678
BUX, 091012, 11, 11.35, 10.9, 11.2, 627044
ABC, 091012, 10.86, 10.89, 10.75, 10.75, 476009
etc.

The first value is the name of the csv file. The second value is the date
(yymmdd). The rest are numbers.

I get one such txt file per work day.

I want to open the text file, then open each corresponding csv (if found).
The first value of each line tells me which csv file to open. Now update the
csv file with the info in the .txt file. ie. it appends the relevant line
from the txt file to the csv file.

Thanks in advance!



--

Dave Peterson

I see Dave got here before me, but since I worked this up, I'll post it
anyhow and perhaps you'll try both and use the one you like the best.
Prompts for the "daily file", has default folder defined in it, but permits
browsing for a different one.

I recommend you set up a test folder with copies of all of thos 200 csv
files in it to test with.

Sub UpdateFromDailyFile()
'custom code by
'JLatham, Excel MVP 2006-2010
'contact at (remove spaces)
'Help From @ JLatham Site.com

'you can change this path to set
'the default to the individual .csv files
Const defaultPathToReports = "C:\MyFolder"
Const fileType = ".csv"
'separator character within the .csv files
Const sepChar = ","

Dim pathToReports As String
Dim myDailyFile As Variant
Dim dBuff As Integer
Dim oneRecord As String
Dim reportFile As String
Dim rBuff As Integer
Dim p1 As Integer
Dim p2 As Integer
Dim monValue As Integer
Dim newDate As String

'select the daily file to work with
myDailyFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
If myDailyFile = "False" Then
Exit Sub
End If
pathToReports = defaultPathToReports

'************
'if the report .csv files are always and forever
'in the default path then you can delete this
'entire If ... End If block
If MsgBox("Default file for .csv files is:" & vbCrLf _
& pathToReports & vbCrLf _
& "Use this path?", vbYesNo + vbQuestion, _
"Use Default Location?") < vbYes Then
' choose another path
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Title = "Select .csv folder"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "No folder selected. Quitting.", vbOKOnly, "User Cancelled"
Exit Sub
End If
pathToReports = .SelectedItems(1)
End With
End If
'************

If Right(pathToReports, 1) < Application.PathSeparator Then
pathToReports = pathToReports & Application.PathSeparator
End If
'set up to open and process the daily file
dBuff = FreeFile()
Open myDailyFile For Input As #dBuff

Do While Not EOF(dBuff)
'read line from daily file
'as: AMD, 091012, 1.88, 1.67, 1.45, 1.23, 345678
Line Input #dBuff, oneRecord
'pick off the id for the record file
reportFile = Left(oneRecord, InStr(oneRecord, sepChar) - 1)
'does the report file exist?
If Dir$(pathToReports & reportFile & fileType) < "" Then
'yes it does, open it and append a record to it
'but have to build new date to stick into it
p1 = InStr(oneRecord, sepChar)
p2 = InStr(p1 + 1, oneRecord, sepChar)
newDate = Mid(oneRecord, p1 + 1, p2 - p1 - 1)
newDate = Format(DateSerial(Val(Left(newDate, 2)), Val(Mid(newDate, 3,
2)), Val(Right(newDate, 2))), "dd-mmm-yy")
newDate = " " & newDate ' needs space in front of it
oneRecord = Left(oneRecord, p1) & newDate & Right(oneRecord,
Len(oneRecord) - p2 + 1)
rBuff = FreeFile()
Open pathToReports & reportFile & fileType For Append As #rBuff
Print #rBuff, oneRecord
Close #rBuff
Else
'could report unprocessed entries
'in this section
End If
Loop ' end of loop through the daily file
Close #dBuff
MsgBox "Task Completed", vbOKOnly + vbInformation, "Job Done"
End Sub


"Diana" wrote:

I have about 200 csv files in a directory.

For example, C:\myfolder\amd.csx, C:\myfoler\bux.csx, C:\myfolder\abc.csx ....

The csv files have the following format:

AMD, 12-Apr-00, 1.4, 1.3, 1.22, 1.5, 627044
AMD, 13-Apr-00, 1.33, 1.23, 1.45, 1.33, 667788
AMD..etc..

First value is the same as the file name, second value is a date
(dd-mmm-yy), the rest are numbers.

Now I have a .txt file. The format is:

AMD, 091012, 1.88, 1.67, 1.45, 1.23, 345678
BUX, 091012, 11, 11.35, 10.9, 11.2, 627044
ABC, 091012, 10.86, 10.89, 10.75, 10.75, 476009
etc.

The first value is the name of the csv file. The second value is the date
(yymmdd). The rest are numbers.

I get one such txt file per work day.

I want to open the text file, then open each corresponding csv (if found).
The first value of each line tells me which csv file to open. Now update the
csv file with the info in the .txt file. ie. it appends the relevant line
from the txt file to the csv file.

Thanks in advance!