Import of csv

[Tom]

If I edit the file (even just delete the lines to make the file more light) the data is COMPLITELY wrong.

Never open a CSV file with Excel.  It can totally corrupt the data.

[SASdevAnneMarie]

You mean that It would be better to openly the csv with Notepad?
Thank you!

[Kurt_Bremser]

Never (as in NEVER) inspect a CSV file with Excel. Use a programmer's editor like Notepad++ for this. Such editors keep the textual structure and will only do the changes you make.

[SASdevAnneMarie]

Thank you!

[SASdevAnneMarie]

Sorry Tom,
I’m curios)) Why Excel corrupts the csv? The encoding is not same?

[ballardw]

---

@SASdevAnneMarie wrote:
Sorry Tom,
I’m curios)) Why Excel corrupts the csv? The encoding is not same?

---

It is related to the behaviors you get when entering values manually. Go to an empty cell in an Excel spreadsheet, type 5-1  , that is a 5, a dash character, and 1.

What does the cell display when you hit enter to move to the next cell?

I will bet that you see 1-May appear. And if you change the format of the cell to General it now displays 43952 (note for those reading this after 2020 you would get a different value due to the default year used). Kind of different from the 5-1 entered. Why? Excel has assumed that you meant to enter a date in the current year but didn't enter a year.

It will do similar things with other types of text depending on the mysteries in the bowels of Microsoft. And if you save the file, even to CSV, the values have been changed and unrecoverable (at least without another pre-exam copy of the file).

[Tom]

---

@SASdevAnneMarie wrote:
Sorry Tom,
I’m curios)) Why Excel corrupts the csv? The encoding is not same?

---

It you let it just open a CSV file on its own it will make decisions about what values the text represents that might not be what you meant the text to represent.  If you open Excel and use whatever the current menu options are for reading data from a text file then you can prevent it from doing that.  You can get control over which columns it treats as text and which it treats as numbers (or dates or time or ...).  If you do that then you could have some success with reading a CSV file into an Excel spreadsheet.   

Note if you do have the data in Excel then just save it as an XSLX file and then SAS can read it more easily as it does not have to guess where the lines and values start and end by looking at characters in a text file. 

[jimbarbour]

You might need more than just regular Notepad.  Regular Notepad will not show the Carriage Return and Line Feed characters.  I used Notepad++.

Jim