Chris Hurlbut wrote:
I hate Excel...If your cutting and pasting into excel from SSMS, you could be losing leading zeros. Usually the only time I do this is exchanging results among my team. If I have to extract data and it is an easy one time request I use the SQL Server Import and export wizard... If this is a repeatable data export or you might have to rerun the processing if the results are getting QAd...SSMS is the way to go as others have mentioned.
It's nothing to hate Excel for. It's doing exactly what it's told and the leading zero data looks like a number, not a string. SQL Server is also doing exactly what it was designed to do. MS will tell you that you're using the wrong tool and need to use SSIS instead. Yah... not me.
It's been a real long time but, IIRC, if you preface your leading zero strings with a single quote, I think your woes will be over. Of course, if Microsoft would finally realize with TRUE CSV/TSV files are, everyone would be happy. They finally got that word on BULK INSERT in 2017 but, consider that MS couldn't even figure out that 1900 wasn't actually a bloody leap year in Excel, why would anyone expect the interface between Excel and the SQL Server grid to work together. 😀 I haven't looked to figure out if they fixed BCP for extractions from SQL Server for CSV in 2017 either.
Not sure about the single quote leader... might have to be double quotes on both sides. I don't remember which and haven't had to do that type of thing in well over a decade.