| 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro convert CSV -to- XLS without parsing line by line
in the code
Set XLSBHAVCOPY = Workbooks.Add() ' open csv, write data to workbook ImportTextFile sPath & sname, "," so the new workbook XLSBHAVCOPY should be the activeworkbook and Chip's code writes to the activeworkbook/activesheet. I don't see how this wouldn't work unless you have some kind of event code that makes MacroProcessor.xls the activeworkbook after you open the new workbook. Obviously I can't see what happens. Can you see anything that makes the new workbook not the activeworkbook before you call ImportTextFile? -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro convert CSV -to- XLS without parsing line by line
Tom, There are two problems he
1. The destination file is still saving as a CSV file-format. 2. The code below: Cells(RowNdx, ColNdx).Value = TempVal in Pearson's routing writes to the file wherein the macro resides (macroprocessor.xls) My debug window shows "Book1" as the ActiveWorkbook.Name, however Cells(..,..) is writing elsewhere! Any thoughts? TIA!! Regards, BR "Tom Ogilvy" wrote: in the code Set XLSBHAVCOPY = Workbooks.Add() ' open csv, write data to workbook ImportTextFile sPath & sname, "," so the new workbook XLSBHAVCOPY should be the activeworkbook and Chip's code writes to the activeworkbook/activesheet. I don't see how this wouldn't work unless you have some kind of event code that makes MacroProcessor.xls the activeworkbook after you open the new workbook. Obviously I can't see what happens. Can you see anything that makes the new workbook not the activeworkbook before you call ImportTextFile? -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... 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 |
| 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 | |||
Linear Mode
