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!