Data Import - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Data Import

Author

Topic

HomerJ
Starting Member

21 Posts

Posted - 2011-03-14 : 11:11:08

I've been tasked with converting a large text-based data file to a database. I plan to use Access, although I could use the Sybase system. But here's the actual problem:

The data is text, and space-delimited, in the following format:

Main-Record 1
Sub-Record 1
Sub-Record 2
Main-Record 2
Sub-Record 1
Sub-Record 2
etc. . .

There could be any number of Sub-Records per Main-Record.

I'm just not sure the 'right' way to convert this data into the two separate tables we need. I assume there are a series of if/else statements or something, but I am simply not experienced enough to guess.

Any insight from the power users would be appreciated. Thanks.

HomerJ
Starting Member

21 Posts

Posted - 2011-03-14 : 11:18:06

Here's the data template, if it clarifies anything?

HEADER-LAYOUT

HEADER-TYPE - ALPHA(1)
DETAIL-REC-COUNT NUMBER(4)
PREMISE-NUM - NUMBER(10)
SERV-PIPE-NUM - NUMBER(11)
HOUSE-NUM - ALPHA(10)
ST-NUM-SUFFIX-CODE ALPHA(3)
STREET-NAME-PREFIX ALPHA(2)
STREET-NAME - ALPHA(28)
STREET-SUFFIX-CODE ALPHA(4)
OVERFLOW-ADDR-NAME ALPHA(35)
HOUSE-LOC-CODE ALPHA (4)
HOUSE-LOC-NAME ALPHA(6)
TOWN-NAME - ALPHA(26)
ZIP-CODE ALPHA(05)
ZIP-PLUS-4-CODE - ALPHA(04)
SQUARE-MILE - NUMBER(04)
PREMISE-TYPE-ID - NUMBER(4)
BUILD-DATE ALPHA(10)
HEATED-AREA-CODE ALPHA(2)
MASTER-METER-STAT - ALPHA(1)
RATE-NUM ALPHA(10)
NAICS-CODE ALPHA(8)

DETAIL-LAYOUT

DETAIL-TYPE - ALPHA(1)
DETAIL-NUM NUMBER(4)
REVENUE-MONTH - NUMBER(6)
START-DATE ALPHA(10)
END-DATE ALPHA(10)
THERMS NUMBER(11)
BILL-AMOUNT - NUMBER(9)
DEGREE-DAYS - NUMBER(4)

HomerJ
Starting Member

21 Posts

Posted - 2011-03-14 : 14:42:11

OK, I've done some more research, and it appears the general consensus is to first load the entire dataset into a table (or temp table) and then write code to process the temp table into two separate tables. This makes a lot of sense. However, I'm not very familiar with the use of storing values and splitting up the two insert statements. What I'm thinking is something like:

Process A
Insert record into MASTER_TABLE where the first character of the record starts with "M" for Master, splitting text line into the appropriate fields.
Store the PREMISE_NUM from this record

Process B
Insert each subsequent record into DETAIL_TABLE where the first character of the record(s) starts with "D" for Detail, splitting the text line into appropriate fields, and including the stored PREMISE_NUM value from the previous process.
Repeat appropriate number of times, until reaching the next "M" record, then go back to Process A.

So, now the question is, where do I begin?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources