joycesolomon
New Member
- Joined
- Aug 2, 2011
- Messages
- 48
Hi,
Need some help.
Do anyone have a sample vb script to run to strip out the first column and the 7th column from a source csv (huge file) to the dest csv file without opening the source file.
I found one script and tried to customize it, and have few errors.
It opens the test data, but nothing is written to it. My source csvs are as big as 25GB
Thank you in advance.
Need some help.
Do anyone have a sample vb script to run to strip out the first column and the 7th column from a source csv (huge file) to the dest csv file without opening the source file.
I found one script and tried to customize it, and have few errors.
Code:
Const xlFilterCopy = 2
Const xlUp = -4162
Const xlDown = -4121
strPathSrc = "C:\Users\public\Amber" ' Source files folder
strMaskSrc = "uni_*.csv" ' Source files filter mask
iSheetSrc = 1 ' Sourse sheet index or name
iColSrc = 7 ' Source column index, e. g. 3 for "C"
strPathDst = "C:\Users\public\Amber\test.xls" ' Destination file
iColDst = 1 ' Destination column index
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkBookDst = objExcel.Workbooks.Open(strPathDst)
Set objSheetTmp = objWorkBookDst.Worksheets.Add
objSheetTmp.Cells(1, iColDst).Value = "TempHeader"
Set objShellApp = CreateObject("Shell.Application")
Set objFolder = objShellApp.NameSpace(strPathSrc)
Set objItems = objFolder.Items()
objItems.Filter 64 + 128, strMaskSrc
objExcel.DisplayAlerts = False
For Each objItem In objItems
Set objWorkBookSrc = objExcel.Workbooks.Open(objItem.Path)
Set objSheetSrc = objWorkBookSrc.Sheets(iSheetSrc)
objSheetSrc.Cells(1, iColSrc).Insert xlDown
objSheetSrc.Cells(1, iColSrc).Value = "TempHeader"
Set objRangeSrc = GetRange(iColSrc, objSheetSrc)
If objRangeSrc.Cells.Count > 1 then
nNextRow = GetRange(iColDst, objSheetTmp).Rows.Count + 1
objRangeSrc.AdvancedFilter xlFilterCopy, , objSheetTmp.Cells(nNextRow, iColDst), True
objSheetTmp.Cells(nNextRow, iColDst).Delete xlUp
Set objRangeTmp = GetRange(iColDst, objSheetTmp)
Set objSheetDst = objWorkBookDst.Worksheets.Add
objRangeTmp.AdvancedFilter xlFilterCopy, , objSheetDst.Cells(1, iColDst), True
objSheetTmp.Delete
Set objSheetTmp = objSheetDst
End If
objWorkBookSrc.Close
Next
objSheetTmp.Cells(1, iColDst).Delete xlUp
objExcel.DisplayAlerts = True
Function GetRange(iColumn, objSheet)
With objSheet
Set GetRange = .Range(.Cells(1, iColumn), .Cells(.Cells(.Cells.Rows.Count, iColumn).End(xlUp).Row, iColumn))
End With
End Function
It opens the test data, but nothing is written to it. My source csvs are as big as 25GB
Thank you in advance.