September 13, 2010 at 9:36 am
Greetings again Super Experts:
I have a file that is converted from mainframe to a .txt file.
After this conversion, I then import the file to SQL Server 2005 db using dts.
Mostly works fine.
There are a couple of issues that I wanted to know from you if it is possible to fix these issues during import.
1, In order to align each column header with appropriate column value, an extra dummy column header was inserted at the extreme right side of the page.
During after importing the data successully, I manually delete this extra dummy column header, then I add an identity seed to the table.
My question is whether it is possible to use a script to both delete this dummy column header as well as insert an identity seed all in one go?
We perform this data import once a month.
Worst case scenario, we do this manually once a month but using a script to make these 2 changes during import to SQL Server would be great.
Any ideas is greatly appreciated.
Many thanks
September 13, 2010 at 10:16 am
If my understanding of your question is correct, you can write a simple script to delete that dummy colum and then add an identity column for the seed. Post a portion of your source file and the results you want if my understanding is wrong.
September 13, 2010 at 10:36 am
Hi John,
Thanks very much for the prompt response.
I don't quite understand what you mean by source file.
If you mean whether I have a script to do what I am asking for, I don't have one.
If you mean the ddl (for insert, select, or create statements), here are some.
Please let me know if I am way off.
Yes, what you described is what I mean.
Thanks again.
CREATE TABLE [dbo].[MTHLY_XREF](
[MTHLY_XREF_NBR] [int] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](50) NULL,
[REL] [nvarchar](50) NULL,
[ESTNO] [nvarchar](50) NULL,
[DTFILD] [nvarchar](50) NULL,
[PUB] [nvarchar](50) NULL,
[TYPEOFDOCUMENT] [nvarchar](50) NULL,
[BTYP] [nvarchar](50) NULL,
[BKNO] [nvarchar](50) NULL,
[PGNO] [nvarchar](50) NULL,
[DISP] [nvarchar](50) NULL,
[DISPDT] [nvarchar](50) NULL,
CONSTRAINT [PK__MTHLY_X__0EA330E9] PRIMARY KEY CLUSTERED
(
[MTHLY_XREF_NBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
****************
Select
***************
SELECT [MTHLY_XREF_NBR]
,[NAME]
,[REL]
,[ESTNO]
,[DTFILD]
,[PUB]
,[TYPEOFDOCUMENT]
,[BTYP]
,[BKNO]
,[PGNO]
,[DISP]
,[DISPDT]
FROM [PCourt].[dbo].[MTHLY_XREF]
*************
insert
*************
INSERT INTO [Pourt].[dbo].[MTHLY_XREF]
([NAME]
,[REL]
,[ESTNO]
,[DTFILD]
,[PUB]
,[TYPEOFDOCUMENT]
,[BTYP]
,[BKNO]
,[PGNO]
,[DISP]
,[DISPDT])
VALUES
(<NAME, nvarchar(50),>
,<REL, nvarchar(50),>
,<ESTNO, nvarchar(50),>
,<DTFILD, nvarchar(50),>
,<PUB, nvarchar(50),>
,<TYPEOFDOCUMENT, nvarchar(50),>
,<BTYP, nvarchar(50),>
,<BKNO, nvarchar(50),>
,<PGNO, nvarchar(50),>
,<DISP, nvarchar(50),>
,<DISPDT, nvarchar(50),>)
September 13, 2010 at 10:59 am
Are you really using DTS instead of SSIS?
It would really help to have SAMPLE DATA. Without that, it's really hard to determine the best course of action.
It sounds like you're including your header data as part of your imported data. My suggestion is to simply skip over the header records altogether. While it helps to have the headers as the first line of the file, it's not necessary. It's probably more work to massage the file into a format that fits what you are looking for than to work with the file as it is.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 13, 2010 at 11:30 am
Here is sample data.
IDNAMERELESTATE NODATE FILLEDPUBTYPE OF DOCUMENTBOOK TYPEBOOK NOPAGE NODISPOSITIONDISP DATE
1NULLNULL 090903 N PETITION TO TERM GDNSHP MINOR MIN 2962146GRANTED 90903
2ALBA, LOUIS R DEC 147112 080191 Y APPN YEAR'S SUPPORT MIN 1325109GRANTED 91991
3MARIADEC 209553 021508 N WILL PROBATED WILL 10966ORD REC 32508
4JONESDEC 021508 N PET PROB SOL FORM MIN 3620322GRANTED 32508
5kELLYDEC 032708 N OATH EXTR MIN 3624277ORD REC 40108
6kELLYDEC 040108 N LETTS TEST SF LT 208206ORD REC 40108
September 15, 2010 at 10:39 am
Any help on this, please?
September 15, 2010 at 12:42 pm
simflex-897410 (9/13/2010)
Here is sample data.
IDNAMERELESTATE NODATE FILLEDPUBTYPE OF DOCUMENTBOOK TYPEBOOK NOPAGE NODISPOSITIONDISP DATE1NULLNULL 090903 N PETITION TO TERM GDNSHP MINOR MIN 2962146GRANTED 90903
2ALBA, LOUIS R DEC 147112 080191 Y APPN YEAR'S SUPPORT MIN 1325109GRANTED 91991
3MARIADEC 209553 021508 N WILL PROBATED WILL 10966ORD REC 32508
4JONESDEC 021508 N PET PROB SOL FORM MIN 3620322GRANTED 32508
5kELLYDEC 032708 N OATH EXTR MIN 3624277ORD REC 40108
6kELLYDEC 040108 N LETTS TEST SF LT 208206ORD REC 40108
I think we would need to see the first few lines of your raw text file, aliasing any sensitive data, including column width specifications
September 15, 2010 at 1:03 pm
Hi,
This data is exported to me from the mainframe by someone.
To ensure that I got each row of data *completely*, I got it to preview window and copied it from there.
Again, because of the data wrapping on a textfile, the only way to ensure I get each row, I had to *pretend* I was exporting it till I get it to the preview window.
If you still insist on the raw, untouched data straight from the .txt file, then is there a way to either attach here or email?
" AKA WILLIAM L GIBBS ""DEC ""188588 ""SEE- -" ""GIBBS, WILLIAM LEE, AKA "" "" "" "" "" "º
" BENTON, HENRY SCOTT ""DEC ""185993 ""110701 ""N ""PET OPEN SAFE DEPOSIT BOX ""MIN ""2660 ""315 ""GRANTED ""111301"º
" BOLTON, PRESIOUS SIERRA ""MIN ""204549 ""091806 ""N ""PET TEMP LETTS GDN "" "" """GRANTED ""111306"º
" "" "" ""111506 ""N ""TEMP LETTS GDN "" ""0006 ""111 ""ORD REC ""111506"º
" "" "" ""111506 ""N ""REC OF GDNSHP/CONSER ""MIN ""3431 ""178 ""ORD REC ""111506"º
" "" "" ""031808 ""N ""PET TERM GDNSHIP / CONSRVTRSHP "" "" """ORD REC ""040708"º
" "" "" ""032408 ""N ""CAVEAT "" "" """ORD REC ""040708"º
" "" "" ""040708 ""N ""ORD TRANSFERRING GUARDIANSHIP "" "" """ORD REC ""040708"º
" BRIGHT, JOHNNIE MALDON, 8107 ""TEST ""208088 ""SEE- - "" ""WILLS FILED FOR SAFEKEEPING "" "" "" "" "" "º
" BROOKS, RYAN PHILLIP ""MIN ""182514 ""010501 ""N ""PET NAT GDN NOT BE LEGAL GPRO ""MIN ""2542 ""284 ""GRANTED ""021501"º
" BROWN, EDNA MAPP, 8405 ""TEST ""208088 ""SEE- - "" ""WILLS FILED FOR SAFEKEEPING "" "" "" "" "" "º
" BROWN, LARVOSKAY WA'HEED ""MIN ""209120 ""010708 ""N ""PET TEMP LETTS GDN "" "" """GRANTED ""010708"º
" "" "" ""010708 ""N ""TEMP LETTS GDN ""LGDN ""0010 ""170 ""ORD REC ""010708"º
" "" "" ""010708 ""N ""REC OF GDNSHP/CONSER ""MIN ""3584 ""349 ""ORD REC ""010708"º
" BRYANT, TAMMY ""DEC ""201731 ""SEE- - "" ""GROSS, MARIE CHRISTINE G, AKA "" "" "" "" "" "º
" BUTTS, LUCILLE S, 8110 ""TEST ""208088 ""SEE- - "" ""WILLS FILED FOR SAFEKEEPING "" "" "" "" "" "º
" CALDWELL, GLORIA MARTIN ""DEC ""191420 ""040903 ""N ""PET PROB SOL FORM ""MIN ""2889 ""144 ""GRANTED ""040903"º
" "" "" ""040903 ""N ""WILL PROBATED ""WILL ""0911 ""179 ""ORD REC ""040903"º
" "" "" ""040903 ""N ""OATH EXTR ""MIN ""2888 ""331 ""ORD REC ""040903"º
" "" "" ""040903 ""N ""LETTS TEST SF ""LT ""0185 ""206 ""ORD REC ""040903"º
" CALDWELL, NAJEEM IBN SULAYMAN ""MIN ""183676 ""SEE- - "" ""CALDWELL, NAJEEM IBN SULAYMAN "" "" "" "" "" "º
" CHANDLER, MILDRED REUDEN ""DEC ""186737 ""013102 ""N ""PET OPEN SAFE DEPOSIT BOX ""MIN ""2698 ""017 ""GRANTED ""013102"º
During the import process, we include column headers, remove the " and then remove the last column that looks like a small zero (o) and its data (which is also like small zeroes).
Hope this makes sense.
thank you very much.
September 15, 2010 at 4:42 pm
One approach you could try is to first BULK INSERT the file into a single column staging table. Then using the SUBSTRING function you can resolve each individual column, cleaning the file in the process. By the way, you should check these values against your actual file. This staging table will serve as a conduit for further processing. One advantage of this method is that you don't have to worry about the irregular header. You may be able to adapt this approach to suit your requirements. What do you think?
IF NOT OBJECT_ID('tempdb.dbo.#stage', 'U') IS NULL DROP TABLE #stage;
CREATE TABLE #stage(BulkColumn nvarchar(255));
BULK INSERT #stage FROM 'C:\SampleFile.txt'
WITH
(
FIRSTROW = 2, TABLOCK
);
SELECT
SUBSTRING(BulkColumn, 3, 31) AS NAME
,SUBSTRING(BulkColumn, 42, 6) AS REL
,SUBSTRING(BulkColumn, 58, 8) AS ESTNO
,SUBSTRING(BulkColumn, 74, 7) AS DTFILD
,SUBSTRING(BulkColumn, 90, 3) AS PUB
,SUBSTRING(BulkColumn, 98, 32) AS TYPEOFDOCUMENT
,SUBSTRING(BulkColumn, 138, 6) AS BTYP
,SUBSTRING(BulkColumn, 154, 5) AS BKNO
,SUBSTRING(BulkColumn, 162, 4) AS PGNO
,SUBSTRING(BulkColumn, 170, 21) AS DISP
,SUBSTRING(BulkColumn, 194, 6) AS DISPDT
FROM #stage;
September 16, 2010 at 11:03 am
Thank you very much.
I was able to modify the code to give me the column headers and values I am after.
I do want to make this more permanent though and automate it.
I can handle the automation bit but I would like to handle this as an insert into actual table.
In other words, once a month, we delete entire records in current table and using dts, re-import the data.
I want to be able to use this method, or similar to help accomplish precisely what this code is doing now.
How possible can I do this?
Thanks alot for your assistance.
IF NOT OBJECT_ID('Judges.dbo.#stage', 'U') IS NULL DROP TABLE #stage;
CREATE TABLE #stage(BulkColumn nvarchar(255));
BULK INSERT #stage FROM 'C:\sample.txt'
WITH
(
FIRSTROW = 2, TABLOCK
);
SELECT
SUBSTRING(BulkColumn, 3, 31) AS NAME
,SUBSTRING(BulkColumn,37, 6) AS REL
,SUBSTRING(BulkColumn, 46, 8) AS ESTNO
,SUBSTRING(BulkColumn, 57, 6) AS DTFILD
,SUBSTRING(BulkColumn, 68, 3) AS PUB
,SUBSTRING(BulkColumn, 74, 32) AS TYPEOFDOCUMENT
,SUBSTRING(BulkColumn, 109, 6) AS BTYP
,SUBSTRING(BulkColumn, 118, 4) AS BKNO
,SUBSTRING(BulkColumn, 126, 4) AS PGNO
,SUBSTRING(BulkColumn, 133, 21) AS DISP
,SUBSTRING(BulkColumn, 157, 6) AS DISPDT
FROM #stage;
September 16, 2010 at 12:40 pm
Can't you just add an INSERT statement to your process? Something like
INSERT [MTHLY_XREF]
(
[NAME]
,[REL]
,[ESTNO]
,[DTFILD]
,[PUB]
,[TYPEOFDOCUMENT]
,[BTYP]
,[BKNO]
,[PGNO]
,[DISP]
,[DISPDT]
)
SELECT
SUBSTRING(BulkColumn, 3, 31) AS NAME
,SUBSTRING(BulkColumn,37, 6) AS REL
,SUBSTRING(BulkColumn, 46, 8) AS ESTNO
,SUBSTRING(BulkColumn, 57, 6) AS DTFILD
,SUBSTRING(BulkColumn, 68, 3) AS PUB
,SUBSTRING(BulkColumn, 74, 32) AS TYPEOFDOCUMENT
,SUBSTRING(BulkColumn, 109, 6) AS BTYP
,SUBSTRING(BulkColumn, 118, 4) AS BKNO
,SUBSTRING(BulkColumn, 126, 4) AS PGNO
,SUBSTRING(BulkColumn, 133, 21) AS DISP
,SUBSTRING(BulkColumn, 157, 6) AS DISPDT
FROM #stage;
September 17, 2010 at 9:35 am
Thank you very much.
I really didn't nitially understand ALL the fun things your solution was doing until now.
I was able to append the insert bit.
Again, thanks for all your help.
September 17, 2010 at 11:20 am
And thanks for the feedback, much appreciated:-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply