Data Mask

Question

Data Mask

Richard Sabbara

Ten Centuries

Points: 1382
More actions
March 28, 2007 at 5:41 am

#173030

I have a several text files that I have to import and I have a document that tells me where each column name begins and ends. I want to this via a DTS, I am just not sure how, any suggestions? In Access it would be a data mask, the problem that I am facing right now is that I have to upload so many of these files and they are huge that it's just easier to do the whole thing in SQL.
Thank you in advance for your help.

Richard

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i do that kind of import a lot...probably better ways to do it, but this works really well for me;

the example below is typical of what I use...the table "FINAL_LAYOUT" does not mirrors the actual production table, but has many of the same columns......since it has many of the same columns, it will be easier for the following steps to insert items that do not exist in PROD, but are new in FINAL_LAYOUT, and also update PROD from FINAL_LAYOUT as well.

--this solution assumes you are importing a fixed width file (not delimited)

--and you know that chars1-12 is one value, 13-21 are another, etc.

--temp table to import the entire file into a table for manipulation and historical purposes.

CREATE TABLE BULKTABLE(RAWDATA VARCHAR(2000) )

--final table to import the results into

CREATE TABLE FINAL_LAYOUT (

LOAN_NO varchar(12),

APPL_DT varchar(8),

ISSUER varchar(4),

PROG varchar(4),

)

--use bulk insert ot get the data

BULK INSERT BULKTABLE FROM 'C:\YOURPATH\IMP1441.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW = 1

)

--insert the data into this table

--if you have the definitions in excel, you can make a formula to construct the

--insert, ie ="SUBSTRING(RAWDATA," & A1 & "," & A2 & "),"

INSERT INTO FINAL_LAYOUT

SELECT

SUBSTRING(RAWDATA,1,12),

SUBSTRING(RAWDATA,13,8),

SUBSTRING(RAWDATA,21,4),

SUBSTRING(RAWDATA,25,4),

&nbsp

from BULKTABLE

--from here, with the values in columns in the table, you can INSERT OR UPDATE into the production tables.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Richard Sabbara Ten Centuries Points: 1382 More actions · Answer 2

Lowell,

While I have not done what you are suggesting before, it seems as if I can attempt it. My question for you is in your code:

BULK INSERT BULKTABLE FROM 'C:\YOURPATH\IMP1441.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW = 1

)

The field and row TERMINATOR are declared, the text file that I have received does not appear to use these, will that mess anything up?

Richard

Lowell SSC Guru Points: 323518 More actions · Answer 3

the row terminator \n would be a carriageReturnLineFeed; i assume that the file does have that, right? (opening it in notepad shows more than one line?)so you can get multiple rows? the \r is just a line feed, which you might use if the source file came from unix, and you might want to look at BOL or google for other bulk insert examples, as BOL is kinda brief on real world examples.

if the comma never is encountered in your file, then no problem, but you could change it to a tilde ~ or something that you know would NEVER appear in the data for the FEILDTERMINATOR; that's what you would use to insert a comma delimited file into multiple columns, but i think your data is fixed width.....

if your file came with ten lines of descriptive information before teh actual data started, you might change the FIRSTROW to another number...FIRSTROW =10 ....... if the first row always contained the column names, you might change it to FIRSTROW =2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!