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 |
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
 Posted - 2011-04-15 : 02:07:43
|
| Hi how to import this fat file using bcpHDR|10|T00000000001000| |RXHUB|RXHUBPBM|1111111111|20080212|01010101|FRM|U|20080212|TFHD|FSL101|FORMULARY 101|1|2|U|U|U|0|F|20080206FDT|A|00000370400|03|||||1|FDT|A|00001027124|03|||||1|FDT|A|00001027125|03|||||2|FDT|A|00001060001|03|||||1|FDT|A|00001104027|03|||||1|FDT|A|00001127000|03|||||1|FDT|A|00001207390|03|||||1|FDT|A|00001208590|03|||||1|FDT|A|00001208680|03|||||1|FDT|A|00001208860|03|||||1|FDT|A|00001208930|03|||||1|FDT|A|99429079253|03|||||1|FDT|A|99429079381|03|||||1|FDT|A|99429079412|03|||||1|FDT|A|99999026035|03|||||1|FDT|A|99999026644|03|||||1|FTR|63298TRL|63300and can I check Validation alsoData type is not yet decided.Raghu' S |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-04-15 : 09:38:00
|
| The inly way to import this using bcp is to put make a table with one single wide column (and maybe an identity column as well), like varchar(2000), and insert the entire row into this single column.The you can use sql queries to process the data based on the first column which I assume is a description of what the row contains.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-17 : 14:04:45
|
| I have not been able to figure out a way to bcp in files that have jagged columns even using a format file. So I have always done it the hard way as Lumbago described.There are only two exceptions to the above that I have used:1. If the jaggedness is in the first few rows AND you want to exclude those rows. One of the bcp options allows you to specify the first row to be imported, so you can skip the jagged rows.2. If the jaggedness is in the last few rows AND you know how many rows there are AND you want to exclude the jagged rows. One of the bcp options allows you to specify the number of rows to be imported, so you can skip the jagged rows.Sometimes I have used my other favorite method - preprocessing the files to my liking using a Perl script |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-04-18 : 01:55:14
|
quote:
Originally posted by visakh16
use format file http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html
Is it possible to use a format file when the number of columns in each row varies?- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
|
|
Fat File Data import