Writing records to CSV file

[Ken Warthen]

I have an Excel 2003 workbook in which users select a number of products,
enter pricing, margins, etc. When the user is finished I need to create a
csv file with every permutations of their selections and entries. I
originally was writing the records to a worksheet and then when finished I
would export the worksheet to a CSV file. My code worked fine until it was
discovered that frequently the number of records could run into the hundreds
of thousands, exceeding Excel 2003's 65k limit.

I need to rewrite my code to write records directly to an external CSV file.
If anyone has any experience in this area that they could share, I would be
very appreciative.

Ken

 

[JP]

Not knowing anything about your workbook or the current code you're
using, can you use the Workbook.SaveAs method and specify "xlCSV" as
the FileFormat parameter?

--JP

 

[Jennifer]

I have an Excel 2003 workbook in which users select a number of products,
enter pricing, margins, etc.  When the user is finished I need to create a
csv file with every permutations of their selections and entries.  I
originally was writing the records to a worksheet and then when finished I
would export the worksheet to a CSV file.  My code worked fine until itwas
discovered that frequently the number of records could run into the hundreds
of thousands, exceeding Excel 2003's 65k limit.

I need to rewrite my code to write records directly to an external CSV file.
 If anyone has any experience in this area that they could share, I would be
very appreciative.

Ken

You could use the File System Object. You can create a text file and
write the line - repeating the write part however many times is
needed.

Not having seen your code, below is very basic code - some variable
declarations and an example of writing a line to a file. I would
suspect you need to set it up in a loop of some kind, but without
seeing your code I don't know. Hope this helps.

Dim FSO
Dim sFileName
Dim Fil
Dim sLine as String
Dim sCol1 as String
Dim sCol2 as String
Dim sCol3 as String
Dim sCol4 as String

Set FSO = CreateObject("Scripting.FileSystemObject")
sFileName = "C:\File.CSV"
Set Fil = fso.OpenTextFile(sFileName, 2)
sLine = sCol1 & "," sCol2 & "," & sCol3 & "," & sCol4 & vbCrLf
Fil.WriteLine sLine

Fil.Close
Set FSO = Nothing

 

[Ken Warthen]

JP,

What I've attempted to do so far is create a new workbook in the xlCSV file
format and then write records directly to that file, rather than write
records to a worksheet and export the worksheet to an xlCSV file. The latter
worked fine, until I ran into the 65k row limit in Excel 2003.

I was hoping I might find someone who has dealt with a similar issue. I'm
concerned about performance issues as some files might end up with a half
million or more records.

Ken

 

[Ken Warthen]

Jennifer,

I actually need to use the xlCSV file format as one of the endusers will be
reviewing the file in Excel before its data is uploaded to a mainframe
application.

Ken

 

[Just Another Yahoo!]

Maybe I'm missing something (and please correct me if so) but if the workbook
is too big to export as .csv, then won't it be too big to import into Excel
for the reviewer as well, regardless of how you save it (as either a workbook
or .csv)?

 

[Dave Peterson]

Maybe the recipient has xl2007 (with 1MB rows).

Or maybe the .csv files will be split into smaller pieces some how????

 

[Ken Warthen]

Dave,

The user will be using Excel 2003. If I can write the records into an xlCSV
file, I thought the row limitation will not apply.

Ken

 

[Dave Peterson]

You can write a file as large as you want (limited by harddrive space???).

But the question is how will the recipient use that file. If it's too large for
you to open in xl2003, then it'll be too big for the recipient, too.

Depending on what you want, you could write to multiple .csv files and limit
their line count to less than 64k.

Then the recipient could open each .csv file into a new worksheet or new
worksheet in a new workbook.

 

[Ken Warthen]

Dave,

A single CSV file is the requirement. Again, I thought since the file
format would be xlCSV it could still be opened in Excel 2003, but if not, it
could be opened with a text editor. In any case, I'm kind of stuck creating
a single CSV file with all the records, be it a few thousand or several
hundred thousand.

Ken

 

[Dave Peterson]

Excel will open the file ok--but you'll see a warning. Excel will truncate the
data at 64k rows. There's no room to read the entire .csv file.

Dave,

A single CSV file is the requirement. Again, I thought since the file
format would be xlCSV it could still be opened in Excel 2003, but if not, it
could be opened with a text editor. In any case, I'm kind of stuck creating
a single CSV file with all the records, be it a few thousand or several
hundred thousand.

Ken