Skipping or adjusting the end columns in a fixed length file


  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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Define your needs for 'dynamically adjust'.
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?
pvsbandiAuthor Commented:
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.
Jim HornMicrosoft SQL Server Data DudeCommented:
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>
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pvsbandiAuthor Commented:
No, file is too big to weed them out :(
Can you define the last column as varchar?  maybe Null?
pvsbandiAuthor Commented:
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?
If you don't need it, then ignore it.  The ease of doing the import depends on what method you are using (as mentioned by other experts in this thread).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pvsbandiAuthor Commented:
Thanks! We did the same..just ignored that field and that was the solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.