January 30, 2008 at 1:20 pm
We have been importing data from flat files into SQL Server 2000 tables for quite a while without any major problems. The field delimiters are specified as being the semi-colon (;) and the text delimiters are specified as being the upright bar (|). The reason for the text delimiters is that some of the data may have embedded text formatting. The data is actually extracted from an Oracle DB and during the extract we 'translate' certain character sequences to spaces or other known character sequences. We enclose this text within an opening and a closing upright bar (|) character.
All is working well until we get to use SQL Server 2005 Imports. Either I am blind or just can't see the proper stuff, but it appears that in the import set ups for 2005 there is no such thing as a TEXT DELIMITER.
Am I missing something, or has the super guru at Microsoft decided to make life miserable for those of us having to live with day to day problems of creating data for SQL Server?
My question is: Where and how is the old text delimiter hidden in the new import?
BTW I have tried using BCP with and without format files, as well as BULK INSERT. All without success......
January 30, 2008 at 1:34 pm
You will have to create a format file for the bulk insert. Inside the format file you will have to specifiy that after each semi-colon a "|" must exist, for text based columns.
Additinonally the other option is to import the data via SSMS and you have the option of choosing a text qualifier. Just right click on a database --> tasks --> import. SSMS package are also bulk inserted.
January 30, 2008 at 6:30 pm
Adam
I appreciate your suggestion. Unfortunately adding the | character to the format file parameter for the field simply makes the field delimiter a 2 character sequence of ;|
Using the import ssis/ssms wizard does not present the opportunity to select a text delimiter in the 2005 version. Unless of course I missed this somewhere along the parameter streams.
In the SQL Server 2000 version of DTS one had the opportunty to select a FIELD delimiter as well as a TEXT DELIMITER. This allowed one to present fields for input that had line formatting characters embedded.
January 30, 2008 at 7:58 pm
I appreciate your suggestion. Unfortunately adding the | character to the format file parameter for the field simply makes the field delimiter a 2 character sequence of ;|
There is no method in bcp or bulk insert to create a text qualifier. You would have to tell the bulk insert to look for ;| to begin all text columns and |; to close all text columns, with the exception of the last column which would end in just a pipe If you didnt do this, your data would appear as |somedata1|,|somedata2|.
Using the import ssis/ssms wizard does not present the opportunity to select a text delimiter in the 2005 version. Unless of course I missed this somewhere along the parameter streams.
Yes it does. You can find this in the instruction I gave you. The very first screen for the importing of a "flat file" has a "text qualifier" option. This is what you are calling a "text delimter." You can then set your column delimeter to ";" under the columns tab.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply