July 9, 2003 at 1:06 pm
Does anyone have any experience with using bcp or BULK INSERT to import fixed length files. I have created a format file and tried various combinations of lengths, terminators, and so on but can't get it to work.
I have BCPd and BULK INSERTed using format files for character deliminited before, so I have a good idea how the process should work. I don't create the file, however, and it is too huge to modify (it contains the country's tax information for each local jurisdiction).
BOL does not provide much useful inromation either.
Any assistance would be welcome.
July 9, 2003 at 11:58 pm
I had the same problem. An easy way to overcome it was import the file into a temp table with a single column (Varchar(8000))
From there, you select out the data and split it by length.
If there is a better way I would love to know as this is not the best.
Fortunatly, mine gets run once a month so the extra 5 minutes it takes is not a problem.
BTW: I did not use BCP but BULK INSERT from within SQL. This gives you a bit more control.
Have you looked at the File Format version for BCP? Might be able to use this. I have not.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 14, 2003 at 11:21 am
To whoever will read on this topic:
Despite claims from numerous sources that bcp and BULK INSERT can use format files to import fixed width columns, this does not seem to be so unless there is no return at the end of each line. For example, if I specify a single column import of a width of three, this goes in just fine: ABCDEFGHI as
ABC
DEF
GHI
but a file that looks like the above will not go in properly. I have never seen a fixed width file that looks like the one above. Instead each record is repeated on a new line.
My conclusion is that if you have such a file to import, you must use DTS, which handles it rather well. I had hoped to get the speed of bcp, but as it turned out, DTS is plenty fast enough.
July 21, 2003 at 7:07 am
I am on SLQ7 SP4 and I can import fixed length text files with or without cr/lf.
Can you post table structure and sample data?
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply