Importing an ascii file of 1500 columns -> need to split

[Keesinho]

Hi,

I have the following problem. I need to import an ascii file
containing 1000 records and 1500(!) columnns into sql server. 1500 clearly exceeds SQLS's limit.

Therefore it was my idea to split the file into 2 files of about 750 columns. My unique identifier (how I can recognize a record) lies in the first 8 columns.

Is there anyone who knows how to do this? I'm almost to the point of manually changing each record, which is of course a very stoopid plan ;-)

 

[jnicho02]

Keesinho,
[tab]You are right about the limit on number of columns. For 6.5 this is 250 and SS7 it is 1024 (close, but no cigar!).

You can create two tables as you have said, then bcp in two goes. There is no problem with creating two format files which both specify the id column. A bit like...

[tt]7.0

9

1[tab]SQLCHAR[tab]0[tab]11[tab]","[tab]1[tab]au_id
2[tab]SQLCHAR[tab]0[tab]40[tab]","[tab]2[tab]au_lname
3[tab]SQLCHAR[tab]0[tab]20[tab]","[tab]3[tab]au_fname
4[tab]SQLCHAR[tab]0[tab]12[tab]","[tab]4[tab]phone
5[tab]SQLCHAR[tab]0[tab]40[tab]","[tab]0[tab]address
6[tab]SQLCHAR[tab]0[tab]20[tab]","[tab]0[tab]city
7[tab]SQLCHAR[tab]0[tab]2[tab]","[tab]0[tab]state
8[tab]SQLCHAR[tab]0[tab]5[tab]","[tab]0[tab]zip
9[tab]SQLCHAR[tab]0[tab]1[tab]"\r\n"[tab]0[tab]contract[/tt]

and

[tt]7.0

9

1[tab]SQLCHAR[tab]0[tab]11[tab]","[tab]1[tab]au_id
2[tab]SQLCHAR[tab]0[tab]40[tab]","[tab]0[tab]au_lname
3[tab]SQLCHAR[tab]0[tab]20[tab]","[tab]0[tab]au_fname
4[tab]SQLCHAR[tab]0[tab]12[tab]","[tab]0[tab]phone
5[tab]SQLCHAR[tab]0[tab]40[tab]","[tab]1[tab]address
6[tab]SQLCHAR[tab]0[tab]20[tab]","[tab]2[tab]city
7[tab]SQLCHAR[tab]0[tab]2[tab]","[tab]3[tab]state
8[tab]SQLCHAR[tab]0[tab]5[tab]","[tab]4[tab]zip
9[tab]SQLCHAR[tab]0[tab]1[tab]"\r\n"[tab]5[tab]contract[/tt]

The zeros in the target column number (2nd from last column in the format file) tells bcp to ignore that column.

You might want to find some way of grouping the fields into logical tables so that when queried you mostly use a single table.

 

[Keesinho]

Thx! Only one thing for me to find out now: how am I gonna split my huge 1500 col file into two 750 col files?

Are there tools available for doing this easily?

Keesinho [sig][/sig]

 

[jnicho02]

You run the two bcp's on top of the same input file. Note that both format files talk about 9 columns, yet the first format loads 4, and the second loads 6.

Sorry..the 2nd one was slightly wrong it should have been..
[tt]7.0

9

1 SQLCHAR 0 11 "," 1 au_id
2 SQLCHAR 0 40 "," 0 au_lname
3 SQLCHAR 0 20 "," 0 au_fname
4 SQLCHAR 0 12 "," 0 phone
5 SQLCHAR 0 40 "," 2 address
6 SQLCHAR 0 20 "," 3 city
7 SQLCHAR 0 2 "," 4 state
8 SQLCHAR 0 5 "," 5 zip
9 SQLCHAR 0 1 "\r\n" 6 contract[/tt]
[sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]