Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro convert CSV -to- XLS without parsing line by line
Is there anyway I can import a "," delimited CSV file as a .XLS Workbook without parsing the CSV line-by-line? My CSV is a derivatives trading-data dump & has 4775 records per day with 14 columns of text, numeric & currency fields. I need to run this macro on 60 such CSV files everyday for 60day Moving Average securities analysis. TIA !! Regards, BR -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro convert CSV -to- XLS without parsing line by line
You macro needs a little more work. This worked fine for me:
'My macro starts here Sub MAKEXLS() Dim sname As String Dim sPath As String Dim XLSBHAVCOPY As Workbook sPath = "D:\INVESTMENTS\CSV\" sPath1 = "D:\INVESTMENTS\xls\" If Right(sPath, 1) < "\" Then sPath = sPath & "\" End If If Right(sPath1, 1) < "\" Then sPath = sPath1 & "\" End If sname = Dir(sPath & "*.csv") Do While sname < "" ' open the workbook Set XLSBHAVCOPY = Workbooks.Add() ' open csv, write data to workbook ImportTextFile sPath & sname, "," ' save the a file as xls sname = Left(sname, Len(sname) - 4) With XLSBHAVCOPY .SaveAs Filename:=sPath1 & sname, _ FileFormat:=xlWorkbookNormal .Close Savechanges:=True ' already saved End With sname = Dir() Loop End Sub 'my Macro ends above 'Chip Pearson code starts here Public Sub ImportTextFile(FName As String, Sep As String) Dim RowNdx As Integer Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Application.ScreenUpdating = False 'On Error GoTo EndMacro: SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.Row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #1 End Sub -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Is there anyway I can import a "," delimited CSV file as a .XLS Workbook without parsing the CSV line-by-line? My CSV is a derivatives trading-data dump & has 4775 records per day with 14 columns of text, numeric & currency fields. I need to run this macro on 60 such CSV files everyday for 60day Moving Average securities analysis. TIA !! Regards, BR -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro convert CSV -to- XLS without parsing line by line
thx so much Tom!! This almost solves the problem, a little more tweaking is
required. I copied this code to a file called MacroProcessor.xls The code copies the contents of the csv to Sheet1 in MacroProcessor.xls However, the resultant Destination File that is created from the CSV file name (for e.g. fo10Aug2004bhav.xls from fo10Aug2004bhav.csv) is empty. Would you be able to advise me? TIA!! Regards, BR "Tom Ogilvy" wrote: You macro needs a little more work. This worked fine for me: 'My macro starts here Sub MAKEXLS() Dim sname As String Dim sPath As String Dim XLSBHAVCOPY As Workbook sPath = "D:\INVESTMENTS\CSV\" sPath1 = "D:\INVESTMENTS\xls\" If Right(sPath, 1) < "\" Then sPath = sPath & "\" End If If Right(sPath1, 1) < "\" Then sPath = sPath1 & "\" End If sname = Dir(sPath & "*.csv") Do While sname < "" ' open the workbook Set XLSBHAVCOPY = Workbooks.Add() ' open csv, write data to workbook ImportTextFile sPath & sname, "," ' save the a file as xls sname = Left(sname, Len(sname) - 4) With XLSBHAVCOPY .SaveAs Filename:=sPath1 & sname, _ FileFormat:=xlWorkbookNormal .Close Savechanges:=True ' already saved End With sname = Dir() Loop End Sub 'my Macro ends above 'Chip Pearson code starts here Public Sub ImportTextFile(FName As String, Sep As String) Dim RowNdx As Integer Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer Application.ScreenUpdating = False 'On Error GoTo EndMacro: SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.Row Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, WholeLine If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #1 End Sub -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Is there anyway I can import a "," delimited CSV file as a .XLS Workbook without parsing the CSV line-by-line? My CSV is a derivatives trading-data dump & has 4775 records per day with 14 columns of text, numeric & currency fields. I need to run this macro on 60 such CSV files everyday for 60day Moving Average securities analysis. TIA !! Regards, BR -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro convert CSV -to- XLS without parsing line by line
there is a very simple solution
But may be you know it and for some reason don't want it replace the delimiter symbol "," with ";" Everything will be then fine without any macro Your Sidibo -------- Message sent via www.excelforums.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to put series name next to individual line in line grap | Charts and Charting in Excel | |||
Parsing line breaks into separate cells | Excel Discussion (Misc queries) | |||
How do I convert a line chart to a vertical line chart in Excel | Charts and Charting in Excel | |||
How to convert a dotted line to a solid line in a line graph | Charts and Charting in Excel | |||
coloring overy other line without doing so line by line | Excel Worksheet Functions |