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
Const sDELIM As String = ","
'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 & sDELIM
Else
sOutput = sOutput & rCell.Value & sDELIM
End If
i = i + 1
Next rCell
'remove the last comma
sOutput = Left(sOutput, Len(sOutput) - Len(sDELIM))
'write to the file and reinitialize the variables
Print #lFnum, sOutput
sOutput = ""
i = LBound(vaColPad)
Next rRow
'Close the file
Close lFnum
End Sub
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
Const sDELIM As String = ","
'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 & sDELIM
Else
sOutput = sOutput & rCell.Value & sDELIM
End If
i = i + 1
Next rCell
'remove the last comma
sOutput = Left(sOutput, Len(sOutput) - Len(sDELIM))
'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
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
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.
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
512-427-6262
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
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
UK
http://www.wthef.com
FYI - 2 Changes I had to make before this code would work on my machine.
1) Changed all 9 instances of ‘ to '
2) Changed "C:MyCsv.csv" to "C:\MyCsv.csv"
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
Hi,
I 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.
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
http://www.dailydoseofexcel.com/archives/2008/02/28/fixed-width-text-files/
Hi, I have an excel file that is in Traditional Chinese. When I convert to a CSV file, I lost all the chinese character as all are converted to "??????". Is there any way I can overcome this? Thank you very much.
Hello,
Can the range that is converted to CSV be specified ??
The code above does exactly what I require, but I only want the first two columns (i.e. A & B) and from row 3 down.
Thanks for a great site
David: Change this line
For Each rRow In Sheet1.UsedRange.Rowsto
For Each rRow in Sheet1.Range("A1:B50").RowsChange the sheet's codename and the range reference to whatever you want. If you don't know how many rows there will be, you could use
For Each rRow In Intersect(Sheet1.UsedRange, Sheet1.Range("A1:B1").EntireColumn).RowsHi,
I am looking for a method to make a .CSV file with the "@" as the delimiter.
When i use the script in this example, i use first the F8-key for stepping through the program, and at the line "Open sFname For Output As lFnum" Excel gives me the error "file not found".
Changing sFname = "C:MyCsv.csv" to sFname = "C:\MyCsv.csv" does not help.
Can anyone help me out here?
Thanks in advance.
Anton: I reposted the code and added an sDELIM constant so you can delimit by any character you want. That should have any effect on your problem, though, as long as I was retesting it I thought I'd spruce it up. You should make sure you have the correct permissions to create file on the root drive. If you have Vista, you probably don't have permission and you should choose a different location.