Is it possible to save an excel spreadsheet as a comma delimited file and skip a column on the spreadsheet?

Posted on 2011-10-04
Last Modified: 2012-05-12
I have an Excel spreadsheet with 2 columns, 1st column is a description of the data the user is to enter and the 2nd column is the data.  I'm developing a utility to load only the data from column 2.  I'm using sql loader and its too hard to try and describe each position for each data field exactly.  I'd rather skip column 1 and have only column 2 data in the cvs file.  Is the a way to format column 1 in Excel so it is not seen as data so when saving as a cvs file it is skipped?
Question by:talahi
    LVL 14

    Expert Comment

    If this is a file that you will continually have to edit and save, then I'd create a new sheet, set the first cell to:

    And copy the cell down through the full column.  Then you can just save the second worksheet as the CSV file, and it will only get the second column from the main sheet.

    Alternatively, if this is a one-time thing, just save the file (as excel spreadsheet, to retain the current version), then delete the first column, and save as CSV.  

    Author Comment

    Ok that requires some manual effort and makes automating tougher by creating a new sheet, writing to the first cell and copying the second column to this new sheet but doable.

    I'd rather have the first column ignored somehow, if possible.

    I'll wait a day to see if anyone has a way to do that before accepting this. Thanks.
    LVL 17

    Assisted Solution

    This will save any columns from the active worksheet that are not hidden.  You could modify the code to identify columns to omit in some other way if you liked.

    Public Sub ExportCols()
        Dim rngCopy As Excel.Range
        Dim c As Excel.Range
        Dim wbkExport As Excel.Workbook
        Dim shtExport As Excel.Worksheet
        For Each c In ActiveSheet.UsedRange.Columns
            If Not c.Hidden Then                        ' or select some other ciriterion
                If rngCopy Is Nothing Then
                    Set rngCopy = c.EntireColumn
                    Set rngCopy = Application.Union(rngCopy, c.EntireColumn)
                End If
            End If
        Next c
        If Not (rngCopy Is Nothing) Then
            Set wbkExport = Application.Workbooks.Add
            Set shtExport = wbkExport.Worksheets(1)
            shtExport.SaveAs "c:\Export.txt", XlFileFormat.xlCSV  ' or whatever file name you need
            wbkExport.Close xlDoNotSaveChanges
        End If
    End Sub

    Open in new window

    LVL 14

    Accepted Solution

    My solution only takes about 30 seconds to set up.  You set the first cell (A1) of the second sheet to be the value of B1 of the first sheet by a formula (=Sheet1!B1), and just copy that into the full column, by selecting that cell, copy it, select the full first column, and paste.  That makes any changes you make to the main sheet instantly reflected on the second sheet (which is the one you save as a CSV).  


    Author Closing Comment

    Ok, thanks.  My inexperience with excel was making it harder to implement correctly but now it works.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now