4 ms·

With CSV files you get much better experience on Windows when you use "Data -> From Text/SV" instead of just opening the CSV file. You get a proper preview, can

With CSV files you get much better experience on Windows when you use "Data -> From Text/SV" instead of just opening the CSV file. You get a proper preview, can tweak the field types, set decimal separators etc. This also creates a connection to the CSV file and you can also easily refresh data. With pivot tables this makes it possible to build simple reporting solutions. Just place updated csv files to known location, refresh data and tables.

Foobar8568 3y ago
That's Power Query, which is basically an ETL, and with PowerPivot (xVelocity engine ), you have basically a powerfull self service BI couple with an actual spreadsheet.
soco 3y ago
Uh, my experience is that the opened CSV file will get all converted - dates formats changed, trailing zeroes trimmed, umlauts broken, you name it. So "better" means at maximum "less shitty".
- vxNsr 3y ago
  This isn't opening the csv file directly, it's importing it into an existing (though possibly not yet saved) workbook. I can attest that it works correctly though it is kind of annoying if you don't want to do more than just some simple manipulation. Also I believe excel for mac either doesn't have this feature or it's not as feature rich as the windows counterpart.
- Isthatablackgsd 3y ago
  There is a difference between double clicking the CSV file vs importing from within. Importing via Get Data > CSV will bring up PowerQuery which only made a copy of the source file and allows me to modify the data without affecting the original file. If I made a mistake after transforming, I can go back to PowerQuery (without importing again) and undo the step then adjust the modifications. That is the beauty of PowerQuery. Even in the wizard, it does allow me to set the data type of the columns before opening. Users generally skip that step ahead and get down to the data. Problem with importing is that laypeople are not familiar with PowerQuery and it can be overwhelming for lot of users. It didn't make it less shitty. The problem is on the between the keyboard and the chair. If users take the time to be familiar with the wizard and PowerQuery, a lot of miscorrection would be avoid in the first place.
  - soco 3y ago
    Oh I see. I definitely don't want to go through all these hoops. If it doesn't open it properly on click, it's only a failure which needs to be corrected by hand - be it with PowerQuery or whatever. And getting familiar with a shitty workaround doesn't make it less shitty or less workaround.

Foobar8568 3y ago

That's Power Query, which is basically an ETL, and with PowerPivot (xVelocity engine ), you have basically a powerfull self service BI couple with an actual spreadsheet.

soco 3y ago

Uh, my experience is that the opened CSV file will get all converted - dates formats changed, trailing zeroes trimmed, umlauts broken, you name it. So "better" means at maximum "less shitty".

vxNsr 3y ago
This isn't opening the csv file directly, it's importing it into an existing (though possibly not yet saved) workbook. I can attest that it works correctly though it is kind of annoying if you don't want to do more than just some simple manipulation. Also I believe excel for mac either doesn't have this feature or it's not as feature rich as the windows counterpart.
Isthatablackgsd 3y ago
There is a difference between double clicking the CSV file vs importing from within. Importing via Get Data > CSV will bring up PowerQuery which only made a copy of the source file and allows me to modify the data without affecting the original file. If I made a mistake after transforming, I can go back to PowerQuery (without importing again) and undo the step then adjust the modifications. That is the beauty of PowerQuery. Even in the wizard, it does allow me to set the data type of the columns before opening. Users generally skip that step ahead and get down to the data. Problem with importing is that laypeople are not familiar with PowerQuery and it can be overwhelming for lot of users. It didn't make it less shitty. The problem is on the between the keyboard and the chair. If users take the time to be familiar with the wizard and PowerQuery, a lot of miscorrection would be avoid in the first place.
- soco 3y ago
  Oh I see. I definitely don't want to go through all these hoops. If it doesn't open it properly on click, it's only a failure which needs to be corrected by hand - be it with PowerQuery or whatever. And getting familiar with a shitty workaround doesn't make it less shitty or less workaround.