VBA for reading and writing to csv file

[xyz0036]

Hello,

I'm trying to do the following task:

Input files
--> csv file 1 with 360 columns of cashflow
--> csv file 2 with corresponding rates for the 360 columns

Task
--> calculate present value based on the cashflow and rate file
(i.e. file 1 column 1 * file 2 column 2 + file 1 column 2 * file 2 column 2 + ... file 1 column 360 * file 2 column 360)

Since 360 columns exceeds the max number of columns in excel. Is there anyway Excel VBA can read the csv files without opening it and loop through the files 360 times to get the present value?

Many thanks!!

 

[boblarson]

1. No, a loop is what you'll need because Access tables are limited to 255 fields as well.

2. If you have Excel 2007 or 2010 then you would not have this problem as it can have up to 16,384 columns.

 

[xyz0036]

I'm using Excel 2003 now.

 

[boblarson]

I'm using Excel 2003 now.

I figured as much. So you would need to use VBA to loop through to read the file and also to output it. Not sure what you will do for a table structure as you won't have enough fields available to use in a single table.

 

[xyz0036]

I know I'd have to use a loop, but I'm stuck on the syntax since I need to open a csv file that has max columns of 360.

But my output will actually be just one column, since it's the present value at time 0.

 

[boblarson]

What code do you have currently?

 

[xyz0036]

Here is my code so far:

Sub CalcPV()
Dim cn, rs As Object
Dim strcon As String
Dim strsql As String
Dim inputPath, inputCF, inputRate, outputPath, outputFile As String
inputPath = Sheets("Sheet1").Cells(3, 4)
inputCF = Sheets("Sheet1").Cells(4, 4)
inputRate = Sheets("Sheet1").Cells(5, 4)
outputPath = Sheets("Sheet1").Cells(7, 4)
outputFile = Sheets("Sheet1").Cells(8, 4)

Set cn = CreateObject("ADODB.Connection")

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & inputPath & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"","
cn.Open strcon
strsql = "SELECT * FROM " & inputCF

Set rs = CreateObject("ADODB.RECORDSET")
rs.Open strsql, cn
'do loop
rs.Close
End Sub

========

Right now I'm having trouble just reading the csv file. The macro has a runtime error.

 

[xyz0036]

I revised my code:

================
Sub CalcPV()
Dim cn, rs As Object
Dim inputPath, inputCF, inputRate, outputPath, outputFile As String
inputPath = Sheets("Sheet1").Cells(3, 4)
inputCF = Sheets("Sheet1").Cells(4, 4)
inputRate = Sheets("Sheet1").Cells(5, 4)
outputPath = Sheets("Sheet1").Cells(7, 4)
outputFile = Sheets("Sheet1").Cells(8, 4)
'Open an ADO connection to the folder specified
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & inputPath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""

'Open the csv file
Set rs = CreateObject("ADODB.RECORDSET")
rs.Open "SELECT * FROM " & inputRate, cn
i = 1
rs.movefirst
While Not rs.EOF
For j = 0 To 255 '360
Sheets("Sheet1").Cells(i, 6) = rs.fields(j).Value
i = i + 1
Next j
rs.movenext
Wend

End Sub

================

I can successfully open the csv file, however, the program doesn't let me read past column 256 (see code highlighted in red). It looks like csv file is still opened through Excel and restricted to the max number of columns. Can someone help me on this??? I need to read the file till column 361.

Thanks.

 

[boblarson]

If you are trying to use Excel for this you would need to start at column 1 all over again on another row once you get to the 255th column. I hope you don't think you'll be able to save this Excel file as a csv file. You would essentially need to use the Open as # method and do the write line / print line (can't remember off the top of my head which puts quotes around strings) and then pull your data in one line at a time, process that row and send it out to the second file and then loop around and do the next line.

Do a Google search on the syntax below. I would help write it but I'm too busy to get that into something else right now.

Open FileNameHere For Input As #1

Open FileName2Here for Output As #2

 

[xyz0036]

Thanks for the reply, boblarson. I don't know if I understand what you mean.

I'm thinking somehow splitting the source into two files. 1st file with column 1 to column 256, 2nd file with column 257 to 360. Is there anyway you can specify the starting column in the source file to read from? i.e. use the following code, but replace * with column 257??

rs.Open "SELECT * FROM " & inputRate, cn

 

[boblarson]

Is there anyway you can specify the starting column in the source file to read from? i.e. use the following code, but replace * with column 257??

Not that I know of.

Here's an example of what I mean.