July 5, 2017 at 5:51 am
Can we convert large Text file to Execl file ?
Then we need to split into multiple excel having max size of 1 million.
If any kind of converter scripts (.net, batch file, shell script, job script) to convert .txt to xlsx is much appriciated
July 5, 2017 at 6:13 am
Yes, you can. No need to use Batch files/Shell/.Net, you can use a Dataflow.
In very simple terms: Use the Flat File Connection to connect to your text file, and an Excel Connection for your Excel File(s). Create a Data Flow task and use the Flat File Source, then create a Conditional Split to separate your data for your different files/worksheets. Finally create an Excel Destination for each File/worksheet and link the appropriate split data from your condition split to the Excel Destination.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 5, 2017 at 6:17 am
Thanks for the response. Working in SSIS hitting performance. So is there any alternative to directly convert .txt to .xlsx
July 5, 2017 at 6:23 am
subratnayak09 - Wednesday, July 5, 2017 6:17 AMThanks for the response. Working in SSIS hitting performance. So is there any alternative to directly convert .txt to .xlsx
Depends how the format of the text file is. You could just open the file in Excel and Save As a .xlsx file.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 5, 2017 at 6:42 am
subratnayak09 - Wednesday, July 5, 2017 6:17 AMThanks for the response. Working in SSIS hitting performance. So is there any alternative to directly convert .txt to .xlsx
Did you at least try doing some research for yourself on this? What were your findings?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 5, 2017 at 6:42 am
Thom A - Wednesday, July 5, 2017 6:23 AMsubratnayak09 - Wednesday, July 5, 2017 6:17 AMThanks for the response. Working in SSIS hitting performance. So is there any alternative to directly convert .txt to .xlsxDepends how the format of the text file is. You could just open the file in Excel and Save As a .xlsx file.
Speaking of format in the text file, what is the field layout of the text file? I guess my other question is why are you trying to import a multi-million row file into Excel? What IS the end game here?
--Jeff Moden
July 5, 2017 at 6:46 am
Everyone should note that this question has previously been asked, and answered ... in a slightly different form ... here.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 5, 2017 at 6:49 am
Jeff Moden - Wednesday, July 5, 2017 6:42 AMThom A - Wednesday, July 5, 2017 6:23 AMsubratnayak09 - Wednesday, July 5, 2017 6:17 AMThanks for the response. Working in SSIS hitting performance. So is there any alternative to directly convert .txt to .xlsxDepends how the format of the text file is. You could just open the file in Excel and Save As a .xlsx file.
Speaking of format in the text file, what is the field layout of the text file? I guess my other question is why are you trying to import a multi-million row file into Excel? What IS the end game here?
Considering, as well, that you just asked "how" to do it, and then within minutes were experiencing performance, this says you knew what you were doing before?
What is it you are doing at the moment if you're having performance issues. Perhaps the question you meant to ask is not "how do i do this", but "How do I improve the efficiency of this task? What I am doing is ..... The performance issues seem to be around when the task is doing x."
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 5, 2017 at 6:51 am
Phil Parkin - Wednesday, July 5, 2017 6:46 AMEveryone should note that this question has previously been asked, and answered ... in a slightly different form ... here.
If it's separate worksheets, perhaps SSRS would be a better tool. You can set groups on pages, which, in Excel, creates a new Worksheet.
Use SSIS to import the text to SQL, and then use an Execute SQL task to start the schedule.
Of course, if this IS the same file that wants 6 million rows, Excel is not the right choice n the first place.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 5, 2017 at 7:09 am
Jeff Moden - Wednesday, July 5, 2017 6:42 AMThom A - Wednesday, July 5, 2017 6:23 AMsubratnayak09 - Wednesday, July 5, 2017 6:17 AMThanks for the response. Working in SSIS hitting performance. So is there any alternative to directly convert .txt to .xlsxDepends how the format of the text file is. You could just open the file in Excel and Save As a .xlsx file.
Speaking of format in the text file, what is the field layout of the text file? I guess my other question is why are you trying to import a multi-million row file into Excel? What IS the end game here?
That is the nonsense requirement from my client and with SSIS only.
If I go for loading multi million records to excel performance will hit . So I can go for text file loading and convert to excel with any mechanism.
July 5, 2017 at 7:28 am
subratnayak09 - Wednesday, July 5, 2017 7:09 AMThat is the nonsense requirement from my client and with SSIS only.
If I go for loading multi million records to excel performance will hit . So I can go for text file loading and convert to excel with any mechanism.
You can't just automagically make a text file into an excel file. If you have a 6 million row text file, and need it to be in Excel, that's going to have some overhead.
I have no idea how wide your datatable or how large the data stored in a column is, but I would imagine that the file is not small in terms of that type of file. I just created an xlsx file that was 1,000,000 rows deep, and 8 columns wide. Each cell was populated with simply 'AAA' and that came out at about 2.5MB. Say your datatable is 20 columns wide, and averaging about 6-7 characters per cell, I wouldn't be surprised if the file is at least 10MB. Times that by ten and that's 60MB.That doesn't should like a lot, but when converting from one format to the other, that's a lot of work to.do
Plus, on a separate note, I'm pretty sure if you're running a 32bit version of Excel, like myself, you will never be able to open that file.
Edit: Here I go fixing typos again!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 5, 2017 at 7:29 am
It seems simple enough. Load the file into one huge table that also has an identity column. Take subsets of a million rows at a time and export them to a new worksheet on the spreadsheet file.
As for performance issues, you're moving millions of rows. Depending on the width of the data, there may always be a performance issue. Best to ask the client why they have such a ridiculous requirement because no one in their right mind is going to effectively use Excel to analyze multiple million row worksheets in Excel to do much of anything.
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply