Hi,

I have a csv file which has a number of datasets (for several years) ,
each yearly datasets are separated by a blank row. Can anyone please
suggest an easy way to convert this dataset to an excel file for each
individual year?

thanks in advance

Nagendra

For the example subroutine below, I had the original csv file named
"multiYr.csv" and located it in the folder "C:\Test". The subroutine
creates muliple csv files from the sections separated by a blank line and
saves them with names "myTemp0.csv", "myTemp1.csv", myTemp2.csv, etc and
locates these new csv files in C:\Temp along with the larger original. For
each of these csv files, a new workbook is created. You would locate the
subroutine in a module, run it and then manually move through the windows
and do a 'File SaveAs' for each, giving it an appropriate name and
location.

__________________________________

Sub BreakApartCSV()

Const ForReading = 1
Const ForWriting = 2

Dim strLine As String
Dim strTemp As String
Dim txtArray()

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.OpenTextFile("C:\Test\multiYr.csv", ForReading)

strTemp = ""
i = 1


Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If strLine = "" Then
ReDim Preserve txtArray(i)
txtArray(i - 1) = strTemp
i = i + 1
strTemp = ""
Else
strTemp = strTemp & strLine
End If
Loop

txtArray(i - 1) = strTemp

For X = 0 To UBound(txtArray)
strPathTmp = "C:\Test\myTemp" & CStr(X) & ".csv"
Set tmpTextFile = FSO.CreateTextFile(strPathTmp, ForWriting)
tmpTextFile.Write txtArray(X)
tmpTextFile.Close
Workbooks.OpenText Filename:=strPathTmp, DataType:=xlDelimited
Next X

objFile.Close
Set FSO = Nothing

End Sub


___________________________________

Steve




"singh" wrote in message
ups.com...
Hi,

I have a csv file which has a number of datasets (for several years) ,
each yearly datasets are separated by a blank row. Can anyone please
suggest an easy way to convert this dataset to an excel file for each
individual year?

thanks in advance

Nagendra