More unintended transformation issues with import csv files

Hello all, I have recently had this similar issue in importing an excel csv file. However, now I have removed the previous file from the global environment using rm() function, went back into the excel csv file and converted all cells to either 'date' or 'number' format.... no commas to be found anywhere.

No issues there, it looks good.. HOWEVER, upon entering the following code to convert to a time series:

I'm now getting another unintended transformation in all 4 columns.
I have noticed that when I changed the excel csv file to 'number' format, saved, closed, then re-opened the file, excel seems to want to change cells back to 'general' format. Is this an issue? There are no commas anywhere that I can find!

Any suggestions for why/how this transformation keeps happening?

Any/all help = greatly appreciated. :slight_smile:

I am not sure what you intend to show with the image you posted. The error about the view function appears because the function is written View.

If you are having trouble reading in a csv, do not look at it with Excel. Use a plain text editor like Notepad. Excel may do all sorts of reformatting without any notice to the user. CSV is not an Excel format; it is plain text. Since the file only has four columns, you can probably copy a couple of lines and paste them here. Place three back ticks on the lines just before and after your pasted material.
Your pasted file lines

Hi FJCC, thanks for your reply... I only get one 'picture' per post being a newbie...

Here is what the excel.csv file looks like:

.csv is a plain text format. Changing the format in Excel and saving will have no effect on it.

What are the data types in your data frame?

sapply(df, class)

Hi Arthur,

image it appears to be 'list' type. Is this a problem? Also, forgive my ignorance as I'm very new to R... but if changing the format in excel has no effect (and I've now discovered this.. haha) why does anyone use excel csv if not to export to some other program such as R, or SAS or whatever? This seems like such a trivial detail but I've have now spent the entire day trying to find a fix.

Also, to your point about notepad... I've just pasted this into a notepad, though should I save it as a *.txt file or as 'all files' ? then what do I select for parameters upon import into R environment?
Thanks so much for your help with this.

No need to apologize, that's what this forum is for.

VARstationary isn't a column in the data frame that's read from the .csv file. If you're having an issue with VARstationary, then it has nothing to do with the csv.

If you're having an issue with the columns in the csv, please read it as a data frame df, then execute this command sapply(df, class). That will tell you the data types of all the columns. It looks like column 1 should be a date type and columns 2, 3, 4 should be numeric.

.csv isn't actually an Excel file. Excel can open .csvs, but Excel files have the extensions .xlsx. If you open a csv in Excel and then make some changes to the format, and save it as .xlsx, then the changes will be saved in the .xlsx. If you make some changes to the format and save as .csv, the changes will be destroyed. That's because .csv is a plain text file. It contains no information about the data types of the columns. You can open it in notepad in Windows and see for yourself the complete contents of the file. What you see is what you get. There's no "metadata" or anything with additional information about the data types, cell colors, cell text formatting, etc.

Thanks Arthur,
While what you're saying makes perfect sense about 'changes in format being destroyed' in a csv file... since there is no info about data types etc. What I don't understand is why, then, is it making such a grave impact on my analysis in R? I've attached the sapply(df, class) command output.. along with the VARstationary View(data) here:

would this be easier to copy into a notepad and re-import? if so... save as *.txt or as 'all types' ? then what parameters in R upon importing into the environment?

You need to run the sapply() on your data frame, not on df.

sapply(VARstationary, class)

There is no need to copy the data into Notepad. Simply open the file using Notepad to see what the csv data look like.

Thanks very much to you both... I have just re-imported as a notepad *.txt file and have converted to timeseries... there is no transformation this time... mission successful!

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.