November 24, 2014 at 9:25 am
Hello,
I have a requirement to load data from the attached file.
It is a .txt file that has a repetitive structure with 7 attributes per record. Please, see the attached sample for layout.
I am wondering how I could use SSIS or any other SQL tool to load this information into a SQL table, so I can use the information to update tables. The table would have 7 columns:
Name
Address
Zip Code
Phone Number
Email Address
Restaurant Location
Comments
Any design suggestions will be greatly appreciated.
Thanks,
Petr
November 24, 2014 at 10:06 am
vecerda (11/24/2014)
Hello,I have a requirement to load data from the attached file.
It is a .txt file that has a repetitive structure with 7 attributes per record. Please, see the attached sample for layout.
I am wondering how I could use SSIS or any other SQL tool to load this information into a SQL table, so I can use the information to update tables. The table would have 7 columns:
Name
Address
Zip Code
Phone Number
Email Address
Restaurant Location
Comments
Any design suggestions will be greatly appreciated.
Thanks,
Petr
I hope you anonymised the data in that file!
Although the data is somewhat structured, it's not in a standard tabular form and therefore does not lead itself to importing.
The only obvious solution I can think of using SSIS is to use an asynchronous Script Component. These are not for the feint hearted, especially if you are new to SSIS. Here[/url] is a link to get you started on the kind of solution that should work for you. You'll find many more links out there.
The principle is that you read the file row by row, reading each row as a single string.
Then you interrogate each row, in sequence, to decide how to break the data into multiple output columns.
'Asynchronous' here refers to the fact that you will have many more rows going into the Script Component than coming out of it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 27, 2014 at 3:36 am
I would load the file into a staging table with indexing fields for filename and row#
You know that a record starts with NAME and the underscore, so find each row# that has just 'NAME' with '======' in the following row.
You now know the starting and ending rows for each record and can parse the lines as necessary.
Alternatively, In your SSIS replace 'NAME [/r/n]====[/r/n]' with '<RECORD><NAME>' replace '[/r/n]Address[/r/n]=======</r/n]' with '</NAME><ADDRESS>' and repeat the replace process for each heading so that you convert the file to a valid XML format. This will be much easier to work with as it turns it into properly structured data. Don't forget to end the record with </RECORD>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply