Get sheet name from a csv file

[Antonio Ponce]

Hello, dear statalist community!

I am trying to import csv files and save them to xlsx format, but I must keep the same sheet names in the new xlsx files from the original csv files

I am having a lot of trouble finding the right code or command to extract the sheet name from a csv file

I have tried r(workseeht_1), but the command does not seem to work for csv files

my code is:


import delimited filename.csv, clear

local sheetname = `r(worksheet_1)'



export excel using filename.xlsx, sheet("`sheetname'", replace)



Anyone has an idea of what could work to incorporate the extraction of the sheet name from the cvs file into my code?


Thanks in advance for your help!!

Kind regards,

Antonio

[Mike Lacy]

I've never heard of a CSV file containing a worksheet name, so I wouldn't see how you could do this, unless (unlikely) the sheet name was stored as a variable in the CSV file. "Worksheet name" is an Excel concept, not a CSV concept.

[Leonardo Guizzetti]

Mike is correct .The CSV file is nothing more than a plain-test file and there are no specifications that include a sheet structure. That is something imposed by spreadsheet software.You don't show your code, but a simple -import delimited- as you imply should import your data.

[Oscar Ozfidan]

I have code that is similar to below and it works for me. In my case, the the sheet names happen to be the filename for the csv files because i create them using visual basic. also note the forward slash "/" right before `filename'.csv and `filename'.xlsx which is contrary to thinking it should be a backward slash. since they are right before a local variable they must be forward slashes for Stata to understand them right...


local filename yourfilename
import delimited C:\...../`filename'.csv
export excel var1 var2 var3 ///
using "C:\.../`filename'.xlsx", sheet("`filename'") sheetmodify firstrow(variables) cell(A1)

[Leonardo Guizzetti]

In my case, the the sheet names happen to be the filename for the csv files because i create them using visual basic.

CSV files are flat -- they have no structure. They are also plain-text, so you can easily inspect their contents. If you had to specify a sheet name in VB Script, then that's a flag that you are not actually creating a CSV file, or the code is incorrect in that it asks for unnecessary information.

Using -import delimited- will work with CSV files because that's what it is designed to do.There notion of sheets in CSV files doesn't exist in this context. However, if you open the CSV file with Excel, then Excel will default to labeling the sheet after the file name; Excel needs to show data in a sheet, and that sheet needs to have a name, so it picks one by default. Try for yourself: make a CSV file, open in it Excel and note the sheet name. Change the name then re-save it is as CSV file. Upon reopening the CSV file in Excel, the edited sheet name will not be shown.

[Antonio Ponce]

Thank you very much for your help Mike and Leonardo, I highly appreciate it!

Leonardo, I had the luck the name of the sheets coincide with the filename, therefore I followed a similar process to the one you suggested!

Thanks again!!