jwcolby
jwcolby at colbyconsulting.com
Sat Jan 30 15:42:11 CST 2010
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 > >