SQL Table Export

[iMORT3rnAL]

Hey people,

Our auditors asked for a spreadsheet containing all the entries in one of our SQL tables. If I filter this to only our previous financial year's data, which they want, then I still have a little mare than 6.4 million lines. I proposed that I rather give them a SQL database containing only that table, but apparently they are only able to use Excel and text documents.

I managed to export the data into a CSV file, but I had to use our SQL server to view it because of memory requirements.

Any ideas on how I can send them this data?

Ideally I would like to export it and have it overflow into a new spreadsheet every 200,000 lines or so.

Thanks in advance.

 

[froot]

Why not just limit the results and write each of those to a CSV file?

 

[murraybiscuit]

if you're on linux/unix, you can use split and specify the amount of lines per file.
http://unixhelp.ed.ac.uk/CGI/man-cgi?split

 

[hyperian]

Ask them to install PowerPivot for Excel (free from Microsoft), and then you can send them all the data you want.

 

[iMORT3rnAL]

Why not just limit the results and write each of those to a CSV file?

At the moment I'm doing it in half months, but it's still time consuming. So I'm just going to script the process because they are coming back for more data.

if you're on linux/unix, you can use split and specify the amount of lines per file.
http://unixhelp.ed.ac.uk/CGI/man-cgi?split

Ah yes, this is EXACTLY what I'm looking for, but unfortunately we're using M$ SQL Server.

Ask them to install PowerPivot for Excel (free from Microsoft), and then you can send them all the data you want.

Haha, yeah... I'll rather just send them the spreadsheets instead of trying to explain that to them.

 

[rrh]

Excel 2007+ can handle over a million rows per spreadsheet.

Write the SQL - extracting per month - put the SQL into Excel (i.e. 12 spreadsheets, one per month) and perform the extracts direct into the spreadsheets.

 

[iMORT3rnAL]

Excel 2007+ can handle over a million rows per spreadsheet.

Write the SQL - extracting per month - put the SQL into Excel (i.e. 12 spreadsheets, one per month) and perform the extracts direct into the spreadsheets.

Yip, 1,048,576 to be exact, but apparently this is still too big for them (laptop with not enough memory), hence the half month files.

But I've now compressed and uploaded the data, awaiting their feedback.

 

[Nod]

Tell the auditors it is 2012, not 1984. They can run a DB on their PC/Laptop and import the data there.
If their laptop specs is not up to scratch, then they have to speak to their company. It is not your problem.

 

[SBSP]

Auditors can sometimes be really stupid.

They cant handle the data so now its your problem , A real Auditor will come to your premises connect to the database extract what they need then leave.

If you want try this.
Open a new excel Sheet, get into the VB Macro editor, double click the first sheet and type

Sub Main

End sub

If you type Sub Main it will automatically type End sub underneath it.
then type the below paste the below between sub Main and end sub.

MyQuery = "Select * from YOURTABLE WHERE YOURTABLE.Date .... Bla bla bla"


    Sheets("Sheet1").Select
    Range("A1").Select
' Declare the QueryTable object

' Set up the SQL Statement
Sqlstring = MyQuery 
' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = "ODBC;DSN=YOURDB;UID=sa;PWD=passwordhere;Database=YOURDB"
' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=Sqlstring)

 .Refresh
End With

with A SQL where clause split it up in multiple excel Sheets.

Then Simply Run the Macro and it will populate the sheets

 

[iMORT3rnAL]

Tell the auditors it is 2012, not 1984. They can run a DB on their PC/Laptop and import the data there.
If their laptop specs is not up to scratch, then they have to speak to their company. It is not your problem.

I mentioned this to them, but apparently they are only able to import Excel and text files into the analysis software.

But you're correct in saying it's not my problem, unfortunately I'm a people-pleaser

 

[Pada]

I'm also sitting in the situation where I've got 2m records per month that I have to give a report on for our clients. At this moment I'm exporting a selection of columns to csv, as well as providing them with a short summary.

MySQL Workbench works pretty well for exporting that amount of rows to csv, but you'll need plenty RAM and preferably a Sandy/Ivy Bridge i5 or i7 if you don't want to waste lots of time on single threaded stuff like this.

I'm running an i7 860 with 16GB RAM, with the MySQL dB completely in a RAM drive, making it pretty darn fast when running multiple queries simultaneously. Unfortunately I don't know MSSQL utilities...
Sent from my Lumia 800 using Board Express

 

[rrh]

I'm also sitting in the situation where I've got 2m records per month that I have to give a report on for our clients. At this moment I'm exporting a selection of columns to csv, as well as providing them with a short summary.

If you are preparing a genuine report (i.e. with consolidations etc.) you should consider dropping your data into a star / snowflake schema i.e. as used for data warehousing. The difference in reporting speed is mind-boggling ...

 

[iMORT3rnAL]

I'm also sitting in the situation where I've got 2m records per month that I have to give a report on for our clients. At this moment I'm exporting a selection of columns to csv, as well as providing them with a short summary.

MySQL Workbench works pretty well for exporting that amount of rows to csv, but you'll need plenty RAM and preferably a Sandy/Ivy Bridge i5 or i7 if you don't want to waste lots of time on single threaded stuff like this.

I'm running an i7 860 with 16GB RAM, with the MySQL dB completely in a RAM drive, making it pretty darn fast when running multiple queries simultaneously. Unfortunately I don't know MSSQL utilities...
Sent from my Lumia 800 using Board Express

Working on 2 six-core Xeons and 48Gb memory, no performance issues on my side

In regards with the report that you have to provide, perhaps you should have a look at ZAP BI.

 

[Nod]

Working on 2 six-core Xeons and 48Gb memory, no performance issues on my side

In regards with the report that you have to provide, perhaps you should have a look at ZAP BI.

Pentaho is also a very feature rich BI solution, and free.

 

[iMORT3rnAL]

Pentaho is also a very feature rich BI solution, and free.

It looks interesting, will watch some of the videos tonight.

ZAP was a few bucks, but with out of the box support for our ERP system, it was worth it.

 

[zippy]

if you're on linux/unix, you can use split and specify the amount of lines per file.
http://unixhelp.ed.ac.uk/CGI/man-cgi?split

There are also windows versions of these tools:

http://unxutils.sourceforge.net/

split.exe is in there somewhere

 

[krono9]

I also audit, and if data is too large, just go to client and get the selections or do the audit test/procedures at he client if necessary. Smaller data sets we usually connect through excel using the data connection wizards.