Export SQL Table(only one table) data to multiple csv worksheets using SSIS

Question

Export SQL Table(only one table) data to multiple csv worksheets using SSIS

Janaa

Say Hey Kid

Points: 707
More actions
November 18, 2010 at 12:04 am

#241401

Hi All,
Warm Greetings!!!
I'm new to SSIS, just 2weeks old, am facing very much trouble to export data from sql table to multiple csv worksheets(sheet1,sheet2,.....)
In SQL table i've more than 65536 records (for eg., 70000 records), while exporting table to csv only 65536 records got exported in sheet 1 remaining 4464 records must come in sheet 2 but it wasn't available.
I used DataFlowTask -> OLEDB Source & FlatFileDestination tools.
Pls help me out to resolve this issue, am awaiting for the reply

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think if you create the CSV, it would have ALL the records...it's just earlier versions of excel would only DISPLAY 65536 rows.

open the file up in an enhanced raw text editor like EditPlus, instead of Excel and you can see all 70K rows exist.

i personally have lots of data extraxts with more rows than that in a csv....

you could also upgrade to a newer version of excel to see all the rows as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Janaa Say Hey Kid Points: 707 More actions · Answer 2

Actually, i've manually created one .csv file in C:\Temp\ and assigned the column names which i need and from OLEDB source getting the data from Table and passing it to FlatFileDestination.

In FlatFileDestination, i've called FlatFileConnectionManager, and selected Delimited format

In it's general tab --> FileName - i browsed my C:\Temp\test.csv file

and in FlatFileDestination i called FlatFileConnectionManager and click ok.

by this way am facing that records issue with sheets.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

So you're still facing the issue with records not showing in your sheets? (I am a bit confused by your reply, hence my question).

If you export all your data to a .csv file, then all the data should be there.

Don't open your .csv with Excel, but with a decent text editor. (Textpad, UltraEdit, Notepad++ or even the built-in windows Wordpad or notepad).

It is Excel that is having troubles with the number of rows, not the .csv file.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

madan.kapil-1069442 SSC-Addicted Points: 420 More actions · Answer 4

Koel - I've a requirement to run 2 separate sql commands and post both result sets in separate csv tabs (sheet1.. sheet2) within a single csv file.. Can you or anyone else in the forum help me out on this.

Thanks

Kapil

David Burrows SSC Guru Points: 65142 More actions · Answer 5

madan.kapil-1069442 (1/15/2014)
Koel - I've a requirement to run 2 separate sql commands and post both result sets in separate csv tabs (sheet1.. sheet2) within a single csv file.. Can you or anyone else in the forum help me out on this.
Thanks
Kapil

You cannot have two sheets in a csv file, a csv file is one sheet.

You can only have multiple sheets in a workbook (.xls or .xlsx)

Far away is close at hand in the images of elsewhere.
Anon.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

It's quite advanced, especially given your experience with the product, but this article is relevant.

Also, as others have mentioned, please stop referring to multi-tab CSV files: they do not and cannot exist.

I believe that you are looking for an export to Excel (XLS or XLSX) which does, of course, support multiple tabs ('worksheets').

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

madan.kapil-1069442 SSC-Addicted Points: 420 More actions · Answer 7

Thanks David & Phil.. But I was actually looking for a multi-tab csv since I am able to add worksheets/ tabs to a csv file manually. so thought that it is possible via automation as well.. But since its not possible, we are going ahead with multi-file logic now.

Thanks again.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

madan.kapil-1069442 (1/16/2014)
Thanks David & Phil.. But I was actually looking for a multi-tab csv since I am able to add worksheets/ tabs to a csv file manually.

A csv file is not an Excel file!

It's not because you can open a csv file in Excel, that they are the same.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246962 More actions · Answer 9

But I was actually looking for a multi-tab csv since I am able to add worksheets/ tabs to a csv file manually

Please post an example of one of these.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

madan.kapil-1069442 SSC-Addicted Points: 420 More actions · Answer 10

Phil - As rightly pointed out by Koen that just because I am able to open csv with excel, csv is not an excel ! May be I am able to add tabs to it by opening it in excel.

David Burrows SSC Guru Points: 65142 More actions · Answer 11

madan.kapil-1069442 (1/16/2014)
May be I am able to add tabs to it by opening it in excel.

When you open a csv file in Excel, Excel loads it as a sheet in a new workbook. Therefore you can then add further sheets to the workbook and save the workbook as a xls or xlsx Excel file. If you try to save the workbook as a csv, Excel will warn you that only one of the sheets can be saved as a csv file.

Far away is close at hand in the images of elsewhere.
Anon.