Format Files
Mario Vargas
I have a format and data file that is submitted to me daily by the realtor
reciprocity within my region. I've been able to use the information in the
format file to create a new table, but I'm doing this manually. I want to
have SQL Server create the table for me, based on the format file's
information. How can I do this?
Thanks for your help!
Mario Vargas
P.S.: Here's the contents of the format file (I know the text will be
wrapped)
8.0
226
1 SQLINT 0 4 "|" 1
LISTING_ID ""
2 SQLINT 1 4 "|" 2
MLS_NUMBER ""
3 SQLDECIMAL 1 19 "|" 3 ACRES
""
4 SQLINT 1 4 "|" 4
ANN_ASSC_FEE ""
5 SQLCHAR 2 30 "|" 5 APN
SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 3 "|" 6 AREA
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 30 "|" 7
AREA_DESC SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 5 "|" 8
AREA_SUB SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 20 "|" 9
ASSMT_TYPE SQL_Latin1_General_CP1_CI_AS
10 SQLDATETIME 1 8 "|" 10
AUCTION_DATE ""
11 SQLCHAR 2 1 "|" 11
AUCTION_YN SQL_Latin1_General_CP1_CI_AS
12 SQLINT 1 4 "|" 12
BAL_CONSTR_ASSMT ""
13 SQLDECIMAL 1 19 "|" 13 BATHS
""
14 SQLSMALLINT 1 2 "|" 14
BATHS_FULL ""
15 SQLSMALLINT 1 2 "|" 15
BATHS_PART ""
16 SQLSMALLINT 1 2 "|" 16 BDRMS
""
17 SQLCHAR 2 6 "|" 17
BUYER_AGENT_COMP SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 2 3 "|" 18
CAT_DESC SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 2 20 "|" 19 CITY
SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 2 36 "|" 20
COMMON_DESC SQL_Latin1_General_CP1_CI_AS
21 SQLINT 1 4 "|" 21
CONSTR_ASSMT ""
22 SQLCHAR 2 30 "|" 22
CONTACT_NAME SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 2 15 "|" 23
CONTACT_PHONE SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 2 15 "|" 24 COUNTY
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 2 1 "|" 25
CROSS_REF_YN SQL_Latin1_General_CP1_CI_AS
26 SQLINT 1 4 "|" 26
DEPOSIT ""
27 SQLCHAR 2 8000 "|" 27
DIRECTIONS SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 2 1 "|" 28
DUAL_RATE_YN SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 2 1 "|" 29
EXCEPTION_YN SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 2 1 "|" 30
FENCED_YN SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 2 1 "|" 31
FIREPLACE_YN SQL_Latin1_General_CP1_CI_AS
32 SQLDECIMAL 1 19 "|" 32
GAR_PRK_NO ""
33 SQLCHAR 2 1 "|" 33
HANDI_ACCESS_YN SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 2 1 "|" 34
HOMSTD_EXEMP_YN SQL_Latin1_General_CP1_CI_AS
35 SQLNUMERIC 1 19 "|" 35
LATITUDE ""
36 SQLCHAR 2 1 "|" 36
LEAD_DISCL_YN SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 2 1 "|" 37
LEASE_REQ_YN SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 2 10 "|" 38
LEASE_TERM SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 2 60 "|" 39
LEGAL_DESC SQL_Latin1_General_CP1_CI_AS
40 SQLINT 1 4 "|" 40
LESS_LOT_OF ""
41 SQLCHAR 2 5 "|" 41
LISTING_TYPE SQL_Latin1_General_CP1_CI_AS
42 SQLNUMERIC 1 19 "|" 42
LONGITUDE ""
43 SQLCHAR 2 6 "|" 43 LOT
SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 2 15 "|" 44
LOT_SIZE SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 2 8 "|" 45
MAP_CODE SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 2 6 "|" 46
OCCUPIED_BY SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 2 1 "|" 47
PETS_YN SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 2 1 "|" 48
PHOTO_YN SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 2 5 "|" 49 PLAT
SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 2 3 "|" 50
POOL_YN SQL_Latin1_General_CP1_CI_AS
51 SQLINT 1 4 "|" 51 PRICE
""
52 SQLINT 1 4 "|" 52
PRICE_CURRENT ""
53 SQLINT 1 4 "|" 53
PRICE_LIST ""
54 SQLINT 1 4 "|" 54
PRICE_ORG ""
55 SQLINT 1 4 "|" 55
PRICE_PREV ""
56 SQLINT 1 4 "|" 56
PRICE_RENT ""
57 SQLINT 1 4 "|" 57
PRICE_SALE ""
58 SQLCHAR 2 1 "|" 58
PROP_DISCL_YN SQL_Latin1_General_CP1_CI_AS
59 SQLTINYINT 1 1 "|" 59
PROP_TYPE ""
60 SQLCHAR 2 8000 "|" 60
REMARKS_AGENT SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 2 8000 "|" 61
REMARKS_CONTING SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 2 8000 "|" 62
REMARKS_GENERAL SQL_Latin1_General_CP1_CI_AS
63 SQLCHAR 2 8000 "|" 63
REMARKS_LEGAL SQL_Latin1_General_CP1_CI_AS
64 SQLCHAR 2 8000 "|" 64
REMARKS_RENTAL SQL_Latin1_General_CP1_CI_AS
65 SQLCHAR 2 8000 "|" 65
REMARKS_SALE SQL_Latin1_General_CP1_CI_AS
66 SQLINT 1 4 "|" 66
RENTAL_NUM ""
67 SQLCHAR 2 15 "|" 67
SCHOOLS_D SQL_Latin1_General_CP1_CI_AS
68 SQLCHAR 2 20 "|" 68
SCHOOLS_E SQL_Latin1_General_CP1_CI_AS
69 SQLCHAR 2 20 "|" 69
SCHOOLS_H SQL_Latin1_General_CP1_CI_AS
70 SQLCHAR 2 20 "|" 70
SHOW_INSTR SQL_Latin1_General_CP1_CI_AS
71 SQLCHAR 2 15 "|" 71
SITEDEFC SQL_Latin1_General_CP1_CI_AS
72 SQLDECIMAL 1 19 "|" 72
six_mo_taxes ""
73 SQLINT 1 4 "|" 73 SQFT
""
74 SQLCHAR 2 8000 "|" 74
SQFT_SOURCE SQL_Latin1_General_CP1_CI_AS
75 SQLCHAR 2 2 "|" 75 STATE
SQL_Latin1_General_CP1_CI_AS
76 SQLCHAR 2 2 "|" 76
STATE_ID SQL_Latin1_General_CP1_CI_AS
77 SQLCHAR 2 4 "|" 77 STATUS
SQL_Latin1_General_CP1_CI_AS
78 SQLCHAR 2 15 "|" 78
STATUS_DESCR_LNG SQL_Latin1_General_CP1_CI_AS
79 SQLCHAR 2 3 "|" 79
STATUS_DESCR_SHRT SQL_Latin1_General_CP1_CI_AS
80 SQLCHAR 2 1 "|" 80
STATUS_FLAG SQL_Latin1_General_CP1_CI_AS
81 SQLCHAR 2 4 "|" 81
status_prev SQL_Latin1_General_CP1_CI_AS
82 SQLCHAR 2 30 "|" 82 STREET
SQL_Latin1_General_CP1_CI_AS
83 SQLCHAR 2 2 "|" 83
STREET_DIR SQL_Latin1_General_CP1_CI_AS
84 SQLINT 1 4 "|" 84
street_id ""
85 SQLINT 1 4 "|" 85
STREET_NO ""
86 SQLCHAR 2 6 "|" 86
STREET_NUM SQL_Latin1_General_CP1_CI_AS
87 SQLCHAR 2 35 "|" 87
street_num_n_name SQL_Latin1_General_CP1_CI_AS
88 SQLCHAR 2 2 "|" 88
STREET_POST_DIR SQL_Latin1_General_CP1_CI_AS
89 SQLCHAR 2 4 "|" 89
STREET_SUFF SQL_Latin1_General_CP1_CI_AS
90 SQLCHAR 2 6 "|" 90
STREET_TYP SQL_Latin1_General_CP1_CI_AS
91 SQLCHAR 2 40 "|" 91
SUB_DIV SQL_Latin1_General_CP1_CI_AS
92 SQLCHAR 2 8 "|" 92
SUB_DIV_ID SQL_Latin1_General_CP1_CI_AS
93 SQLCHAR 2 15 "|" 93
SUB_PROP SQL_Latin1_General_CP1_CI_AS
94 SQLCHAR 2 15 "|" 94
sub_prop_long SQL_Latin1_General_CP1_CI_AS
95 SQLDECIMAL 1 19 "|" 95 TAX_1
""
96 SQLDECIMAL 1 19 "|" 96 TAX_2
""
97 SQLCHAR 2 25 "|" 97 TAX_ID
SQL_Latin1_General_CP1_CI_AS
98 SQLDECIMAL 1 19 "|" 98
TOT_TAXES ""
99 SQLCHAR 2 12 "|" 99
TOWNSHIP SQL_Latin1_General_CP1_CI_AS
100 SQLCHAR 2 6 "|" 100
UNIT_NO SQL_Latin1_General_CP1_CI_AS
101 SQLCHAR 2 1 "|" 101
VALUE_RNG_YN SQL_Latin1_General_CP1_CI_AS
102 SQLSMALLINT 1 2 "|" 102
YEAR_BUILT ""
103 SQLCHAR 2 5 "|" 103
year_built_desc SQL_Latin1_General_CP1_CI_AS
104 SQLCHAR 2 10 "|" 104 ZIP
SQL_Latin1_General_CP1_CI_AS
105 SQLCHAR 2 8 "|" 105 ZONE
SQL_Latin1_General_CP1_CI_AS
106 SQLSMALLINT 1 2 "|" 106
BATH_FULL_LOWER ""
107 SQLSMALLINT 1 2 "|" 107
BATH_FULL_MAIN ""
108 SQLSMALLINT 1 2 "|" 108
BATH_FULL_UPPER ""
109 SQLSMALLINT 1 2 "|" 109
BATH_PART_LOWER ""
110 SQLSMALLINT 1 2 "|" 110
BATH_PART_MAIN ""
111 SQLSMALLINT 1 2 "|" 111
BATH_PART_UPPER ""
112 SQLCHAR 2 1 "|" 112
MASTER_BTH_YN SQL_Latin1_General_CP1_CI_AS
113 SQLINT 1 4 "|" 113
NO_ROOMS ""
114 SQLCHAR 2 15 "|" 114
_1_FEAT SQL_Latin1_General_CP1_CI_AS
115 SQLCHAR 2 3 "|" 115
_1_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
116 SQLCHAR 2 3 "|" 116
_1_ROOM_ID SQL_Latin1_General_CP1_CI_AS
117 SQLINT 1 4 "|" 117
_1_SIZE_1 ""
118 SQLINT 1 4 "|" 118
_1_SIZE_2 ""
119 SQLINT 1 4 "|" 119
_1_SQFT ""
120 SQLCHAR 2 1 "|" 120
_1_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
121 SQLCHAR 2 15 "|" 121
_2_FEAT SQL_Latin1_General_CP1_CI_AS
122 SQLCHAR 2 3 "|" 122
_2_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
123 SQLCHAR 2 3 "|" 123
_2_ROOM_ID SQL_Latin1_General_CP1_CI_AS
124 SQLINT 1 4 "|" 124
_2_SIZE_1 ""
125 SQLINT 1 4 "|" 125
_2_SIZE_2 ""
126 SQLINT 1 4 "|" 126
_2_SQFT ""
127 SQLCHAR 2 1 "|" 127
_2_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
128 SQLCHAR 2 15 "|" 128
_3_FEAT SQL_Latin1_General_CP1_CI_AS
129 SQLCHAR 2 3 "|" 129
_3_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
130 SQLCHAR 2 3 "|" 130
_3_ROOM_ID SQL_Latin1_General_CP1_CI_AS
131 SQLINT 1 4 "|" 131
_3_SIZE_1 ""
132 SQLINT 1 4 "|" 132
_3_SIZE_2 ""
133 SQLINT 1 4 "|" 133
_3_SQFT ""
134 SQLCHAR 2 1 "|" 134
_3_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
135 SQLCHAR 2 15 "|" 135
_4_FEAT SQL_Latin1_General_CP1_CI_AS
136 SQLCHAR 2 3 "|" 136
_4_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
137 SQLCHAR 2 3 "|" 137
_4_ROOM_ID SQL_Latin1_General_CP1_CI_AS
138 SQLINT 1 4 "|" 138
_4_SIZE_1 ""
139 SQLINT 1 4 "|" 139
_4_SIZE_2 ""
140 SQLINT 1 4 "|" 140
_4_SQFT ""
141 SQLCHAR 2 1 "|" 141
_4_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
142 SQLCHAR 2 15 "|" 142
_5_FEAT SQL_Latin1_General_CP1_CI_AS
143 SQLCHAR 2 3 "|" 143
_5_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
144 SQLCHAR 2 3 "|" 144
_5_ROOM_ID SQL_Latin1_General_CP1_CI_AS
145 SQLINT 1 4 "|" 145
_5_SIZE_1 ""
146 SQLINT 1 4 "|" 146
_5_SIZE_2 ""
147 SQLINT 1 4 "|" 147
_5_SQFT ""
148 SQLCHAR 2 1 "|" 148
_5_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
149 SQLCHAR 2 15 "|" 149
_6_FEAT SQL_Latin1_General_CP1_CI_AS
150 SQLCHAR 2 3 "|" 150
_6_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
151 SQLCHAR 2 3 "|" 151
_6_ROOM_ID SQL_Latin1_General_CP1_CI_AS
152 SQLINT 1 4 "|" 152
_6_SIZE_1 ""
153 SQLINT 1 4 "|" 153
_6_SIZE_2 ""
154 SQLINT 1 4 "|" 154
_6_SQFT ""
155 SQLCHAR 2 1 "|" 155
_6_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
156 SQLCHAR 2 15 "|" 156
_7_FEAT SQL_Latin1_General_CP1_CI_AS
157 SQLCHAR 2 3 "|" 157
_7_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
158 SQLCHAR 2 3 "|" 158
_7_ROOM_ID SQL_Latin1_General_CP1_CI_AS
159 SQLINT 1 4 "|" 159
_7_SIZE_1 ""
160 SQLINT 1 4 "|" 160
_7_SIZE_2 ""
161 SQLINT 1 4 "|" 161
_7_SQFT ""
162 SQLCHAR 2 1 "|" 162
_7_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
163 SQLCHAR 2 15 "|" 163
_8_FEAT SQL_Latin1_General_CP1_CI_AS
164 SQLCHAR 2 3 "|" 164
_8_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
165 SQLCHAR 2 3 "|" 165
_8_ROOM_ID SQL_Latin1_General_CP1_CI_AS
166 SQLINT 1 4 "|" 166
_8_SIZE_1 ""
167 SQLINT 1 4 "|" 167
_8_SIZE_2 ""
168 SQLINT 1 4 "|" 168
_8_SQFT ""
169 SQLCHAR 2 1 "|" 169
_8_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
170 SQLCHAR 2 15 "|" 170
_9_FEAT SQL_Latin1_General_CP1_CI_AS
171 SQLCHAR 2 3 "|" 171
_9_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
172 SQLCHAR 2 3 "|" 172
_9_ROOM_ID SQL_Latin1_General_CP1_CI_AS
173 SQLINT 1 4 "|" 173
_9_SIZE_1 ""
174 SQLINT 1 4 "|" 174
_9_SIZE_2 ""
175 SQLINT 1 4 "|" 175
_9_SQFT ""
176 SQLCHAR 2 1 "|" 176
_9_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
177 SQLCHAR 2 15 "|" 177
_10_FEAT SQL_Latin1_General_CP1_CI_AS
178 SQLCHAR 2 3 "|" 178
_10_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
179 SQLCHAR 2 3 "|" 179
_10_ROOM_ID SQL_Latin1_General_CP1_CI_AS
180 SQLINT 1 4 "|" 180
_10_SIZE_1 ""
181 SQLINT 1 4 "|" 181
_10_SIZE_2 ""
182 SQLINT 1 4 "|" 182
_10_SQFT ""
183 SQLCHAR 2 1 "|" 183
_10_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
184 SQLCHAR 2 15 "|" 184
_11_DESCR SQL_Latin1_General_CP1_CI_AS
185 SQLCHAR 2 15 "|" 185
_11_FEAT SQL_Latin1_General_CP1_CI_AS
186 SQLCHAR 2 3 "|" 186
_11_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
187 SQLCHAR 2 3 "|" 187
_11_ROOM_ID SQL_Latin1_General_CP1_CI_AS
188 SQLINT 1 4 "|" 188
_11_SIZE_1 ""
189 SQLINT 1 4 "|" 189
_11_SIZE_2 ""
190 SQLINT 1 4 "|" 190
_11_SQFT ""
191 SQLCHAR 2 1 "|" 191
_11_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
192 SQLCHAR 2 15 "|" 192
_12_DESCR SQL_Latin1_General_CP1_CI_AS
193 SQLCHAR 2 15 "|" 193
_12_FEAT SQL_Latin1_General_CP1_CI_AS
194 SQLCHAR 2 3 "|" 194
_12_ON_LEVEL SQL_Latin1_General_CP1_CI_AS
195 SQLCHAR 2 3 "|" 195
_12_ROOM_ID SQL_Latin1_General_CP1_CI_AS
196 SQLINT 1 4 "|" 196
_12_SIZE_1 ""
197 SQLINT 1 4 "|" 197
_12_SIZE_2 ""
198 SQLINT 1 4 "|" 198
_12_SQFT ""
199 SQLCHAR 2 1 "|" 199
_12_WNDW_COV_YN SQL_Latin1_General_CP1_CI_AS
200 SQLCHAR 2 150 "|" 200 STYLE
SQL_Latin1_General_CP1_CI_AS
201 SQLCHAR 2 150 "|" 201 TYPE
SQL_Latin1_General_CP1_CI_AS
202 SQLCHAR 2 150 "|" 202
EXTERIOR SQL_Latin1_General_CP1_CI_AS
203 SQLCHAR 2 150 "|" 203
FOUNDATION SQL_Latin1_General_CP1_CI_AS
204 SQLCHAR 2 150 "|" 204 ROOF
SQL_Latin1_General_CP1_CI_AS
205 SQLCHAR 2 150 "|" 205 GARAGE
SQL_Latin1_General_CP1_CI_AS
206 SQLCHAR 2 150 "|" 206
DRIVEWAY SQL_Latin1_General_CP1_CI_AS
207 SQLCHAR 2 150 "|" 207
LOT_DESC SQL_Latin1_General_CP1_CI_AS
208 SQLCHAR 2 150 "|" 208
FIREPLACE SQL_Latin1_General_CP1_CI_AS
209 SQLCHAR 2 150 "|" 209
EQUIPMENT SQL_Latin1_General_CP1_CI_AS
210 SQLCHAR 2 150 "|" 210
MISCELLANEOUS SQL_Latin1_General_CP1_CI_AS
211 SQLCHAR 2 150 "|" 211
HEATING SQL_Latin1_General_CP1_CI_AS
212 SQLCHAR 2 150 "|" 212
HEAT_FUEL SQL_Latin1_General_CP1_CI_AS
213 SQLCHAR 2 150 "|" 213
COOLING SQL_Latin1_General_CP1_CI_AS
214 SQLCHAR 2 150 "|" 214
WATER_HEATER SQL_Latin1_General_CP1_CI_AS
215 SQLCHAR 2 150 "|" 215 WATER
SQL_Latin1_General_CP1_CI_AS
216 SQLCHAR 2 150 "|" 216 SEWER
SQL_Latin1_General_CP1_CI_AS
217 SQLCHAR 2 150 "|" 217 TERMS
SQL_Latin1_General_CP1_CI_AS
218 SQLCHAR 2 150 "|" 218
POSSESSION SQL_Latin1_General_CP1_CI_AS
219 SQLCHAR 2 8 "|" 219
L_OFF_ID SQL_Latin1_General_CP1_CI_AS
220 SQLCHAR 2 30 "|" 220
L_OFF_NAME SQL_Latin1_General_CP1_CI_AS
221 SQLCHAR 2 10 "|" 221
L_AG1_ID SQL_Latin1_General_CP1_CI_AS
222 SQLCHAR 2 30 "|" 222
L_AG1_NAME SQL_Latin1_General_CP1_CI_AS
223 SQLCHAR 2 15 "|" 223
L_AG1_PHONE SQL_Latin1_General_CP1_CI_AS
224 SQLCHAR 2 15 "|" 224
L_OFF_PHONE SQL_Latin1_General_CP1_CI_AS
225 SQLCHAR 2 30 "|" 225
MAIL_CITY SQL_Latin1_General_CP1_CI_AS
226 SQLCHAR 2 2 "" 226
MAIL_STATE SQL_Latin1_General_CP1_CI_AS
Mario Vargas
Mario
"Dennis Redfield" <dennis....@acadia-ins.com> wrote in message
news:#OwKh0kiCHA.1572@tkmsftngp09...
> BCP will not create a table for you. You really have two choices here.
> (1) use script to create a DDL statement from the format file and submit
the
> statement throught DSO or osql.
> (2) (if you can get your supplier to give use an XML formated file:) Use
> SQLXML. Bulk Load and the file will be created for you.
> "Mario Vargas" <mario...@thecourier.com> wrote in message
> news:ONBnHLdiCHA.2536@tkmsftngp12...
msnews
schedule it, etc.