Mark Breen
marklbreen at gmail.com
Mon Feb 1 04:38:55 CST 2010
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 > >