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 |
|
Zippeh
Starting Member
9 Posts |
 Posted - 2010-02-18 : 11:01:07
|
Hi,I have a file which needs to be imported regularly into a table on SQL Server 2005. The data elements in this file are of a fixed width, and I have create the following Format File in order to use BULK INSERT.9.0111 SQLCHAR 0 16 "" 1 balance Latin1_General_BIN2 SQLCHAR 0 50 "" 2 reference Latin1_General_BIN3 SQLCHAR 0 5 "" 3 fund_code Latin1_General_BIN4 SQLCHAR 0 50 "" 4 first_name Latin1_General_BIN5 SQLCHAR 0 100 "" 5 last_name Latin1_General_BIN6 SQLCHAR 0 50 "" 6 addr_1 Latin1_General_BIN7 SQLCHAR 0 50 "" 7 addr_2 Latin1_General_BIN8 SQLCHAR 0 50 "" 8 addr_3 Latin1_General_BIN9 SQLCHAR 0 50 "" 9 addr_4 Latin1_General_BIN10 SQLCHAR 0 50 "" 10 postcode Latin1_General_BIN11 SQLCHAR 0 301 "\r\n" 0 other Latin1_General_BIN Now, the problem I'm having is not all the fields are filled towards the end of the row. Some might not have a postcode, addr_4 for example. Because of this, the newline character is come across sooner and therefore it messes up my import. Does anyone know of any trickery so that when it reaches the newline character I can force it to start a new record and not carry on the existing one until it fills the required space in the column?Thanks! |
|
|
|
|
|
Fixed width fields that aren't fixed...