locked
Saving very large file out RRS feed

  • Question

  • I am needing to save a very large file that has 500,000 + rows.  when i send the results to text with a | delimiter and try to save out the results i get an out of memory exception.  When I save the file from the results to a csv i return extra rows because of the comas in the data. any suggestions to get this massive dataset into an excel file with the appropriate number of rows?
    Friday, May 24, 2013 2:36 PM

Answers

  • You can use CSV option with Text Qualifier as "  (double quote).

    Open the flat file connection manager and set the Text Qualifier as ".


    Regards, RSingh

    • Marked as answer by SBolton Friday, May 24, 2013 3:50 PM
    Friday, May 24, 2013 2:44 PM

All replies

  • You can use CSV option with Text Qualifier as "  (double quote).

    Open the flat file connection manager and set the Text Qualifier as ".


    Regards, RSingh

    • Marked as answer by SBolton Friday, May 24, 2013 3:50 PM
    Friday, May 24, 2013 2:44 PM
  • Hello,

    You can use the MS sqlcmd Utility do dump out even large files quite quickly.


    Olaf Helper

    Blog Xing

    Friday, May 24, 2013 2:50 PM
  • Using the right tool - or at least a good one - will help.  SSMS is pretty crappy tool to work with data in any serious manner.  You can use BCP to move data into/out of sql server.  You can pull the data into excel directly using a query as the source of data.  If you want this process to be repeatable, perhaps investigate SSIS - moving data around is the primary purpose of SSIS.  Hope you are using a current version of Excel, since older versions have their own limitations.

    Lastly, no one should have to repeat this.  Always identify the version of sql server that you are using.

    Friday, May 24, 2013 2:52 PM
  • Navigate to your database in the Object Explorer. Right click on it then goto Tasks-Export Data.

    From there keep your source as the default SQL Client click next, and your destination as a CSV file in your chosen location. (You can choose Flat File Destination in the drop down menu.)

    Ensure you have a double quote in your text qualifier box before continuing. You can then carry onto to export the data into the file location and it shouldn't push out any data because of the double quotes.


    Friday, May 24, 2013 3:50 PM