Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aakcse
Aged Yak Warrior
570 Posts |
 Posted - 2012-07-09 : 06:38:29
|
I have file which is imported to table raw, now the raw table has Id column and raw_col which contains entire line of data file imported to it.Now this raw_col has data in fixed width, I want to copy this data to proper table, please help me in using bulk import or any for doing so, Also I want to ignore data after #, as that is commenttcp 1/tcp # TCP Port tcp 1/udp # TCP Port comp 2/tcp # Management comp 2/udp # Management comp 3/tcp # Compression -Neil-Neil |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-09 : 07:28:00
|
| create a view on the raw table excluding the id column.bulk insert into the viewbulk insert mydb..myview from 'c:\myfile'theninsert mytbl select left(mycol,nnn) -- as it's fixed widthorinsert mytbl select left(mycol,charindex('#',mycol)-1) -- if it's not quite fixed widthlooks like you actually wantinsert mytbl select rtrim(substring(mycol,1,20)),substring(mycol,11,20), ...==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-09 : 09:39:40
|
| does all your data rows have # and part after?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2012-07-09 : 11:39:21
|
| some lines have #followed by few words, that is comment, I need not include that in to the proper target table. Also now I have another file which has two delimiter found in my data file, Few lines are "space" delimited and other are "tab", I need to handle this as well, assuming data does not have space or tab, I can create a SP or a script to do this-Neil |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-09 : 11:54:58
|
quote:
Originally posted by aakcse
some lines have #followed by few words, that is comment, I need not include that in to the proper target table. Also now I have another file which has two delimiter found in my data file, Few lines are "space" delimited and other are "tab", I need to handle this as well, assuming data does not have space or tab, I can create a SP or a script to do this-Neil
then you've to add a small modification like below to avoid it breaking for ones without #..left(mycol,charindex('#',mycol+'#')-1)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2012-07-09 : 13:22:20
|
| Thanks Visakh,How to deal with two delimiter in a file, Idea is to separate lines which has space as delimiter with the tab and clean them.or is there any better way to deal with them.-Neil |
|
|
|
|
|
|
|
|
Bulk loading