How do I prevent Excel from truncating data?

I am working to pull data from a SQL database.  In SQL Server 2005, I save the results as a CSV file (Version 2013).  Sometimes (no rhyme or reason) some of the data is truncated.

If I copy the data from the SQL Query Results and paste into Excel, the data is truncated

If I copy the data from the SQL Query Results and paste into word, the data is not truncated.

I have over 1/2 million rows to export into Excel and pasting into Word first is not a viable option.
lmfsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ITSysTechSenior Systems AdministratorCommented:
It would be cleaner to import the data rather then paste it by going to the data tab in Excel and then chose "From text" and chose your .csv file.

1.JPG
0
NorieAnalyst Assistant Commented:
Is it a particular field, or type of field, that is being truncated?
0
ITSysTechSenior Systems AdministratorCommented:
If he is able to copy and paste into Word without truncation then the .csv file should not be the issue. Unless he is coping directly from SQL.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

lmfsAuthor Commented:
We are still having the issue, even if we use the Excel Data From Text Import as you suggested.
0
ITSysTechSenior Systems AdministratorCommented:
Okay, how are you exporting into a .csv file? Are you using SQL Server Import and Export Wizard or a SQL query?
0
AlanConsultantCommented:
Hi Imfs,

We need to work out at which point this is going wrong, and you have two steps currently (export from SQL to CSV, then import the CSV into Excel).

Is the data truncated upon export from SQL into the CSV file, or is it getting to the CSV file okay?

Thanks,

Alan.
0
lmfsAuthor Commented:
The data is truncated upon export from SQL into the CSV file.
0
ITSysTechSenior Systems AdministratorCommented:
What are the steps you are taking to export from SQL into the CSV file?
0
Rob HensonFinance AnalystCommented:
This is A long shot...

How long is the value in the cells that are getting truncated? A cell is limited to 32,767 characters. Would that be the problem???

Excel specifications and limits detailed here:

Excel specifications and limits
0
lmfsAuthor Commented:
We ended up finding a solution to this issue. ITSysTech helped us explore Excel a bit more for the solution. Using Excel 2016 ( which provided different options than 2013) we connected to our server that held the database. We then executed our SQL query from Excel and all of the text was captured. Thank you all for your assistance!

Excel Query Solution
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lmfsAuthor Commented:
We continued to explore further export options in Excel and arrived to our own solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.