Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
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 On Sep 21, 2:06*pm, Ken Warthen wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
On Sep 21, 1:06*pm, Ken Warthen
wrote: 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
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 "JP" wrote: 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 On Sep 21, 2:06 pm, Ken Warthen wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
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 "Jennifer" wrote: On Sep 21, 1:06 pm, Ken Warthen wrote: 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
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)? -- Toby Erkson Excel 2003, WinXP "Ken Warthen" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
Maybe the recipient has xl2007 (with 1MB rows).
Or maybe the .csv files will be split into smaller pieces some how???? "Just Another Yahoo!" wrote: 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)? -- Toby Erkson Excel 2003, WinXP "Ken Warthen" wrote in message ... 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
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" wrote: Maybe the recipient has xl2007 (with 1MB rows). Or maybe the .csv files will be split into smaller pieces some how???? "Just Another Yahoo!" wrote: 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)? -- Toby Erkson Excel 2003, WinXP "Ken Warthen" wrote in message ... 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
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 wrote: 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" wrote: Maybe the recipient has xl2007 (with 1MB rows). Or maybe the .csv files will be split into smaller pieces some how???? "Just Another Yahoo!" wrote: 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)? -- Toby Erkson Excel 2003, WinXP "Ken Warthen" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to 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 "Dave Peterson" wrote: 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 wrote: 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" wrote: Maybe the recipient has xl2007 (with 1MB rows). Or maybe the .csv files will be split into smaller pieces some how???? "Just Another Yahoo!" wrote: 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)? -- Toby Erkson Excel 2003, WinXP "Ken Warthen" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing records to CSV file
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. Ken Warthen wrote: 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" wrote: 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 wrote: 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" wrote: Maybe the recipient has xl2007 (with 1MB rows). Or maybe the .csv files will be split into smaller pieces some how???? "Just Another Yahoo!" wrote: 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)? -- Toby Erkson Excel 2003, WinXP "Ken Warthen" wrote in message ... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing value to another file | Excel Programming | |||
Writing to a .txt File | Excel Programming | |||
writing to a DB4 file | Excel Programming | |||
draw 999 x 8 random records from file with 8614 records | Excel Programming | |||
file writing | Excel Programming |