Roll Your Own CSV
In Creating CSV Files, I showed how to use the Save As dialog to create a text file from an Excel worksheet. Stephen flattered me in to expanding on that theme. If you have special needs when creating a CSV file, you can roll your own with VBA.
This example takes a worksheet with some numbers on it and creates a CSV file where certain columns have a specified width. It pads those columns with zeros if the length of the value is less than what’s required.
Sub CreateCSV()
Dim rCell As Range
Dim rRow As Range
Dim vaColPad As Variant
Dim i As Long
Dim sOutput As String
Dim sFname As String, lFnum As Long
‘Required width of columns
vaColPad = Array(0, 0, 6, 0, 4)
i = LBound(vaColPad)
‘Open a text file to write
sFname = "C:MyCsv.csv"
lFnum = FreeFile
Open sFname For Output As lFnum
‘Loop through the rows
For Each rRow In Sheet1.UsedRange.Rows
‘Loop through the cells in the rows
For Each rCell In rRow.Cells
‘If the cell value is less than required, then pad
‘it with zeros, else just use the cell value
If Len(rCell.Value) < vaColPad(i) Then
sOutput = sOutput & Application.Rept(0, _
vaColPad(i) - Len(rCell.Value)) & rCell.Value & ","
Else
sOutput = sOutput & rCell.Value & ","
End If
i = i + 1
Next rCell
‘remove the last comma
sOutput = Left(sOutput, Len(sOutput) - 1)
‘write to the file and reinitialize the variables
Print #lFnum, sOutput
sOutput = ""
i = LBound(vaColPad)
Next rRow
‘Close the file
Close lFnum
End Sub
The output looks like this
Mark LaVenia:
Dick: Wow–great site (definitely over my head though). Maybe you can help: I’m a school teacher (and novice Excel user), and I’m trying to import my school’s roster into Harcourt’s Electronic Assessment program. Our roster is presently formatted in Excel; Harcourt requires it to be in an ASCII file. I’m not seeing ASCII as a possible Save As format. A colleague recommended I try saving as Text Tab delimited, and the Harcourt program at least recognizes it, but will not import it, because it says that there are too many characters for the first field. (The fields on Harcourt are ID, Name, and Password.) I think this format is cramming everything into the ID field, rather than dispersing along. Any guidance you can lend will be greatly appreciated. Thank you, Mark
3 February 2005, 8:59 amJake Marx:
Mark,
I’m not sure what ASCII format Harcourt is looking for in that particular software (ASCII is just another way of saying plain text). They may be looking for a comma delimited file. I would try the following to see if it will work for you:
1) save workbook as type “CSV (Comma delimited) (*.csv)”
2) change the resulting filename’s extension to .txt
If that doesn’t work, you could try PRN format, which is a “flat file” format (no delimiters, but a certain number of characters for each column of data in the text file - typically padded with spaces). But that one’s a bit trickier to work with, so try the CSV first.
3 February 2005, 2:08 pmScott Sewell:
Dick,
Thanks for this site. It helped me tremendously. I was able to create a macro that converted spreadsheet data to “.txt” file easy as 1-2-3. Now, the question is…Why can’t I get this macro to work on other PCs than my own. I installed the “personal.xls” macro sheet to the default XLSTART folder on several other machines, then fired up EXCEL. This macro opens a vb form which has 5 command buttons on it. Each button opens a new form. When the first command button is pressed, a second form opens up. However, when I hit the “Add” command button on this second form (while on other PCs from my own) the application aborts with a “compiler error, can’t find project or library”; and the debugger opens up to the code behind this second screen on the first line of code which is strleading = “”. Is there any modules or dlls that need to be present on the PC for a personal.xls macro to execute from within EXCEL? Any help would be greatly appreciated.
Thanks for this site, it has helped me a great deal.
Scott Sewell
18 May 2005, 10:44 am512-427-6262
willie:
Hi all,
I`m locking also for a good solution for my users, using MS EXCEL to create csv files.
We need semicolons to Import Data.
But I`ve try it with OpenOffice. It so easy.
I would recommend to try in such cases with OO.
Regards
15 June 2005, 9:16 amUK:
So is it just looking for an open file? If so can you export a csv from a specified file name without opening the file?
Thanks
20 October 2005, 10:12 amUK
www.wthef.com
Brian Kranson:
FYI - 2 Changes I had to make before this code would work on my machine.
1) Changed all 9 instances of ‘ to ‘
14 November 2005, 11:45 am2) Changed “C:MyCsv.csv” to “C:\MyCsv.csv”
Shuvro:
Hi,
It was a neat trick to create the CSV. However I would like to know how to do the reverse. No the question is why I dont use Excel to import the file? Well what I need to do is create a CSV file from different sheets and import all the data into 1 sheet. Where each CSV file represents a specific group. And the data need to be populated in particular cells only.
Would be really nice to get some pointers on this.
Thanks
3 August 2006, 3:57 amKaz:
Hi,
12 February 2008, 5:37 pmI am looking for a script in VBA to first delete the first 10 rows, then extract the remaining used range in a csv format but the column widths should be exactly the same as the source file, I am not sure how I can define the column width in a csv file since all cells are only seperated by “,”. Please help…
Thanks.
Dick Kusleika:
Kaz: Usually you don’t have delimeters (like commas or tabs) and fixed-width columns. If each column is a prescribed width, why would you need the comma. Here’s a post on fixed-width that may be helpful
28 February 2008, 9:54 amhttp://www.dailydoseofexcel.com/archives/2008/02/28/fixed-width-text-files/