How to saving file xlsx to csv filetype

[Phil_NZ]

Hi SAS Users.

Recently I have been stucked with data import. As suggested by some experts in this group, especially @ballardw , I am thinking of changing the data type from xlsx to csv to make the data plainer and let it be easier to be dealt with later on.

So, regarding saving excel file (xlsx datatype) to csv file, whether we only use the saving function from excel like the picture below

And is there any notice I need to do when saving from xlsx to CSV type to import to SAS later on?

And it looks like that when saving from xlsx to csv type, we cannot save the whole workbook but just sheets individually, please correct me if I understand incorrectly, is there any way to save the whole workbook or any alternative way to do so?

I am really new to CSV, all comments and contributions are highly appreciated, especially letting it be friendly to SAS environment! 

Best regards.

[andreas_lds]

If there is only one sheet in each file, just click "OK" and everything is fine. Important: Don't open csv-files with excel, use a text editor (like notepad++) instead.

[ChrisNZ]

Yes, one sheet per CSV file.

In my opinion, the best way to do this is to run a vbs script fro SAS.

For example (taken from https://excelfox.com/forum/showthread.php/388-export-all-worksheets-to-separate-csv-files😞

    Run it like so: wscript xls2cvs.vbs C:\SourceFile.xls C:\DestFile.csv

    Code:

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Dim oSheet

If oBook.Sheets.count = 1 Then 
  'save a single sheet
  oBook.SaveAs WScript.Arguments.Item(1), 6
else
  'save multiple sheets
  i=1
  aname=split(Wscript.Arguments.Item(1),".",-1,1)
  For Each oSheet In oBook.WorkSheets
    fname = aname(0) & "_sheet" & Cstr(i)
    oSheet.SaveAs fname, 6
    i=i+1
  Next   
End If

oBook.Close True
oExcel.Quit
WScript.Quit
set oSheet = Nothing
set oBook = Nothing
set oExcel = Nothing

[Kurt_Bremser]

LibreOffice can do such conversions from the commandline.