row terminator in Bulk Insert SQL Server 2005

i get an extract daily via ftp that was a text file that is now compressed and having a .gz extension and is now binary. my bulk insert was working without a ROWTERMINATOR when it  was a .txt file, but since its been .gz I get
Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 33. Verify that the field terminator and row terminator are specified correctly.

I added a ROWTERMINATOR = '\r\n' to no avail. The file is an extract from Oracle. I can import the file into access no problem. Any help is appreciated.

Thanks,

Jim
LVL 18
jmoss111Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jmoss111Connect With a Mentor Author Commented:
i downloaded todos.exe and ran that against my files... problem solved
0
 
knightEknightCommented:
.gz is a compressed file -- you need to un-compress it first using the gzip command like this:

  gzip.exe  -d  myfile.gz
0
 
knightEknightCommented:
Run the above from a command prompt in the same folder where your file resides.  If you don't have gzip.exe you can download it from several places - just google.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jmoss111Author Commented:
yes i do uncompress the file and i have the problem with the extracted .txt file and not by trying to load the .gz file

0
 
knightEknightCommented:
I'm guessing then that the file comes from a unix/linux environment.  Have you tried setting ROWTERMINATOR='\n'   or  ROWTERMINATOR='\r'  ?
0
 
jmoss111Author Commented:
the file did come from unix/Oracle and i tried ROWTERMINATOR = '\r\n'  and ROWTERMINATOR = '\n' and ROWTERMINATOR = '\r'
0
 
jmoss111Author Commented:
i found a workable solution
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.