Skipping or adjusting the end columns in a fixed length file
Hi,
I have a fixed length file which i get on a monthly basis to load into a database.
It has many fields and an end field called "End_Col".
This End_Col field is defined as 20 characters, but may be 20 or less than 20 in length.
Can we dynamically adjust its length and load into the table? Or, can we ignore this field altogether?
I have a fixed length file which i get on a monthly basis to load into a database.
It has many fields and an end field called "End_Col".
This End_Col field is defined as 20 characters, but may be 20 or less than 20 in length.
Can we dynamically adjust its length and load into the table? Or, can we ignore this field altogether?
ASKER
Hello Jim,
The issue is not on the database side. It is on the Fixed Length file side.
We get this fixed length file, but the last field may or may not have 20 characters all the time. Rest of the fields have a fixed length.
The issue is not on the database side. It is on the Fixed Length file side.
We get this fixed length file, but the last field may or may not have 20 characters all the time. Rest of the fields have a fixed length.
Hmm. Can you import as fixed width, then weed out all of the return keys? Char(13) + CHR(10).
<Busy day today, so if another expert wants to jump in here go ahead>
<Busy day today, so if another expert wants to jump in here go ahead>
ASKER
No, file is too big to weed them out :(
Can you define the last column as varchar? maybe Null?
ASKER
Hi Mark,
Database side, yes, we could work it out somehow.
But how do i deal with it on the text file side? Can we ignore this last field?
Database side, yes, we could work it out somehow.
But how do i deal with it on the text file side? Can we ignore this last field?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! We did the same..just ignored that field and that was the solution.
Assuming fixed length, if the SQL Server column that it's stored in is a varchar, then it'll auto-magically optimize for the value(s).
If you're concerned about spaces, you could always throw in a LTRIM(RTRIM(column_name)) in there.
How are you ETL-ing this file?