Trouble Importing Flat File Into MS SQL 2008

Good morning!

I've got a very large pipe delimited flat file that I'm having difficulty importing into SQL.

Here's the situation:

The file is about 650MB (a few million records
The file is pipe delimited
I don't know the max lengths of the values in the fields
I tried importing the file into a table in temdb with all of the fields set to varchar(max) and I still get truncation errors in SQL.
I'm getting truncation errors on a specific column when I tell the import/export wizard to ignore truncation and even when I tell it to ignore the entire column

I've been able to open the file in a program called "Stat Transfer" (something we purchased along with a program called STATA - an SPSS competitor).  That reads about 4,400 records but, it seems to think those are all the records in the file.  

I suspect there's some kind of odd character around the end of that 4,400th record that's causing these issues.  Typically, I would search around in the text file and manually edit it to fix something like that but, this file is too big to open in notepad or notepad++.  

So what to do?  Any ideas as to how I might go about opening or editing the source file?  

Thanks in advance!
LVL 1
ttist25Asked:
Who is Participating?

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

x
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.

John_VidmarCommented:
There are free file-splitting utilities.  Break the file into smaller pieces, and then open the smaller file using Notepad++ to find out what's going on around that 4,400th record.
0

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
ttist25Author Commented:
Thanks John.  

Your method would definitely work so I'm awarding points but, I ended up using something called "File Query" by AgileSoftware.  It's a free trial and it was pretty useful so I may actually pay for it.  It opened the file right up and loaded all of the records.  I was able to query min.max lengths etc.  I'm still working to get it into MSSQL but, this worked well for opening and allowing edits.  

Thanks!
0
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 2008

From novice to tech pro — start learning today.