Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

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!
0
ttist25
Asked:
ttist25
1 Solution
 
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
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now