February 22, 2011 at 7:53 am
I need to extract 10 columns from a flat file (txt)that are found in differents location in the file header, the file that have 148 columns, and I need to import them via scripting into a table in SQL Server 2008, any help will be appreciated.
Thnks
February 22, 2011 at 8:31 am
Are there delimiters? Really you need to just find the logic that will allow you to find the fields. Are they are certain positions? Or do they come after a certain phrase? Once you have the logic, then there are functions that can easily split up the header line(s) into sections. Substring, charindex (In T-SQL), etc.
February 22, 2011 at 8:54 am
yes , the files are {tab} delimiter
February 22, 2011 at 8:58 am
what I'm trying to implement is to be able to load certain columns from a flat file which is tab delimiter dinamically, I don't need all the columns from the file and some files can have different set of columns
February 22, 2011 at 12:06 pm
navecomp (2/22/2011)
what I'm trying to implement is to be able to load certain columns from a flat file which is tab delimiter dinamically, I don't need all the columns from the file and some files can have different set of columns
You can use a script task that uses an OLE DB .NET connection to the text file. That way you can write an "ordinary" SQL query to your text file.
Check out www.connectionstrings.com, navigate to text file to get you started.
(one downside is that you'll have to create an .INI file so that the OLE DB Provider - MS JET 4.0 - can interprete the file correctly)
edit: another downside is that this approach is slower than reading the flat file in with SSIS and parse it in the dataflow. The advantage is that it is more flexibel.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply