|
HOW TO: CONVERT CSV DELIMITED FILES TO XLS
See Chip Pearson's site for code that will read in a CSV file - you can
specify the delimiter: http://www.cpearson.com/excel/imptext.htm import/export text files You can loop through a directory with sName = dir("C:\Myfiles\*.csv") do while sName < "" ' call chips routine to open the file ' save the a file as xls sName = Dir() Loop -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Can someone guide me how to write a macro that Loops through 'N' .CSV files in a desktop folder and saves them as .XLS files. It is important to recognize the comma delimiter and throw the comma-separated individual values in different cells. If I use a normalExcel fileproperty then my xls file has all values in column A only. TIA Regards, -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
HOW TO: CONVERT CSV DELIMITED FILES TO XLS
Thx Tom !! I am able to parse Chip's code, but saving an empty file. Would
you be able to tell me what is wrong in this macro: 'My macro starts here Sub MAKEXLS() Dim sname As String Dim XLSBHAVCOPY As Workbook sname = Dir("D:\INVESTMENTS\CSV\*.csv") Do While sname < "" ImportTextFile sname, ";" ' save the a file as xls Set XLSBHAVCOPY = Workbooks.Add() With XLSBHAVCOPY .SaveAs FILENAME:="d:\investments\xls\" & sname .SaveAs FileFormat:=xlWorkbookNormal .Close 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 'Chip Pearson macro ends above TIA !!! Regards, BR "Tom Ogilvy" wrote: See Chip Pearson's site for code that will read in a CSV file - you can specify the delimiter: http://www.cpearson.com/excel/imptext.htm import/export text files You can loop through a directory with sName = dir("C:\Myfiles\*.csv") do while sName < "" ' call chips routine to open the file ' save the a file as xls sName = Dir() Loop -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Can someone guide me how to write a macro that Loops through 'N' .CSV files in a desktop folder and saves them as .XLS files. It is important to recognize the comma delimiter and throw the comma-separated individual values in different cells. If I use a normalExcel fileproperty then my xls file has all values in column A only. TIA Regards, -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
HOW TO: CONVERT CSV DELIMITED FILES TO XLS
Thx Tom !! I still face some problems (saves an empty file as csv). Please
tell me if this macro is correct: 'My macro starts here Sub MAKEXLS() Dim sname As String Dim XLSBHAVCOPY As Workbook sname = Dir("D:\INVESTMENTS\CSV\*.csv") Do While sname < "" 'Call Chip Pearson macro here ImportTextFile sname, ";" ' save the a file as xls Set XLSBHAVCOPY = Workbooks.Add() With XLSBHAVCOPY .SaveAs FILENAME:="d:\investments\xls\" & sname .SaveAs FileFormat:=xlWorkbookNormal .Close End With sname = Dir() Loop End Sub 'My macro ends above 'Chip Pearson code starts below 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 'Chip Pearson code ends above "Tom Ogilvy" wrote: See Chip Pearson's site for code that will read in a CSV file - you can specify the delimiter: http://www.cpearson.com/excel/imptext.htm import/export text files You can loop through a directory with sName = dir("C:\Myfiles\*.csv") do while sName < "" ' call chips routine to open the file ' save the a file as xls sName = Dir() Loop -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Can someone guide me how to write a macro that Loops through 'N' .CSV files in a desktop folder and saves them as .XLS files. It is important to recognize the comma delimiter and throw the comma-separated individual values in different cells. If I use a normalExcel fileproperty then my xls file has all values in column A only. TIA Regards, -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
HOW TO: CONVERT CSV DELIMITED FILES TO XLS
'My macro starts here
Sub MAKEXLS() Dim sname As String Dim XLSBHAVCOPY As Workbook sname = Dir("D:\INVESTMENTS\CSV\*.csv") Do While sname < "" ' open the workbook Set XLSBHAVCOPY = Workbooks.Add() ' open csv, write data to workbook ImportTextFile sname, ";" ' save the a file as xls With XLSBHAVCOPY .SaveAs FILENAME:="d:\investments\xls\" & 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 ... Thx Tom !! I am able to parse Chip's code, but saving an empty file. Would you be able to tell me what is wrong in this macro: 'My macro starts here Sub MAKEXLS() Dim sname As String Dim XLSBHAVCOPY As Workbook sname = Dir("D:\INVESTMENTS\CSV\*.csv") Do While sname < "" ImportTextFile sname, ";" ' save the a file as xls Set XLSBHAVCOPY = Workbooks.Add() With XLSBHAVCOPY .SaveAs FILENAME:="d:\investments\xls\" & sname .SaveAs FileFormat:=xlWorkbookNormal .Close 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 'Chip Pearson macro ends above TIA !!! Regards, BR "Tom Ogilvy" wrote: See Chip Pearson's site for code that will read in a CSV file - you can specify the delimiter: http://www.cpearson.com/excel/imptext.htm import/export text files You can loop through a directory with sName = dir("C:\Myfiles\*.csv") do while sName < "" ' call chips routine to open the file ' save the a file as xls sName = Dir() Loop -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Can someone guide me how to write a macro that Loops through 'N' .CSV files in a desktop folder and saves them as .XLS files. It is important to recognize the comma delimiter and throw the comma-separated individual values in different cells. If I use a normalExcel fileproperty then my xls file has all values in column A only. TIA Regards, -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
HOW TO: CONVERT CSV DELIMITED FILES TO XLS
Thx Tom!!! On Debug, I run into a macro On Error in the Pearson routine. I am working with this CSV file "http://www.nseindia.com/content/historical/DERIVATIVES/2004/SEP/fo14SEP2004bhav.csv" The file has 4475 rows of market-quotes for Futures, Options & other exchange traded derivative contracts. Would you have any suggestions, am I running into a memory allocation problem? TIA !! Regards, BR "Tom Ogilvy" wrote: 'My macro starts here Sub MAKEXLS() Dim sname As String Dim XLSBHAVCOPY As Workbook sname = Dir("D:\INVESTMENTS\CSV\*.csv") Do While sname < "" ' open the workbook Set XLSBHAVCOPY = Workbooks.Add() ' open csv, write data to workbook ImportTextFile sname, ";" ' save the a file as xls With XLSBHAVCOPY .SaveAs FILENAME:="d:\investments\xls\" & 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 ... Thx Tom !! I am able to parse Chip's code, but saving an empty file. Would you be able to tell me what is wrong in this macro: 'My macro starts here Sub MAKEXLS() Dim sname As String Dim XLSBHAVCOPY As Workbook sname = Dir("D:\INVESTMENTS\CSV\*.csv") Do While sname < "" ImportTextFile sname, ";" ' save the a file as xls Set XLSBHAVCOPY = Workbooks.Add() With XLSBHAVCOPY .SaveAs FILENAME:="d:\investments\xls\" & sname .SaveAs FileFormat:=xlWorkbookNormal .Close 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 'Chip Pearson macro ends above TIA !!! Regards, BR "Tom Ogilvy" wrote: See Chip Pearson's site for code that will read in a CSV file - you can specify the delimiter: http://www.cpearson.com/excel/imptext.htm import/export text files You can loop through a directory with sName = dir("C:\Myfiles\*.csv") do while sName < "" ' call chips routine to open the file ' save the a file as xls sName = Dir() Loop -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Can someone guide me how to write a macro that Loops through 'N' .CSV files in a desktop folder and saves them as .XLS files. It is important to recognize the comma delimiter and throw the comma-separated individual values in different cells. If I use a normalExcel fileproperty then my xls file has all values in column A only. TIA Regards, -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
HOW TO: CONVERT CSV DELIMITED FILES TO XLS
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 ... Thx Tom!!! On Debug, I run into a macro On Error in the Pearson routine. I am working with this CSV file "http://www.nseindia.com/content/historical/DERIVATIVES/2004/SEP/fo14SEP2004 bhav.csv" The file has 4475 rows of market-quotes for Futures, Options & other exchange traded derivative contracts. Would you have any suggestions, am I running into a memory allocation problem? TIA !! Regards, BR "Tom Ogilvy" wrote: 'My macro starts here Sub MAKEXLS() Dim sname As String Dim XLSBHAVCOPY As Workbook sname = Dir("D:\INVESTMENTS\CSV\*.csv") Do While sname < "" ' open the workbook Set XLSBHAVCOPY = Workbooks.Add() ' open csv, write data to workbook ImportTextFile sname, ";" ' save the a file as xls With XLSBHAVCOPY .SaveAs FILENAME:="d:\investments\xls\" & 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 ... Thx Tom !! I am able to parse Chip's code, but saving an empty file. Would you be able to tell me what is wrong in this macro: 'My macro starts here Sub MAKEXLS() Dim sname As String Dim XLSBHAVCOPY As Workbook sname = Dir("D:\INVESTMENTS\CSV\*.csv") Do While sname < "" ImportTextFile sname, ";" ' save the a file as xls Set XLSBHAVCOPY = Workbooks.Add() With XLSBHAVCOPY .SaveAs FILENAME:="d:\investments\xls\" & sname .SaveAs FileFormat:=xlWorkbookNormal .Close 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 'Chip Pearson macro ends above TIA !!! Regards, BR "Tom Ogilvy" wrote: See Chip Pearson's site for code that will read in a CSV file - you can specify the delimiter: http://www.cpearson.com/excel/imptext.htm import/export text files You can loop through a directory with sName = dir("C:\Myfiles\*.csv") do while sName < "" ' call chips routine to open the file ' save the a file as xls sName = Dir() Loop -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Can someone guide me how to write a macro that Loops through 'N' ..CSV files in a desktop folder and saves them as .XLS files. It is important to recognize the comma delimiter and throw the comma-separated individual values in different cells. If I use a normalExcel fileproperty then my xls file has all values in column A only. TIA Regards, -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
| All times are GMT +1. The time now is 11:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com