jwcolby
jwcolby at colbyconsulting.com
Sun Jan 31 07:17:35 CST 2010
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 > >