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.

CreateCSV1

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

The output looks like this

CreateCSV2

Posted in Uncategorized

41 thoughts on “Roll Your Own CSV

  1. 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

  2. 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. 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

  4. 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

  5. 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”

  6. 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

  7. 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.

  8. 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.

  9. 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

  10. David: Change this line

    For Each rRow In Sheet1.UsedRange.Rows

    to

    For Each rRow in Sheet1.Range(“A1:B50”).Rows

    Change 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).Rows
  11. Hi,

    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.

  12. 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.

  13. Howdy,

    Out of interest, instead of padding out with zeroes based on column width, is there an easy way to add a “~”at the start and end of the text in each cell?

    Along the lines of:

    sOutput = Application.Rept(“~”,1) & sOutput & Application.Rept(“~”,1) & rCell.Value & sDELIM

    Cheers, Stu

  14. @Dick – I’m pretty sure you can replace these lines from your blog article’s For/Next loop…

    ‘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

    with this single line of code…

    sOutput = sOutput & Format(rCell.Value, String(vaColPad(i), “0”))
  15. @Dick – Whoops! I forgot to include the delimiter in my last one-liner posting…

    sOutput = sOutput & Format(rCell.Value, String(vaColPad(i), “0”)) & sDELIM
  16. @Stuart – I think you are looking for this line of code (it replaces the entire If/Then/Else/EndIf block)…

    sOutput = sOutput & “~” & rCell.Value & “~” & sDELIM

  17. Out of interest, seeing as Windows 7 protects against saving files to the root of a drive, Would it be possible to replace the static filename and bring up a save as dialog box instead?

  18. Nice information! Thank you so much.

    I need to have my user select an excel file and then programatically resave the original excel file into a CSV format without excel every popping up it’s head.

    In the end there would be 2 files, File1.xls and File1.CSV.

    Is this possible without actually opening up excel? I just want to use its saveas feature. :)

  19. Stuart: You need Application.GetSaveAsFileName, which returns a string that has the full path to whatever the user enters. You can store that name in sFname and it should work like you want.

  20. David: You have a couple of options, neither of them easy IMO. You could make an Excel dictator app. That would open a new instance of Excel, but it wouldn’t look like Excel if you use the right settings. (See Professional Excel Development for how to create dictator apps).

    The other option is a VB6 program that automates Excel. VBA and VB6 are pretty similar and you’d have GetOpenFilename because you’d have a reference to the Excel library.

  21. hi everyone

    can someone help me with the csv format im using the myob program i export the files to excel then save it using the csv format but every time i open the file some of the account number was converted to date format (e.g : account number is 1-2000 (excel) after saving it become jan 10. I try to change it manually and saving it all over again but same results . . how can i save the file without worrying of any changes

  22. Angel: I’m sure there are better ways to do this but this is what works for me: Change the csv extension to txt then open the file in notepad. do a find..replace and replace – with the tilde character ~. Save the file. Rename it from .txt to .csv and now load it back in Excel. That’s one way of getting Excel to not convert your account numbers to dates.

    Greg

  23. This script is exactly what I have been looking for, however, when I run the script the C:MyCSV.CSV file is empty (I just copied in the code at the top of this page into an Excel module and ran it). It looks like it is creating the first field with a length of zero…and then finishing.

    I seem to be the only person that is having this issue, so must be something I am doing wrong. Any ideas appreciated.

    Cheers,

    Detz.

  24. 98: The code references Sheet1, which is the codename for the sheet. Make sure Sheet1 in your workbook actually has some data. Or change the reference to the proper sheet’s codename.

  25. When working with long strings the code overhead can be significantly reduced by separating the concatenation of the short strings into a single string to be added separately to the long master string, ie

    sOutput = sOutput & (Application.Rept(0, vaColPad(i) – _
    Len(rCell.Value)) & rCell.Value & sDELIM)

  26. When working with long strings the code overhead can be significantly reduced by separating the concatenation of the short strings into a single string to be added separately to the long master string, ie

    sOutput = sOutput & (Application.Rept(0, vaColPad(i) – _
                  Len(rCell.Value)) & rCell.Value & sDELIM)
  27. One more issue – when I save this script to a module in my Personal.XLS (in the Excel Startup folder) and try to run it on a new Excel Workbook (i.e. Book1.XLS) it does not work and creates and empty file. I have verified that my worksheet within my Book1.XLS is called Sheet1.

    If I was actually add this script to a module for Book1.XLS, it works just fine. Is there a statement that I need to add to my Personal.XLS module that will allow this script to run on other workbooks?

  28. With Personal.xls as the active workbook, choose Window-Hide and then close Excel, answering Yes to the prompt to save Personal.xls
    that should fix the issue you’re having.

  29. Gave that a shot too, but still no dice (now there are no other Workbooks open except Book1). It will only run successfully when I run the macro specifically from the Book1 VBAProject.

  30. Firstly thanks for giving so much of your time and and sharing your knowledge with us. I got here by a google search and I’m finding snippets of what I need here and there. Basically I’m using a workbook to enter data and use areas of the work book to make calculations and based on the entries.
    In the coarse of my work I need to take data from one software system and enter it onto not one but several others. For many of the fields they are also transformed or formatted in someway before I can use them in the other software. I’ve made myself a sort of ‘pasteboard’ if you will that I use to paste the initial entries. My worksheets extrapolate the data I need to aste into the other programs.
    So far I’ve been saving the entire workbook for each policy, which is absurd and far too weighty and wasteful. My goal is to only save one sheet as the data and be able to retrieve it again later. But not just one record. I want to store subseqent records also so I can look back over the policies I’ve done in the past. So each time a new record is entered I want to store it, it needs to go on the next free row on the ‘Data_Sheet’.So I’ve split my subs into;

    StoreRecord() I will make a button which when pressed stores all the data entered on sheet1 on the first availabe row of the ‘DataSheet’ (So I need to know how to find the first available free row – or I will need a dialogue asking what row to save to)
    ReloadRecord() With this I hope to repopulate the entry fields with previously stored record (row) from the DataSheet. I will likely need a dialogue or dropdown combobox to choose which record to be loaded.
    SaveDataSheet() and LoadDataSheet() Idealy I’d like to offer a file save/load dialogue with this so I can choose a filename. I also want to overwrite the existing sheet with the newly loaded one and have it named ‘DataSheet’ so any macros still work for repopulating etc.

    Sorry for being so long winded. Just thought I’d ask if you have any tips on any of these subs it would be a great time saver for me as it is all up hill from here.

    Thanks again for a great site.

  31. Hi Dick, Well there is a God after all. I’ve been bugging Microsoft about the inconsistencies of saving CSV file with and without macros in an attempt to create a text file using the pipe character “|” as a delimiter. This little gem of code, albiet 6 years old, solves all my problems and makes me very, very happy. Thanks so much.

  32. In case anyone is looking for a solution to save multiple sheets of the same workbook as csv:

    Sub SaveSheetsAsCsv()
     
        Dim rCell As Range
        Dim rRow As Range
        Dim sSheet As Worksheet
        Dim vaColPad As Variant
        Dim sOutput As String
        Dim sFname As String, lFnum As Long
       
        Const sDELIM As String = “,”
           
        lFnum = FreeFile
       
        For Each sSheet In ActiveWorkbook.Sheets
       
         sFname = ActiveWorkbook.Path & “” & sSheet.Name & “.csv”
         
         Open sFname For Output As lFnum
       
         For Each rRow In sSheet.UsedRange.Rows
             For Each rCell In rRow.Cells
                 sOutput = sOutput & CStr(rCell.value) & sDELIM
                 i = i + 1
             Next rCell
             sOutput = Left(sOutput, Len(sOutput) – Len(sDELIM))
             
            Print #lFnum, sOutput
             sOutput = “”
         Next rRow
             
         Close lFnum
       
        Next sSheet
    End Sub
  33. Hey guys,

    Have a problem… someone on another post wrote a similar question… how do I get excel to surround each value with QUOTES “” ? Any ideas, really need this…? THANK YOU ALL IN ADVANCE!

  34. This is a truly splendid piece of code. i was in a tight spot with a job and time was running low. The code itself didn’t contain anything i haven’t used at some time in the past but the concept/thought process for the routine is great!

    Massive help, cheers!!

  35. Can Excel be used to create a pipe delimited output at a simple click, without the use of extra code or programming? If so, how do you do it for a single file and not all files from then on?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.