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?

Improve company productivity with a Business Account.Sign Up

x
 
John_VidmarConnect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.