Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
brubakerbr
Yak Posting Veteran
53 Posts |
 Posted - 2013-08-27 : 15:14:53
|
| Our HR department has an automated export from our SAP system in the form of a flat file. The information in the flat file looks like so. G/L Account 4544000 Recruiting/Job Search Company Code 0020-------------------------- | Posting Date| LC amnt||------------------------| | 01/01/2013 | 406.25 || 02/01/2013 | 283.33 || 03/21/2013 |1,517.18 |--------------------------G/L Account 4544000 Recruiting/Job Search Company Code 0020-------------------------- | Posting Date| LC amnt||------------------------| | 05/01/2013 | 406.25 || 06/01/2013 | 283.33 || 07/21/2013 |1,517.18 |--------------------------When I look at the data in the SSIS Flat File Source Connection all of the information is in a single column. I have tried to use the Delimiter set to bar but it will not separate the data, I assume due to the nonessential information at the top and middle of the file. I need to remove the data at the top and middle and then have the Date and Total split into two separate columns. The goal of this is to separate the data so that I can get a single SUM for the running year. Year Total 2013 $5123.25I have tried to do this in SSIS but I cant seem to separate the columns or remove the data. I want to avoid a script task as I am not familiar with the code or operation of that component. Any assistance would be appreciated.Brian D. BrubakerBusiness Intelligence AnalystViega LLC |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-08-27 : 15:34:54
|
| In this case - it might just be easier to load the data into a single column and use SQL Server to parse it out. You will need a good string split utility to parse the fields out - but it shouldn't be hard to find one.First step - load file into staging table.Second step - parse staging table into multiple values - send to destination table |
 |
|
|
|
|
|
Flat File Import: Remove Data