BULK INSERT with fixed length text file

Question

BULK INSERT with fixed length text file

hydera

SSC-Addicted

Points: 410
More actions
August 15, 2005 at 2:31 pm

#187038

I'm trying to do a BULK INSERT on a text file into a table. The file format is fixed length fields with no fieldterminator or rowterminator characters (whole file is on one line). Is anyone familiar with how to perform a bulk insert on this type of file? Sample is shown below, setting the line length to 62 formats it properly. The field lengths are:
-name 1-15
-sex 16-16
-dob 17+23
-groupid 24-25
-school 26-29
-postcode 30-33
-recordbkno 34-38
-entrydate 39-44
-bronze 45-50
-silver 51-56
-gold 57-62
ADAIR L f300568bpsMerc606106627210383140286 ANDREWS M m010169gzzPAC 606101010010183 021286AUSTIN B m010169basArmy601800000010183100984 BACA F f250568bssMelH615635235301183 BAILEY C f150569bpsPenr615335280020883 BAILEY G m010169basArmy615500000010183270683270984 BAWDEN I C m100268bpsGGS 60556026 010683 BELL M f010169bzzPAC 606200000010183010184010685 BENNISON R f010668bssBelH61045405 010383 BERRY D m261267bzzHosp606435253171083 BETTERIDGE M m010169bzzSwan601500000010183170685 BLAIR K m010169basAir 615500000010183250784 BLAKE P m010169bpsCCGS600900000010183010184011284 BOEREMA J m211266bpsScot600935273270983 BOOTH BG m010169bssBelH610300000010183191185 BOOTH S m010169bssBelH610300000010183290584 BOUSFIELD KA f180865bbsScou610535281021083

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47795 More actions · Answer 1

I'd be inclined to say you'll have to pre-process the file and insert the row terminators. Something like running a short VBScript to insert CR+LF into the appropriate places. Once you have the row terminators in place it would be a simple matter to create a format file and import using BULK INSERT.

--------------------
Colt 45 - the original point and click interface

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

Format file for Bulk insert use option [ [ , ] FORMATFILE = 'format_file_path' ]

[ [ , ] FORMATFILE = 'format_file_path' ]

Test62.fmt:

-------------start----------------------

8.0

11

1 SQLCHAR 0 15 "" 1 name Latin1_General_CI_AS

2 SQLCHAR 0 1 "" 2 sex Latin1_General_CI_AS

3 SQLCHAR 0 7 "" 3 dob Latin1_General_CI_AS

4 SQLCHAR 0 2 "" 4 groupid Latin1_General_CI_AS

5 SQLCHAR 0 4 "" 5 school Latin1_General_CI_AS

6 SQLCHAR 0 4 "" 6 postcode Latin1_General_CI_AS

7 SQLCHAR 0 5 "" 7 recordbkno Latin1_General_CI_AS

8 SQLCHAR 0 6 "" 8 entrydate Latin1_General_CI_AS

9 SQLCHAR 0 6 "" 9 bronze Latin1_General_CI_AS

10 SQLCHAR 0 6 "" 10 silver Latin1_General_CI_AS

11 SQLCHAR 0 6 "" 11 gold Latin1_General_CI_AS

-------------------end-------------------

Kindest Regards,

Vasc

SQL Draggon Right there with Babe Points: 766 More actions · Answer 3

Besids BCP you can use DTS. in the DTS package create a connection to the destination DB. create aconnection to the text file, during the creation of the connection to the text file you can select a "Delemited" or "Fixed Length" format. From there on it's a peice of cake...

Sam...

Enjoy..

Don't count what you do, do what counts.

SQL Draggon