Import CSV to 1 sheet
I'm not clear on whether the data contained in the files can be dumped
straight into a worksheet or requires some effort to parse / calculate
the required data - this would affect whether to use WOrkbooks.Open or
Open #.
Here's something that should start you in the right direction. I'm
assuming the files are formatted as one line something like:
NAME, DATE, RATE
Read tgrough my code carefully to understand what it's doing - in
particular, you might not like that I delete the original file. So
please check.
Sub DailyFIleProcess()
Dim wb As Workbook
Dim sh As Worksheet
Dim F As Integer
Dim myFile As String
Dim myData As String
Dim mySplitData() As String
Dim iRow As Integer
Const myFolder As String = "\\server\myfolder\"
Const myFolderBackup As String = "\\server\myfolder\bkup\"
'make a new workbook and worksheet
Set wb = Workbooks.Add
Set sh = wb.Sheets(1)
'Write in the headers
iRow = 1
With sh
.Cells(iRow, 1) = "NAME"
.Cells(iRow, 2) = "DATE"
.Cells(iRow, 3) = "RATE"
End With
'Go through the folder until all the files
'have been processed.
myFile = Dir(myFolder & "*.csv")
Do While myFile < ""
'Get the data from the file
F = FreeFile
Open myFolder & myFile For Input As #F
Line Input #F, myData
Close #F
'split the data into an array on the comma delimiter
'(XL2000 and above only)
mySplitData = Split(myData, ",")
'write the data onto the worksheet
iRow = iRow + 1
With sh
.Cells(iRow, 1) = myData(0)
.Cells(iRow, 2) = myData(1)
.Cells(iRow, 3) = myData(2)
End With
'copy file to back up folder
FileCopy myFolder & myFile, myFolderBackup & myFile _
& "_" & Format(Now, "yyyy.mm.dd_hh.nn.ss")
'delete original file
Kill myFolder & myFile
'Check for another file
myFile = Dir(myFolder & "*.csv")
Loop
End Sub
JR wrote:
Hey guys, here's my situation...
We have about 15 individual CSV files come in daily. I would like to create
a macro to go through each of those files, however many they may be, and
separate the information into premade columns. As an example, I would like a
Name header, a Date header, and a Rate header. When I run the macro, I would
need it to shoot through maybe a dozen CSV files, take the name, date, and
rate information, and place it into the respective columns. It would then be
1 sheet, 12 rows long (not counting headers), with the individual
information.
I'm very new to macros. I've made a few attempts at piecing together macros
from other peoples' issues, but have had no luck. Any help would soo
appreciated!
Thanks,
JR
|