• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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

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?
0
talahi
Asked:
talahi
  • 2
  • 2
2 Solutions
 
sentnerCommented:
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:
=sheet1!B1

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.  
0
 
talahiAuthor Commented:
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.
0
 
andrewssd3Commented:
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
            Else
                Set rngCopy = Application.Union(rngCopy, c.EntireColumn)
            End If
        End If
    Next c
    
    If Not (rngCopy Is Nothing) Then
        rngCopy.Copy
        Set wbkExport = Application.Workbooks.Add
        Set shtExport = wbkExport.Worksheets(1)
        shtExport.Paste
        
        shtExport.SaveAs "c:\Export.txt", XlFileFormat.xlCSV  ' or whatever file name you need
        wbkExport.Close xlDoNotSaveChanges
    End If

End Sub

Open in new window

0
 
sentnerCommented:
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).  

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now