| Author |
Topic |
|
aakcse
Aged Yak Warrior
570 Posts |
 Posted - 2010-02-04 : 09:26:28
|
| Hi all,I have a table as below.Col1aaa|bbbb|cccc|dddddddaaa1|bbbb1|cccc1|ddddddd1aaa2|bbbb2|cccc2|ddddddd2aaa3|bbbb3|cccc3|dddd ddd3aaa4|bbbb4|cccc4|dddd ddd4I want output as belowCol1aaa|bbbb|cccc|dddd dddaaa1|bbbb1|cccc1|dddd ddd1aaa2|bbbb2|cccc2|dddd ddd2aaa3|bbbb3|cccc3|dddd ddd3aaa4|bbbb4|cccc4|dddd ddd4Please help me with the update statementupdate table.... where col1 like '%|%'Actually as seen it is a pipe delimited data which needs to be loaded into a table however for last records the data has gone into next line and creating problem for me, cleaning manually is difficult as the no of rec is 60000+. hence I have loaded it into single column and want to clean it with update statement and export and reimport to correct table.Regards,aak |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-04 : 09:33:23
|
| Tryselect replace(replace(col,char(13),''),char(10),' ') from your_tableMadhivananFailing to plan is Planning to fail |
 |
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-02-04 : 09:50:51
|
| Thanks Madhi,I want to write an update or select into a new table, which contains data as belowCol1aaa|bbbb|cccc|dddd dddaaa1|bbbb1|cccc1|dddd ddd1aaa2|bbbb2|cccc2|dddd ddd2aaa3|bbbb3|cccc3|dddd ddd3aaa4|bbbb4|cccc4|dddd ddd4ddd1 etc taken in as a new record in my table. where ever such records exist (based on non existance of |) I want it to be appended to previous line record with one space. |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-02-04 : 10:00:30
|
| is this rt to do like below...select [col1] from (select [col1]from tab where [col1] like '%|%'union allselect ' '+[col1] from tab where [col1] not like '%|%') t where t.[col1] like '%|%' |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-02-04 : 10:16:02
|
| no the above query is not giving me the require out put... |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-02-04 : 10:40:09
|
| Will it help if I add an identity col to this table as below1 aaa|bbbb|cccc|dddd2 ddd3 aaa1|bbbb1|cccc1|dddd4 ddd15 aaa2|bbbb2|cccc2|dddd6 ddd27 aaa3|bbbb3|cccc3|dddd ddd38 aaa4|bbbb4|cccc4|dddd ddd4 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 10:48:20
|
quote:
Originally posted by aakcse
Will it help if I add an identity col to this table as below1 aaa|bbbb|cccc|dddd2 ddd3 aaa1|bbbb1|cccc1|dddd4 ddd15 aaa2|bbbb2|cccc2|dddd6 ddd27 aaa3|bbbb3|cccc3|dddd ddd38 aaa4|bbbb4|cccc4|dddd ddd4
where do you get source data from? is it from another table or flat file? |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-02-04 : 10:59:31
|
| I have loaded from flat file, in to a single column, then I need to re export to flat file and import it correctly into table with all columns pipe delimited |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 11:01:41
|
quote:
Originally posted by aakcse
I have loaded from flat file, in to a single column, then I need to re export to flat file and import it correctly into table with all columns pipe delimited
and you get last values in each row as a separate row due to next line character? |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-02-04 : 11:03:18
|
| in the flat file few of the records got corrupted as seen above data for last column comming in new line and it is treated as new record, which I need to put it right |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 11:05:02
|
quote:
Originally posted by aakcse
in the flat file few of the records got corrupted as seen above data for last column comming in new line and it is treated as new record, which I need to put it right
then isnt it a matter of specifying this inconsistency in delimiter by means of a format file? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 11:05:40
|
| refer below as an examplehttp://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-02-04 : 11:06:46
|
| I have to correct this data at my end, hence I imported it to one table with one column. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 11:25:34
|
quote:
Originally posted by aakcse
I have to correct this data at my end, hence I imported it to one table with one column.
but once you get this to table you wont have any relating column to identify which column value forms part of which row |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2010-02-04 : 12:42:07
|
| I was just getting it to one column all the rows, hmm for the rows with problem, they just do not contains | delimiter those rows should be taken to previous rows last column with one space |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-04 : 13:01:41
|
quote:
Originally posted by aakcse
I was just getting it to one column all the rows, hmm for the rows with problem, they just do not contains | delimiter those rows should be taken to previous rows last column with one space
I dont think you can merge the column relying on the order of insertion to table. I certainly think this needs to be handled at place you do the data import to table |
|
|
|
|
help needed for updating table