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 > >