[dba-SQLServer] BCP of fixed width files

jwcolby jwcolby at colbyconsulting.com
Mon Feb 1 08:12:41 CST 2010


If you would ban fixed width, I would vote for you for King.

John W. Colby
www.ColbyConsulting.com


Mark Breen wrote:
> Hi John,
> 
> Yes, this is a great idea, then you can pluck positions 8 - 13 and 47 - 49
> and you are good to go, very useful, I have done that plenty of times also.
> 
> 
> If I were King, I would ban fixed width.
> 
> Mark
> 
> 
> 
> On 31 January 2010 13:17, jwcolby <jwcolby at colbyconsulting.com> wrote:
> 
>> I do have the column definitions.
>>
>> In fact I pretty much don't care about most of the columns.  It occurred to
>> me last night that I can
>> just define the columns I care about which makes it just a few minutes
>> work.  Sometimes you can't
>> see the table for the fields, so to speak.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> Mark Breen wrote:
>>> Hello John,
>>>
>>> Sorry to hear that, do you have the column mappings?  If not, how can you
>>> interrogate the fixed width file, if you then...
>>>
>>> if it is only 80 columns can you not
>>>
>>> a) make that table manually, then use it to generate the format file
>>> (probably 80 minutes work)
>>> b) if you have an existing similar table then modify that, add the new
>>> columns, and use that to generate a format file.
>>>
>>> Good luck,
>>>
>>> Mark
>>>
>>>
>>>
>>> On 30 January 2010 21:42, jwcolby <jwcolby at colbyconsulting.com> wrote:
>>>
>>>> Mark,
>>>>
>>>> Sigh.
>>>>
>>>> The problem here is that I don't have the table I intend to import to.
>>  Or
>>>> more correctly I thought
>>>> I did but they changed the format.  Dropped one field for sure, and who
>>>> knows what else.  I found a
>>>> way to cause BCP to export the spec if you have the table, but if you
>> don't
>>>> have the table
>>>> (importing something new...) then you have to do it once manually in
>> order
>>>> to get that table the
>>>> first time.  This thing is 670 characters wide and about 80 or so
>> fields.
>>>> This sucks so bad!
>>>>
>>>> John W. Colby
>>>> www.ColbyConsulting.com
>>>>
>>>>
>>>> Mark Breen wrote:
>>>>> Hello John,
>>>>>
>>>>> the way that I learnt to create a format file for bcp use was to
>> perform
>>>> an
>>>>> export from the table that i intend to import to, and then once I have
>>>> the
>>>>> export working well, I could save that as a format file.  Once you have
>>>> the
>>>>> format file, the import will work well also.  Did you teach me that
>> trick
>>>> in
>>>>> 1997/8 ?
>>>>>
>>>>> Having said all that, I still encourage you to spend eight hours with
>>>> SSIS,
>>>>> you are performing ETL, and SSIS is the ETL tool that MS supply.  It is
>>>>> designed for volumes like you are using.  SSIS must be as fast as bcp,
>>>> just
>>>>> a better GUI and much more control.  You can run scripts on a column by
>>>>> column or record by record basis.  *Extract* from your text file,
>>>> *transform
>>>>> * the data and then when you have clean data *load* it into SQL Server
>>>>> tables.
>>>>>
>>>>> With your knowledge of your particular data, and your programming
>>>> abilities,
>>>>> you could have SSIS really singing in a week or two, and I think you
>>>> would
>>>>> not look back.
>>>>>
>>>>> Additionally, SSIS and ETL are very marketable toolsets to be expert
>> in.
>>>>>  Will not hurt the CV.
>>>>>
>>>>> Once you have an SSIS package, you can call that from an sproc, and
>>>> probably
>>>>> even more sophisticated ways to manipulate it.
>>>>>
>>>>> PS, I have used fixed width files in the past and hate them, once
>> change
>>>> in
>>>>> column seven and you have to re-do every column.  For even small
>> tables,
>>>> I
>>>>> think it is worth while building a string concatenation tool in Excel,
>> so
>>>>> that I just name the columns and sizes and Excel automatically
>> calculates
>>>>> the start and end points of the data.  For you, this must be a must.
>>  If
>>>> you
>>>>> have not already discovered that, you will soon enough.  Remapping by
>>>> hand
>>>>> the second time is no fun.
>>>>>
>>>>> Thanks
>>>>>
>>>>> Mark
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On 30 January 2010 14:04, jwcolby <jwcolby at colbyconsulting.com> wrote:
>>>>>
>>>>>> Amigos,
>>>>>>
>>>>>> I have to import a fixed width file periodically.  It is fairly
>> complex,
>>>>>> containing perhaps 50-70
>>>>>> fields.  I performed this import one time "manually" using the import
>>>>>> wizard, but manually defining
>>>>>> the field names / widths every time is not happening!
>>>>>>
>>>>>> So, I have a "template" table that has the field names and sizes (all
>>>>>> nvarchar() ).  I have a stored
>>>>>> procedure I created last night which will create that table for me.
>>>>>>
>>>>>> I routinely use BCP to import and export "CSV" type files (delimited),
>>>> and
>>>>>> I have a C# class which
>>>>>> allows me to quickly and easily set up a stored procedure object, set
>> up
>>>>>> parameters, get back error
>>>>>> codes etc.
>>>>>>
>>>>>> What I need to do (it it is even possible) is to learn how to use BCP
>>>> with
>>>>>> a fixed width file.  My
>>>>>> understanding is that you use a format file in which you define the
>>>> fields,
>>>>>> data types etc. and then
>>>>>> you pass that to BCP.
>>>>>>
>>>>>> Does anyone out there do this thing, any words of advice.  Any
>>>> alternatives
>>>>>> which I as a SQL Server
>>>>>> novice could possibly make happen?
>>>>>>
>>>>>> TIA for any assistance.
>>>>>>
>>>>>> --
>>>>>> John W. Colby
>>>>>> www.ColbyConsulting.com
>>>>>> _______________________________________________
>>>>>> dba-SQLServer mailing list
>>>>>> dba-SQLServer at databaseadvisors.com
>>>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>>>>> http://www.databaseadvisors.com
>>>>>>
>>>>>>
>>>>> _______________________________________________
>>>>> dba-SQLServer mailing list
>>>>> dba-SQLServer at databaseadvisors.com
>>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>>>> http://www.databaseadvisors.com
>>>>>
>>>>>
>>>> _______________________________________________
>>>> dba-SQLServer mailing list
>>>> dba-SQLServer at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>>> http://www.databaseadvisors.com
>>>>
>>>>
>>> _______________________________________________
>>> dba-SQLServer mailing list
>>> dba-SQLServer at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>> http://www.databaseadvisors.com
>>>
>>>
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 



More information about the dba-SQLServer mailing list