March 6, 2009 at 4:14 am
I have a TXT file that im importing.
It has 4 columns but no type of column seperator, i.e. Comma, Semicolon etc...
Below is what one row with the column headings look like:
FIM ISIN_CODE SEDL DESCRIPTION
ABA.L GB0000130756 0013075 ALBA ORD 10P
I cannot seem to import all the columns, I have tried setting the format to 'Fixed Width' and this does not capture all the columns.
Any ideas please??!!!
March 6, 2009 at 4:28 am
These look like variable-length records with variable numbers of spaces between the fields ... a bit of a nightmare.
Is it true to say that if two or more consecutive spaces appear, that is a column delimiter?
You might find it easier to write something which changes the format of the input file such that multiple spaces are replaced by a single comma and then feed the resulting file into SSIS.
Phil
March 6, 2009 at 4:49 am
Thanks for the info.
I really want to avoid touching the file! (Long story)
The really problem is that, there are 4 columns and no delimiters and I need only to import column 1.
What other options do I have?
March 6, 2009 at 5:44 am
You need some sort of delimiter or you will find it difficult to tell what you have. I was going to suggest searching on spaces and delimiting yourself on them (char 32), but look at the last row:
ABA.L GB0000130756 0013075 ALBA ORD 10P
So do you have four columns, or six? This method probably won't work since your last entry has two spaces in its description. When I get something like this, I usually tell whoever to fix the file and throw it back at them...normally see something like it from the mainframe bluehairs.
March 6, 2009 at 6:50 am
There is 4 columns altogether.
Problem is the people we get the file from will charge us to make this change and thats somthing "we" (The man above) does not want.....
For the moment I have used Ragged Right and then set the column widths manually. Not ideal but for now its working...
March 6, 2009 at 7:16 am
The fact that you need only column 1 makes this a lot easier, I think.
Just import the whole record into a single column on SQL Server and then run an UPDATE query to search for the first occurrence of two consecutive space characters and delete everything to the right of them.
Phil
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply