March 16, 2006 at 6:46 am
need help to import a text file to table in sql server
my text file is like this
what i need to is create a new record
where the line
DN:
in my text file i have a 300000
like this
###########################################
-------------------------------------------------------------------------------
DN: 6361000
TYPE: SINGLE PARTY LINE
SNPA: 304 SIG: DT LNATTIDX: 21
LINE EQUIPMENT NUMBER: AKVA 05 0 22 00
LINE CLASS CODE: 1FR
IBN TYPE: STATION
CUSTGRP: RESGRP SUBGRP: 0 NCOS: 21
LINE TREATMENT GROUP: 21
CARDCODE: 6K18AA GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER : 123
PM TERMINAL NUMBER : 705
OPTIONS: NONE
RES OPTIONS:
MTR DGT PPH RPPO CARR 1 NONE CRA 4
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DN: 6361002
TYPE: SINGLE PARTY LINE
SNPA: 304 SIG: DT LNATTIDX: 23
LINE EQUIPMENT NUMBER: AKVA 07 0 34 24
LINE CLASS CODE: 1FR
IBN TYPE: STATION
CUSTGRP: RESGRP SUBGRP: 0 NCOS: 23
LINE TREATMENT GROUP: 23
CARDCODE: 6K17BA GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER : 125
PM TERMINAL NUMBER : 1113
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CWT CWR CCWB CNDB NOAMA ACB NOAMA AR NOAMA SCWID DGT DDN NOAMA CFDA N NSCR
5 A 24 15046361002 MWT CMWI Y N N N WUCR I VMI N N CARR 1 NONE CRA 1 2 4
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
DN: 6361004
TYPE: SINGLE PARTY LINE
SNPA: 304 SIG: DT LNATTIDX: 20
LINE EQUIPMENT NUMBER: AKVA 04 0 12 24
LINE CLASS CODE: 1FR
IBN TYPE: STATION
CUSTGRP: RESGRP SUBGRP: 0 NCOS: 20
LINE TREATMENT GROUP: 20
CARDCODE: 6K17AC GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER : 122
PM TERMINAL NUMBER : 409
CFW INDEX: N/A
OPTIONS: NONE
RES OPTIONS:
CWT 3WC CWR CCWB CNDB NOAMA ACB NOAMA AR NOAMA DGT DDN NOAMA CFW C NSCR 5 I
$ CFDA N NSCR 5 A 20 15046361004 MWT CMWI Y N N N WUCR I VMI N N CARR 1
NONE
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
thnks
ilan
March 16, 2006 at 9:52 am
You have a couple different options for this......you can create a linked server to the file, through script, and then query the lines of text for the line like DN, and parsing the values out by a series of rules, or create a BCP statement, through script, simply reading the whole file into a temp table, one line per row, and querying for the DN lines, then parsing the numbers out.....either will work fine....
I personally prefer the BCP method, but have done it the linked server method as well....it kinda depends on whether your more comfortable generating BCP statements through code, or creating linked servers through code. But you should be able to write a proc that handles this easily enough, going by these methods.
I would suggest creating a staging table for the imports, and processing the DN lines from that, archiving the text lines afterwards just to guarantee auditablility, as well....
I had a project where we did this for many different text files of different formats, and we used a table of definitions for the text files, in order to grab values like this......It looks like your file is formatted consistently, so it should work fine doing it this way......but if there are formatting differences the parsing of the values could get tricky.....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply