SSIS - flat file import
-
I am trying to import a text file that looks like this into a sql server database table using ssis. How can import all of the content in a file like this? See attached.
-
a text file would never look like that - that has formatting that would not be possible on a text file so you are showing us potentially a rendering of the file.
please attach a REAL file so we can advise.
-
Thank you for your response; because the file I have in highly confidential I cannot share it here. However, it has some similarities to the structure of this file. The file I have is a client fee payment report. I have to import everything in the file as is. I'm not sure how to proceed...
-
replace the strings and values with others - as long as the position and sizes are the same it will do - without it not much we can tell.
but regardless of it you will need to load it as a single column into a table and then do the processing in SQL - or you will need to do a C# script to parse the file before loading into its final table.
Native SSIS, without the C# will not do it.
-
Ok. I have been able to import the all of the file's content into a single column using SSIS; parsing the file before loading into its final table is where I'm stuck. I am more proficient with importing parsed data. I do not know c#. I which there some script out there that I could modify to fit my context...
-
The format of the file matters - what kind of file is it?
A PDF or Word document - or is it a TIF or some other image file? For any of those types of files you really need to look at software that is designed to parse images into text. Depending on the type of files there are different methods available - or you can use an OCR process on the image files.
If this is really a text file - with a .txt extension that can be opened in Notepad - then it might be possible to use C# in SSIS to open the file, read the first line - parse the text - read next line, etc.
Again - the file type matters.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Ok, I have attached the sample file. Thanks.
-
have a look at the code below.
basically I've created a temp table (on your case it would be a Permanente one) staging table to load the raw data
these types of tables would require a identity column as well so we can order the rows as they are in the file so we can "look back" on the file contents to gather important details.
then for each record type we split the rawdata into its individual parts - trimming all spaces, replacing thousands separators, converting date columns to proper dates and for numerics also parsing the sign separately (as SQL needs it on the left side of the string, and on your input it is on the "right" side)
drop table if exists #staging;
create table #staging
(importid int identity(1, 1)
, rawdata varchar(2000)
)
insert into #staging
select *
from (values
('')
, ('07/12/20 FTI STATE OF WEALTH HEAD GENERAL''S OFFICE PAGE 1')
, (' 123 EAST LONG STREET - 131ST FLOOR - EVERYWHERE, OHIO 43215-3130')
, ('')
, (' FORWARD CLIENT FEE PAYMENT REPORT CLIENT ID: SC25008 - MR. IAM THE BEST')
, ('')
, ('CHECK AMOUNT : 1,323.44 CHECK DATE : 05-11-23 CHECK # ACH')
, ('******** BATCH # : AP*TAX*20208 ')
, ('')
, ('NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK')
, ('-------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----')
, ('FACCBAN, BICRAIP R & 04-20-23 XXX-XX-1234 18402053 99.39 1.21 .21000 1,843.38 0.00 89')
, ('BURPIR, PISPII D 04-21-23 XXX-XX-5678 15857188 200.00 66.67 .33333 296,515.90 0.00 283')
, ('FUSS, FRYAN R & BIRN 04-24-23 XXX-XX-1234 18900043 18.49 1.52 .33333 161.93 0.00 89')
, ('NUBPI, ANDRIW P 04-24-23 XXX-XX-3472 15125182 175.21 36.80 .21000 2,026.99 0.00 89')
, ('CAW, CRRISPUPRIR J & 04-25-23 XXX-XX-1412 12323688 0.00 0.01 .33333 1,982.10 0.00 998')
, ('BAICRNIR, BRANDY J 04-26-23 XXX-XX-6470 16012783 36.94 12.31 .33333 164.03 0.00 89')
, ('BAICRNIR, BRANDY J 04-27-23 XXX-XX-6470 16012783 243.83 81.28 .33333 164.03 0.00 89')
, ('PUPPIPP JR, GIURGI I 04-27-23 XXX-XX-4446 15763686 518.94 172.98 .33333 4,153.04 0.00 89')
, ('WIPPIABS, JISSICA P 04-27-23 XXX-XX-8413 16919961 47.86 15.95 .33333 933.70 0.00 89')
, ('SAANDIRS, RANDY & PI 04-27-23 XXX-XX-5417 15867037 296.53 62.27 .21000 14,799.42 0.00 89')
, ('BAPP, CARUP A 04-27-23 XXX-XX-2471 16980180 15.58 3.27 .21000 1,489.84 0.00 89')
, ('CINNIDY, CARI S 04-28-23 XXX-XX-5406 15319472 87.14 18.30 .21000 708.55 0.00 89')
, ('BAPPINS, BISS C 04-28-23 XXX-XX-8444 15340341 49.56 16.52 .33333 2,438.81 0.00 89')
, ('BIPPIR, NICUPI 04-28-23 XXX-XX-3439 16585175 0.27 0.09 .33333 28.78 0.00 89')
, ('DARSP, IARP W & ANDR 04-28-23 XXX-XX-8477 2311801021 16654014 FTY 45.86 15.29 .33333 2,367.98 0.00 89')
, ('SUB TOTAL 1,835.60 504.47 ')
, ('')
, ('******** BATCH # : AR*CAT100-6*20166 DIRECT PAYMENTS')
, ('')
, ('NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK')
, ('-------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----')
, ('ACCENT ROOFING, LTD 03-16-23 95109675 6666666666 20483464 TPD 218.28 72.76 .33333 232.72 0.00 289')
, ('SUB TOTAL 218.28 72.76 ')
, ('')
, ('******** BATCH # : AR*PIT100-6*20152 DIRECT PAYMENTS')
, ('')
, ('NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK')
, ('-------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----')
, ('WYATH, BRONDY R 03-02-23 XXX-XX-2560 19262328 102.00- 0.00 0.0 3,025.34 0.00 89')
, ('DORJE-CHANG,LOBSONG 03-02-23 XXX-XX-6598 4312448 103.93 0.00 0.0 1,178.69 0.00 283')
, ('DONG JR, KING C 03-02-23 XXX-XX-7579 17031865 180.00 0.00 0.0 933.55 0.00 289')
, ('IRAHOAD, STEPHANIE M 03-03-23 XXX-XX-2645 6666666666 20243656 TPD 2,238.66 746.21 .33333 278.45 0.00 283')
, ('')
, ('05/12/23 FTI STATE OF WEALTH HEAD GENERAL''S OFFICE PAGE 2')
, (' 123 EAST LONG STREET - 131ST FLOOR - EVERYWHERE, OHIO 43215-3130')
, ('')
, ('SUB TOTAL 2,420.59 746.21 ')
, ('')
, ('******** BATCH # : AR*PIT100-6*20159 DIRECT PAYMENTS')
, ('')
, ('NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK')
, ('-------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----')
, ('DIRSEY, ONIHOMY J & 03-09-23 XXX-XX-8533 16779669 666.00 0.00 0.0 6,731.82 0.00 283')
, ('FARMER, BRAIN T & JA 03-09-23 XXX-XX-2454 17260890 1,070.69 0.00 0.0 3,844.39 0.00 89')
, ('BAKER , JUNIS E & BR 03-09-23 XXX-XX-9036 16787888 334.86 0.00 0.0 55.84 0.00 89')
, ('ATDERSON, JERMEKI N 03-09-23 XXX-XX-7317 6666666666 14416424 REF 218.74 0.00 0.0 29.28 0.00 283')
, ('SUB TOTAL 2,290.29 0.00 ')
, ('')
, ('******** BATCH # : AR*PIT100-6*20173 DIRECT PAYMENTS')
, ('')
, ('NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK')
, ('-------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----')
, ('GILBERT,DALE R & JOD 03-23-23 XXX-XX-7575 1452387 66.00 0.00 0.0 9,946.12 0.00 293')
, ('PERSON, TRACEY R 03-23-23 XXX-XX-6278 12926123 326.00 0.00 0.0 4,488.33 0.00 289')
, ('EVERETT, ROGER L & C 03-23-23 XXX-XX-2875 15037743 105.00 0.00 0.0 1,498.51 0.00 998')
, ('MCCOMAS, JOAN A & JA 03-23-23 XXX-XX-2605 6832161 31.61 0.00 0.0 41.23 0.00 89')
, ('DAVIS, RICHARD D 03-23-23 XXX-XX-6355 12661871 278.00 0.00 0.0 1,364.65 0.00 89')
, ('BULER, KICKY O & JUH 03-23-23 XXX-XX-7816 16529880 114.68 0.00 0.0 2,210.30 0.00 89')
, ('BULER, KICKY O & JUH 03-23-23 XXX-XX-7816 16717951 128.32 0.00 0.0 2,210.30 0.00 89')
, ('PACKER, MICHIAL J & 03-23-23 XXX-XX-8533 6666666666 16779669 REF 141.66 0.00 0.0 6,731.82 0.00 283')
, ('SUB TOTAL 1,191.27 0.00 ')
, ('')
, ('******** BATCH # : AR*PSD100-6*20166 DIRECT PAYMENTS')
, ('')
, ('NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK')
, ('-------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----')
, ('KUMAR, PHILLUP P & M 03-16-23 XXX-XX-9148 6666666666 19307571 REF 1,286.43 0.00 0.0 1,687.48 0.00 89')
, ('SUB TOTAL 1,286.43 0.00 ')
, ('')
, ('******** BATCH # : AR*PSD100-6*20173 DIRECT PAYMENTS')
, ('')
, ('NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK')
, ('-------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----')
, ('PACKER, MICHIAL J & 03-23-23 XXX-XX-8533 15959196 139.77 0.00 0.0 0.00 0.00 999')
, ('PACKER, MICHIAL J & 03-23-23 XXX-XX-8533 16779676 44.11 0.00 0.0 0.00 0.00 999')
, ('PACKER, MICHIAL J & 03-23-23 XXX-XX-8533 6666666666 17896929 REF 438.46 0.00 0.0 6,731.82 0.00 283')
, ('')
, ('')
, ('05/12/23 FTI STATE OF WEALTH HEAD GENERAL''S OFFICE PAGE 3')
, (' 123 EAST LONG STREET - 131ST FLOOR - EVERYWHERE, OHIO 43215-3130')
, ('')
, ('SUB TOTAL 622.34 0.00 ')
, ('')
, ('******** BATCH # : AR*ST100-6*20159 DIRECT PAYMENTS')
, ('')
, ('NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK')
, ('-------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----')
, ('NATURAL HOOFS & SPA 03-07-23 45-050060 6666666666 18467824 REF 27.73 0.00 0.0 37,523.53 0.00 89')
, ('SUB TOTAL 27.73 0.00 ')
, ('')
, ('SC25279 TOTAL 9,892.53 1,323.44 ')
, ('')
, ('')
, ('05/12/23 FTI STATE OF WEALTH HEAD GENERAL''S OFFICE PAGE 4')
, (' 123 EAST LONG STREET - 131ST FLOOR - EVERYWHERE, OHIO 43215-3130')
, ('')
, ('')
) t(rawdata)
;with
-- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
-- 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
-- CHECK AMOUNT : 1,323.44 CHECK DATE : 05-11-23 CHECK # ACH
-- ******** BATCH # : AR*ST100-6*20159 DIRECT PAYMENTS
checks as
(select st1.importid
, convert(decimal(20, 2), replace(rtrim(substring(st1.rawdata, 16, 10)), ',','')) as CheckAmount
, convert(date, substring(st1.rawdata, 45, 8), 10) as CheckDate
, trim(substring(st1.rawdata, 64, 20)) as CheckType
from #staging st1
where st1.rawdata like 'CHECK AMOUNT%'
),
batches as
(select st1.importid
, trim(substring(st1.rawdata, 20, 20)) as BatchID
, trim(substring(st1.rawdata, 40, 20)) as BatchType
from #staging st1
where st1.rawdata like '******** BATCH #%'
),
transactions as
(select st1.importid
-- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
-- 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
-- NAME PDT/ASMT DRL/TIN DTN ACCT P/T AMT FEE FWD% BALANCE FC FEE ADJ DESK
-- -------------------- --------- ------------- ---------- -------- ---- --------------- ------------ ------ ------------ ---------- ----
-- 12345678901234567890 123456789 1234567890123 1234567890 12345678 1234 123456789012345 123456789012 123456 123456789012 12345678901 1234
, trim(substring(st1.rawdata, 1, 20)) as Name
, trim(substring(st1.rawdata, 23, 9)) as PTD_ASMT
, trim(substring(st1.rawdata, 34, 13)) as DRL_TIN
, trim(substring(st1.rawdata, 49, 10)) as DTN
, trim(substring(st1.rawdata, 61, 8)) as ACCT
, trim(substring(st1.rawdata, 71, 4)) as P_T
, trim(substring(st1.rawdata, 77, 15)) as AMT
, trim(substring(st1.rawdata, 94, 12)) as FEE
, trim(substring(st1.rawdata, 108, 6)) as FWD_PCT
, trim(substring(st1.rawdata, 116, 12)) as BALANCE
, trim(substring(st1.rawdata, 130, 11)) as FC_FEE_ADJ
, trim(substring(st1.rawdata, 142, 4)) as DESK
from #staging st1
where substring(st1.rawdata,23, 8) like '%[0-9][0-9]-[0-9][0-9]-[0-9][0-9]%'
)
select chk.CheckAmount
, chk.CheckDate
, chk.CheckType
, btch.BatchID
, btch.BatchType
, t1.importid
, t1.Name
, convert(date, t1.PTD_ASMT, 10) as PTD_ASMT
, t1.DRL_TIN
, t1.DTN
, t1.ACCT
, t1.P_T
, convert(decimal(20, 2), replace(replace(t1.AMT, ',', ''), '-', '')) * case when right(t1.AMT, 1) = '-' then -1 else 1 end as AMT
, convert(decimal(20, 2), replace(replace(t1.FEE, ',', ''), '-', '')) * case when right(t1.FEE, 1) = '-' then -1 else 1 end as FEE
, convert(decimal(20, 5), replace(replace(t1.FWD_PCT, ',', ''), '-', '')) * case when right(t1.FWD_PCT, 1) = '-' then -1 else 1 end as FWD_PCT
, convert(decimal(20, 2), replace(replace(t1.BALANCE, ',', ''), '-', '')) * case when right(t1.BALANCE, 1) = '-' then -1 else 1 end as BALANCE
, convert(decimal(20, 2), replace(replace(t1.FC_FEE_ADJ, ',', ''), '-', '')) * case when right(t1.AMT, 1) = '-' then -1 else 1 end as FC_FEE_ADJ
, t1.DESK
from transactions t1
outer apply (select top 1 *
from batches b1
where b1.importid < t1.importid
order by b1.importid desc
) btch
outer apply (select top 1 *
from checks b1
where b1.importid < t1.importid
order by b1.importid desc
) chk
--where t1.AMT like '%-%' -
Thanks so much. Can this be done in SSIS? Like the splits
-
How can I automate this? Like if I had multiple of such files to import. Will I have to just follow the same process?
- EM2 wrote:
Thanks so much. Can this be done in SSIS? Like the splits
Yes. In SSIS, you have access to C#, allowing very sophisticated levels of manipulation to be accomplished. But you need to be fairly adept at coding to achieve this.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - EM2 wrote:
How can I automate this? Like if I had multiple of such files to import. Will I have to just follow the same process?
yes. a standard pattern for this is based on a table containing the filename being processed, the identity column I mentioned, and then a column for the raw data.
in SSIS you would have the following
loop container - loop through the filesystem and get list of files to process
-- Data Flow - read file, append filename to row, insert into staging table
-- execute sql to process from staging to final table - at same time log into a audit/process table the filename that was processed (so you don't process it again)
depending on the requirements the SQL process could happen AFTER all files were loaded instead of within the loop for each file.
plenty of examples on net on how to do this in SSIS.
-
.
-
Is this an export or report created out of a mainframe? This is strangely similar to a situation I had to work on a while back.
Reading this thread, the issues may be that the various files are inconsistent. Line 1 can be ignored in file A, but it contains required data in file B, and so forth. If you can be guaranteed that every file you need to import will be exactly the same, then you can create a repeatable automated process.
My solution was to go to the developers of this report and ask them to create a set of reports that were all in the format I specified. Of course, it would have taken them 43 years to do this, so I learned Mumps and did it myself!
Is getting a better set of files a possibility??
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
I am not sure how to move from the staging table to the final table (physical table). this is something I should know but I am not sure. I am researching to see if I can find a way to get this done. I think once I can get the input from the staging table to the final table then I can work on automating it.
I welcome and appreciate your help with this...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply