Mark Breen
marklbreen at gmail.com
Sat Jan 30 13:56:48 CST 2010
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 > >