topic Re: How to saving file xlsx to csv filetype in SAS Programming

How to saving file xlsx to csv filetype

Phil_NZ — Mon, 15 Feb 2021 09:05:09 GMT

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.

Re: How to saving file xlsx to csv filetype

andreas_lds — Mon, 15 Feb 2021 09:42:03 GMT

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.

Re: How to saving file xlsx to csv filetype

ChrisNZ — Mon, 15 Feb 2021 23:11:48 GMT

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

Re: How to saving file xlsx to csv filetype

Kurt_Bremser — Tue, 16 Feb 2021 09:04:55 GMT

LibreOffice can do such conversions from the commandline.