February 13, 2017 at 10:03 am
Hello, all. This problem has been driving me nuts. Something Excel does so wonderfully seems impossible in SQL or SSIS.
Basically, I have a flat file in .txt format that contains a data set. It is a single column of data in blocks of 58 rows where the first 58 is composed of the header names.
How on earth can I output this to either a task down stream or to another flat file in a 58 column format of n+1 rows that will be used to populate a SQL table?
Thanx!
February 14, 2017 at 12:40 pm
ckirmser - Monday, February 13, 2017 10:03 AMHello, all. This problem has been driving me nuts. Something Excel does so wonderfully seems impossible in SQL or SSIS.
Basically, I have a flat file in .txt format that contains a data set. It is a single column of data in blocks of 58 rows where the first 58 is composed of the header names.
How on earth can I output this to either a task down stream or to another flat file in a 58 column format of n+1 rows that will be used to populate a SQL table?
Thanx!
It's not going to be easy, mind you, but it's not impossible. There are a number of ways to go about it. With SSIS, you could have a Data Flow task that reads all rows of information as character data, where you add a Row Number to each record, and then derive an overall record number, computed as that RowNumber minus 1, then divided by 58, (that becomes your record number), and then you establish a field number by using ROW_NUMBER() OVER(PARTITION BY RecordNumber ORDER BY RowNumber) AS FieldNumber and it all flows into a staging table. You then have another Data Flow task that runs a query to pivot that data into records and populate the table. At that point, each data value is associated with a row number and field number, and row number 0 is your header columns. Here's a query that just demonstrates creating a quick 174 numbers using a recursive CTE just to code it quickly (not for performance), and then derive the row number and field number:
WITH NUMBERS AS (
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM NUMBERS
WHERE N < 175
)
SELECT X.N, X.RowNumber, ROW_NUMBER() OVER(PARTITION BY X.RowNumber ORDER BY X.N) AS FieldNumber
FROM (
SELECT N.N, ((N.N - 1) / 58) AS RowNumber
FROM NUMBERS AS N
) AS X
ORDER BY X.N
OPTION (MAXRECURSION 0);
That might help give you some ideas. Another alternative is to code an algorithm in VBA within Excel, or use a Script Task in SSIS and use VB or C#. And as that could work, you could as easily develop a VBScript that could be run from a command prompt.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 6:09 am
sgmunson - Tuesday, February 14, 2017 12:40 PMIt's not going to be easy, mind you, but it's not impossible.
Indeed!
This came about from a need to import an XML file into SQL using SSIS. Unfortunately, no XSD was provided, there was none inline and, because of multiple namespaces, SSIS couldn't generate one.
Thing is, Excel would read the file like a champ and, as SSIS is just another way to manipulate spreadsheets - by the same manufacturer, even - I couldn't see why SSIS was having the problem.
Thanx for your help, sgmunson! I shall give it a try.
February 15, 2017 at 1:32 pm
ckirmser - Wednesday, February 15, 2017 6:09 AMsgmunson - Tuesday, February 14, 2017 12:40 PMIt's not going to be easy, mind you, but it's not impossible.Indeed!
This came about from a need to import an XML file into SQL using SSIS. Unfortunately, no XSD was provided, there was none inline and, because of multiple namespaces, SSIS couldn't generate one.
Thing is, Excel would read the file like a champ and, as SSIS is just another way to manipulate spreadsheets - by the same manufacturer, even - I couldn't see why SSIS was having the problem.
Thanx for your help, sgmunson! I shall give it a try.
Have you considered using a Script Task in SSIS to do the import into Excel? You can use VB as the language for your script and that will make it easier to manipulate Excel's object model. You can basically write a VB program for that script task that opens Excel and reads the XML file, and then you could program all the actions you want Excel to take to "pretty it up", or whatever else you need, including saving the file to a network share. Admittedly, Excel would have to be installed on your SSIS box, but that's a pretty small price to pay.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 2:15 pm
sgmunson - Wednesday, February 15, 2017 1:32 PMckirmser - Wednesday, February 15, 2017 6:09 AMsgmunson - Tuesday, February 14, 2017 12:40 PMIt's not going to be easy, mind you, but it's not impossible.Indeed!
This came about from a need to import an XML file into SQL using SSIS. Unfortunately, no XSD was provided, there was none inline and, because of multiple namespaces, SSIS couldn't generate one.
Thing is, Excel would read the file like a champ and, as SSIS is just another way to manipulate spreadsheets - by the same manufacturer, even - I couldn't see why SSIS was having the problem.
Thanx for your help, sgmunson! I shall give it a try.Have you considered using a Script Task in SSIS to do the import into Excel? You can use VB as the language for your script and that will make it easier to manipulate Excel's object model. You can basically write a VB program for that script task that opens Excel and reads the XML file, and then you could program all the actions you want Excel to take to "pretty it up", or whatever else you need, including saving the file to a network share. Admittedly, Excel would have to be installed on your SSIS box, but that's a pretty small price to pay.
That was a suggestion made by someone in another forum, but, as you pointed out, it needs Office on the SSIS server. Unfortunately, my employer ain't 'bout t'do that...
Too bad, it sounded like a neat idea.
But, I have finally had my thick skull cracked open and wrapped my juicy grey matter around a block of C# code that manages to read the original XML file. I plugged that into an SSIS Source Script Component and it does the job.
Thanx for the reply!
February 15, 2017 at 2:50 pm
Now all you have to worry about are changes to the structure of the file that would break your C#. Honestly, I'd ditch the C# and re-write it in VB. The object model in combination with Visual Basic is almost self-documenting. C# tends to read like Arabic and is actually harder to maintain. And I'd push the boss to realize what he's losing out on by not having Excel installed on the SSIS box. That's really a seriously self-limiting option.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 7:16 am
I know VB better, but it's not installed on our server. Actually, neither is C#, but I can get to it inside an SSIS task by using a script component. Unfortunately, I can't switch to Vbasic. I can read C# OK. The problem comes with little tricks in C# that come from experience. Also, the C# seems to have a capability that I've never experienced in VBasic. It can select only certain tags in an XML file and specify the child nodes of those tags. That removes the huge problem of multiple namespaces that I was having. So, since my data in the XML is all stored in a block tagged "Row," I can tell C# to only search the Row items and then grab the specific child node of each Row for my data. That solution fit my problem like a skin.
Using C#, though, is not a big problem as it's only a handful of lines. The format shouldn't change, since this is a standardized report. Potentially, all they might change is to add or delete one or more columns and that'll be easy to correct for.
As for getting Excel on the box; well, not really an option. It's not my boss who's the hurdle, it's our IT dept. They are very restrictive in what they will do or allow.
February 16, 2017 at 8:54 am
ckirmser - Thursday, February 16, 2017 7:16 AMI know VB better, but it's not installed on our server. Actually, neither is C#, but I can get to it inside an SSIS task by using a script component. Unfortunately, I can't switch to Vbasic. I can read C# OK. The problem comes with little tricks in C# that come from experience. Also, the C# seems to have a capability that I've never experienced in VBasic. It can select only certain tags in an XML file and specify the child nodes of those tags. That removes the huge problem of multiple namespaces that I was having. So, since my data in the XML is all stored in a block tagged "Row," I can tell C# to only search the Row items and then grab the specific child node of each Row for my data. That solution fit my problem like a skin.Using C#, though, is not a big problem as it's only a handful of lines. The format shouldn't change, since this is a standardized report. Potentially, all they might change is to add or delete one or more columns and that'll be easy to correct for.
As for getting Excel on the box; well, not really an option. It's not my boss who's the hurdle, it's our IT dept. They are very restrictive in what they will do or allow.
Tell them it will be equally "restrictive" in what you can accomplish.... 😉
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 11:18 am
sgmunson - Thursday, February 16, 2017 8:54 AMTell them it will be equally "restrictive" in what you can accomplish.... 😉
Oh, if only it were that simple - for more than just my current employer...
February 16, 2017 at 11:23 am
ckirmser - Thursday, February 16, 2017 11:18 AMsgmunson - Thursday, February 16, 2017 8:54 AMTell them it will be equally "restrictive" in what you can accomplish.... 😉Oh, if only it were that simple - for more than just my current employer...
That's what emoticons are for.... do you have those turned on? I used the "wink" one at the end of my post (you can simulate using a semicolon and a right parenthesis)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply