Get and Transform is a piece of Excel 2016 that makes this much easier. I'm Oz du Soleil, Excel MVP and author. This course is an introduction to Get and Transform.

I have a new install of Excel 2016, that hates CSV files. It opens them with everything in one column flagpole style, down column A, with commas and speech marks visible. Salient points: • I have two machines, desktop and laptop, both running same version of Excel. Desktop works fine, opens the same problem files formatted correctly. Smb://swnas.swmed.org/data/installs/microsoft office for mac 2016. • I can create CSV files on laptop, save those, open them again on laptop, and it's fine. • Even opening it in Notepad++, saving in the hope of some sort of file format normalisation, and still no good. • I have compared regional settings and almost all settings in Excel.

• I tried renaming the file to TXT, it brought up the text file conversion dialogue, I chose comma delimited. First time it ignored that, still got everything in column A, second attempt, that actually worked, however, that is a pants solution, I want to be able to just natively open CSV files without saving as TXT, I use many different ones every day.

Anyone got any ideas? Thanks in advance. CSV files are character separated value files, not necessarily comma separated.

For more than half the world the separator character is a semicolon (;), not a comma (,) Excel 2016 properly respects your Windows regional settings, and uses the specified 'List separator' character One solution is to change your regional settings for the 'List separator' attribute to the character you want Excel to default to using, e.g. A comma (,) This can be changed in: Control Panel / Region / Additional Settings / List separator: refs: (spec point 3). @TFD RFCs are widely accepted as 'de facto' standards even if they're not ratified as full standards; and the goal of any standard is communication. RFC4180 documents a common format for software to use to communicate; and it calls that format 'comma separated values'. You aren't obligated to be compliant with that format (unless you are); so if you want to use a semicolon as your delimiter; go ahead; but don't claim to be CSV-compliant. Nevertheless; the rest of your answer is spot-on; so have an upvote.

– May 23 '17 at 20:01. The correct answer is to edit your regional settings as suggested above (if a long term change in behavior is desired) Control Panel -> Region -> Additional Settings -> List separator: But for my purposes a simple Edit -> Find and Replace using Notepad to replace all commas with semi-colons was a quick and dirty solution that I preferred. Despite the comment that csv means 'Character Separated Values', in Office 2016 my.csv file association to Excel still says 'Microsoft Excel Comma Separated Values File'.

Excel 2016 comes with a set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. Get & Transform allows users to connect, combine, and refine data sources to meet their analysis needs.

Microsoft today ten new data transformation and connectivity features for Get & Transform that have been requested by many customers. These updates include the following new or improved data connectivity and transformation features: • Web Connector—UX support for specifying HTTP Request Headers. • OData Connector—support for “Select Related Tables” option. • Oracle Connector—improved Navigator preview performance. • SAP HANA Connector—enhancements to parameter input UX. • Query Dependencies view from Query Editor. • Query Editor ribbon support for scalar values.

• Add custom column based on function invocation. • Expand & Aggregate columns provide support for “Load More” values. • Convert table column to a list—new transformation. • Select as you type in drop-down menus. These updates are available for free as part of an Office 365 subscription.

Read about all these improvements in detail. Some links in the article may not be viewable as you are using an AdBlocker. Please add us to your whitelist to enable the website to function properly. Deals You can now get 42% discount on 12-month Xbox Game Pass membership from Amazon US. After the discount, it is available for just $69.99.

With Xbox Game Pass membership, you can: Get unlimited. Microsoft’s new Surface Go is perfect for all your daily tasks, giving you laptop performance, tablet portability, and a touchscreen with the power of Windows 10. It weighs just 1.15 l. If you are looking for a decent tablet which can be used for your occasional work, check out this deal from Microsoft Store. You can now save $310 on the Surface Pro (5th gen) Intel Core m3/. You can now get Sandisk Ultra 256 GB microSD card for just $54.99, $23 less than its usual $78 price, from Amazon US.

This is an A1-rated card, so it can load apps faster for a better smart. Amazon is offering a Buy One, Get One Free deal on Xbox Live membership, offering an extra 3 months Xbox Live when you purchase a 3-month code for $24.99. Besides multi-player gaming, Xbox L. At CES 2018, Lenovo revealed the new Miix 630, its first Qualcomm Snapdragon-based Windows device. The Windows 10 S 2-in-1 detachable comes with integrated 4G LTE for seamless connectivity. The new Surface Pro 6 and Surface Laptop 2 are now available for order in Australia, Austria, Canada, China, Germany, Ireland, Japan, New Zealand, the United Kingdom and United States. Amazon UK is running a great deal on Xbox Live Gold membership cards.