CODEWITHSUNDEEP
excelbashshellscripting
rooney
rooney

Reputation: 21

Converting xlxs file to csv

I have N number of sheets in an excel file. now I want to convert all of the sheets into .csv format Using bash shell scripting And before converting the xlxs file to .csv I need to add extra columns to each sheet with common value at the end. Please help

Upvotes: 2

Views: 289

Answers (1)

Sam Gilbert
Sam Gilbert

Reputation: 1702

I agree with the other comment, can't this be done through excel?

Example solution if you choose this approach:

' Forces all variables to be declared
Option Explicit

Sub WorksheetLoop()

' Define variable types
Dim WS_Count As Integer
Dim I As Integer
Dim currentWorkSheet As String
Dim PathName As String

' Stop screen from flickering while code is running
Application.ScreenUpdating = False

' Set WS_Count equal to the number of worksheets in the active workbook
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

   ' Find name of the current sheet
   currentWorkSheet = ActiveWorkbook.Worksheets(I).Name

   ' Go to the current sheet
   Worksheets(currentWorkSheet).Activate

   ' Add extra column on that sheet

   ' Select rows in the extra column where the formula is to be added
   Range("D1:D10").Select

   ' Add the formula (example formula, multiplying previous column by 1)
   Selection.FormulaR1C1 = "=RC[-1]*1"

   ' Export to CSV

   ' Copy the current worksheet
   ActiveWorkbook.Sheets(currentWorkSheet).Copy after:=ActiveWorkbook.Sheets(currentWorkSheet)

   ' Rename the current worksheet
   Sheets(ActiveSheet.Name).Name = currentWorkSheet & "_export"

   ' Export the excel to csv

   ' Create path and name for export
   PathName = "" & ThisWorkbook.Path & ActiveSheet.Name & ".csv"

   ' Move sheet to seperate workbook
   Sheets(ActiveSheet.Name).Move

   ' Save as CSV file
   ActiveWorkbook.SaveAs Filename:=PathName, FileFormat:=xlCSV

   ' Close that CSV with the SAVE warnings surpressed
   Application.DisplayAlerts = False
   ActiveWorkbook.Close
   Application.DisplayAlerts = True

Next I

Application.ScreenUpdating = True

End Sub

To run the code use the vba editor (shortcut to open the editor ALT+F11). Open a new macro, paste the code, then you can use F8 to step through each line

Upvotes: 3

Related Questions