January 26, 2010 at 11:48 pm
Hi guys!!
I need your help in resolving this issue:
Description:
------------------
I have a Table with fixed number of columns , say 10 columns. All are of NVARCHAR. Now, my SP will have to use "BULK INSERT" statment and populate that table from a DAT file.
Issue:
----------
The issue is the DAT file. The source system provides us DAT file that contains 9 columns some times and 10 columns other times. The source system wont inform us the number of columns. Thus i cannot use a format file to load the data.
How do i calculate the number of columns in the input DAT file so that i can change my FORMATFILE with that number of columns or is there any other workaround to import the DAT file irrespective of the number of columns in the DAT file into a table with 10 columns
Please guide me on how to resolve this!!!
Thanks in advance!!
P.S: I searched in SSC for similar kind of issues, but my search is futile! 🙁
January 26, 2010 at 11:58 pm
You can create an intermidate table with 11 columns.
Import all the data into 1st column which you can define as max size of varchar.
Split the data into other column using charindex using the column seperator in your input file
keep track of a counter which u can update each time you insert into a column.
January 27, 2010 at 12:06 am
Thanks vyas for the reply.
My DAT file is very huge. Contians more than 5 million rows. So do u think i can import whole of the data into that one column ??
Can this be done using a FORMATFILE?? I mean, a single format file that imports data from a DAT file to a 10 column table irrespective of the number of column in the DAT file? I am sure that NEVER more than 10 columns will be present in the DAT file...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply