Mark Breen
marklbreen at gmail.com
Sun Jan 31 04:24:05 CST 2010
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 > >