I have a workbook that runs on Excel for PCs. It uses ADODB to connect to an Azure SQL database, run a query and return the results to a worksheet. It works great. I need to create one for Excel for Mac. Already, the problem is that it does not support ADODB. I have downloaded and installed SQL drivers from Actual Technologies. However, I'd have to pay for several licenses.
I'd like to avoid that. Can someone send me simple code to connect to Azure SQL from Excel for Mac 2016 and run a query and return data to Excel, without involving the purchase of a third party software? Seems that should be doable.
To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. New in Excel 2016 is the TEXTJOIN function, which allows you to concatenate a range of cells with a delimiter. With, TEXTJOIN, the example above would look like this. Get over 200 Excel shortcuts for Windows and Mac in one handy. 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.
Recently I was tasked to create a pipe delimited file from an Excel Spreadsheet, a simple task you would expect. On first glance it was not as simple I thought. I use Excel 2010 and was expecting to be able to do something like ‘save as’ and select the file type ‘CSV (Comma delimited)(.csv)’ and then have the option to select the delimiter like when importing a csv file. After a little head scratching and a quick google, I found I had to make a global setting change in the control panel (Yes, that’s right). So in Windows 7, I found you must do the following to change the delimiter:.
Make sure Excel is closed. Navigate to control panel. Select ‘Region and Language’. Click the ‘Additional Settings’ button. Find the List separator and change it from a comma to your preferred delimiter such as a pipe ( ).
Click OK. Click OK. Exit Control panel.
Open the Excel file you want to export to a pipe delimited file. Select File, Save As. Change the ‘Save as type’ to ‘CSV (Comma delimited)(.csv)’. Change the name and file extension if you want, by default stays as csv even though a different delimiter. Click Save. Click OK.
Click Yes And that is it, easy right? Hmmmm – if you only wanted to change the delimiter once not an easy/simple process. Ok, it is not as bad as I make out.
For example, if you want to have tab delimiter or fix width (space) there are save as file type options available, however, given how easy the import wizard is I would have thought they would have done something similar. Have I missed something or has google put me wrong? If so let me know Barry, Preventer Of Chaos. Barry blogs about how to stop chaos in your systems Read to find out how to filter based on the structure of information or Barry’s blogs. Visit our to find out how our courses can help learn how to get the best from your data.
Hi Barry, It doesn’t work for me. ? I did exactly what you explained, even rebooted my computer to hope the changes would have any effect, but even after a reboot, the saved CSV will still have; instead of,. The Region and Language settings are correct, so the list separator is configured with a, not a;. Even when I open up a new Excel window and a Blank Workbook, fill in some data and save as CSV (Comma delimited) it still is being saved with;. I am very sad because things aren’t working the way I would like to. Best regards, Erik. Hi Raman, Barry’s colleague Shaun here.
Welcome to the wonderful world of “text encoding”! In general, Microsoft favours character sets that only include Latin characters, and I have faced the same problems working with Russian characters that you face here with Japanese. Unfortunately successive Office versions have hidden the solution further and further from view! On Windows: – When you have your Excel file ready, save it in Excel format somewhere safe, so you can return to it if needed. – Then, go File - Save As, and choose a location for your CSV.
– In the “Save as type” box under the file name, select “CSV (Comma delimited) (.csv)”, then click the “Tools” button next to “Save”, and select “Web Options”. – Choose the “Encoding” tab and change the option under “Save this document as” to either “Unicode (UTF-8)” or one of the Japanese options. You might need to experiment a little! On Mac: the options above don’t exist, but see my next comment on this post! Good luck, Shaun. If you try my solution to Raman’s problem on Mac, you’ll find there is no (longer) support for saving CSVs in other encodings. For the other problems described above, including Barry’s original use case, the solution I use is LibreOffice or OpenOffice.
– Install LibreOffice or OpenOffice (both are free and open source) – Open your Excel or CSV file, make sure it looks right – “Save as”, choose CSV, and check the “Edit filter settings” box – Choose encoding (character set), field delimiter (comma, pipe, or whatever else you want), and text delimiter! Nobody likes an answer that involves installing new software.
But I think the outpouring of CSV-related issues in other folks’ comments support my point above that we would really like these features but they have been taken away (or hidden) in successive redesigns of Office. Fortunately, if you do a lot of work with other languages or exotic file formats, LibreOffice/OpenOffice can help. Good Information! Note: If you have changed the list separator from, to then on manually saving an excel file to CSV will generate a pipe delimited CSV. However, if you have a code(macro) which is saving an excel to csv format then in the SAVEAS method you need to add a parameter “Local” and set its value to True. If you do not set this parameter, the default value for this parameter will remain false and your code will not refer to the Windows List separator changes at all.
Hoping this information helps.