Allow user defined "text qualifier" when importing from csv file or text
There are some cases where I export data from SQL server to a text file and want to open it with Excel. I do this because some of my columns have text that has newlines [ char(13) + char(10) ], which will not correctly import into Excel, when importing from SQL Server to Excel directly. There should be other cases where a user can't choose a pre-determined text qualifier from some proprietary software.
This is where Excel should allow the user to choose the text qualifier instead of only having the pre-defined choices.
LibreOffice Calc allows this functionality, and I sometimes open the text file in LibreOffice, since it does allow me to choose the text qualifier, and then the file will import correctly. Once in LibreOffice, I save the file to Excel format, and then I have my data.
I shouldn't have to use LibreOffice to do something that Excel could easily do if I could choose a text qualifier.
Using my attached sample file in Excel, when a user opens a new text file in the following way starting with Data tab on the ribbon:
DATA>Get External Data>From Text> then choose your text file
On the popup window Text Import Wizard - Step 1 of 3 choose Delimited, and check the box My data has headers
Then on Text Import Wizard - Step 2 of 3
Choose comma as a delimiter, but I can't choose a text qualifier, which in this case, is the tilde. But unfortunately in Excel, I can't choose my text qualifier, and thus my data doesn't import correctly.
When I try in LibreOffice Calc, I just open the csv file, and under Text Import, choose the following Separator Options: Comma, Merge Delimiters, and String Delimiter (set to in this case) Tilde. Then I get the data importing correctly.
Thus, unfortunately or fortunately, I save in Excel format, and now can use properly in Excel.
Please allow a user to choose a TEXT QUALIFIER of their liking!
I have attached my sample file for testing... you can see that the tilde is the text qualifier, and is delimited by commas, like a standard csv file.
Thanks for your consideration, and please don't make us use LibreOffice Calc any longer!