facing a 50% line limit when loading text file

Question

facing a 50% line limit when loading text file

Gimli son of Gloin

Ten Centuries

Points: 1263
More actions
July 14, 2004 at 8:56 pm

#162920

I am loading medical claims data from a text file into a new SQL Server database and new table. No matter how large the file (370,000 or 274,000 lines), the Enterprise load wizard only loads 50% of the lines. So for the first file (370,000 lines) I get 50% which is 185,000 lines loaded and for the 2nd file (274,000 lines) I get only 137,000 lines (50%).
Why is this happening and how can I get all the lines loaded? I have 12 of these files and their size ranges from 30,000 lines to 3,300,000 lines.
The text files are in the National Standard Format. Each medical claim record has a record identifier. I'm loading the entire 316 character length of the line into a single column. I tried char(316) first and then varchar(316) and then text data types but they all only load 50% of the text file.
In case you are wondering why I'm loading the entire line into one column, the data fields in the National Standard Format are not delimited nor fixed so I will write a cursor to parse it into its separate data columns.
BTW, if anyone knows of a utility out there that can do this let me know. It will save me lots of time.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

warey SSC Enthusiast Points: 162 More actions · Answer 1

If the wizard always loads 50% of the file no matter the size. Concatenate the file onto itself. so your 370K file becomes 740K rows 50% of that will be the whole thing right?

Gimli son of Gloin Ten Centuries Points: 1263 More actions · Answer 2

I'll try that, though it would be nice to have a better method since I'll be getting these files on a monthly basis.

I also need to try the bulk insert statement and see if that'll load the entire file.

Alex Wilson-148544 SSC-Addicted Points: 440 More actions · Answer 3

i've used to use the BCP program to import data and never had a problem, aslong as I didnt get the row and column terminators confused - which i do often, as im usually bulk loading in the middle of hte night and nursing it thru manually... but thats besides the point.

since then, I have used DTS for it, and indeed for any ETL process.

simply - create a file connection, create a server connection and add a new transformation between the two.

I have successfully imported between 26 Million and 100 Mill records this way.

-- Alex

Gimli son of Gloin Ten Centuries Points: 1263 More actions · Answer 4

I have been using DTS and I'm only getting 50% of the file every time for every file. However, I tried the bulk insert statement with a format file and I get the entire file loaded. Strange!?