CODEWITHSUNDEEP
sql
Zannie155
Zannie155

Reputation: 49

Copy huge amount of SQL data into a file

I need to copy over 6 million rows of data into a source file, for instance SQL to Excel, but the problem is copying to excel gives me a memory error. Any suggestions on what to do or what type of source file I could use that would support this amount of data ?

Upvotes: 0

Views: 11413

Answers (3)

H_J
H_J

Reputation: 486

Excel has a limitation on number of rows, the current limit is 1,048,576.

I have 2 recommendations generally works with high volume data export:

  1. Try exporting the file in .csv format. It is memory efficient over Excel export. Moreover csv format is among the most convenient format in terms of data transformation.

  2. Find out a way to split the number of records in your sql query. Applying some set of filters which exhaustively covers all the records. (In your case you could try creating 6 different exports of 1 Million Records)

What is the final analysis you would like to do with this data? Knowing more on type of analysis you are looking to perform and platform you would use can help us help you.

Upvotes: 0

Gregology
Gregology

Reputation: 1735

Microsoft Server Management Studio allows you to export tables, views, and queries to csv and rpt files which have no size limit. Here are two options.

First option:

For SSMS - SQL Server 2008 R2 (to get CSV)

Tools > Options

screen shot

expand Query Results > SQL Server > Results to Text

select the output format you would like to use

Query > Results To > Results To File

Then execute required query. It will Prompt to save result as .rpt.

You can either save this result as .csv explicitly (Enclosed file name with extension in double quotes) or open .rpt file with excel to view. Details from SQL forum.

Second option:

Create a view with the data you require. Right click the database Tasks > Export data...

enter image description here

Then save the csv file.

Upvotes: 1

kufudo
kufudo

Reputation: 2833

OpenOffice Base has no limit on number of rows. You could try importing it into that. Without further details about what you're trying to do, it's hard to recommend the best tool for the job though.

Upvotes: 0

Related Questions