flat file import

Question

flat file import

Martin Dutton

SSC Rookie

Points: 36
More actions
July 5, 2004 at 9:11 am

#63408

Hi, new to the forum and this is my first post so please go easy on me.
I need to sort out a data importing task from a flat file into the sql database, ive got the basic swing of importing into a table if the flat file is 'layed out' for that table - however its not all that simple, the flat file that i have to import is like follows;
tbl_table_name_1
data¦data¦data¦data¦data¦data¦data¦data¦data¦data¦
tbl_table_name_2
data¦data¦data¦data¦data¦data¦data¦data¦data¦data¦
tbl_table_name_3
data¦data¦data¦data¦data¦data¦data¦data¦data¦data¦
and to be honest ill have very little (if any at all) say over that format. What i need to do is have a script/DTS Package that will read the first line as the destination table, then read the second line as the data - update/create as needed and the move to the next table name and on and on.
Can anyone offer any pointers here - a helping nudge in the right direction (or the completed script) would be most helpful.
Thank you.
Martin.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 1

Import the flat file to one table with one column. Write a stored procedure to insert the data| lines into seperate tables as the next SQLTask. Then move the data accordingly.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

Vince Pacella SSC Veteran Points: 267 More actions · Answer 2

I had a flat file that was composed of four tables , which each line had a prefix A B C or D denoting what table the line belongs to. And there could be many lines per table before the next table was gotten to, plus the FK was only in A not B C D so there was no way to link the data in the last three tables to the first table.

So what i did first was write a program in C# that read through this one file and created 4 seperate text files, one for each table and wrote the PK or FK to each appropiate file, and then in Sql Server I wrote a package that imported the 4 files into their respective tables. Works fast and was easy to do.

philcart SSC-Forever Points: 47794 More actions · Answer 3

I'd go with Derrick's suggestion. I've always found that data manipulation was much faster and cleaner once the data was in a SQL database.

Doing things like setting PK/FK is easy when you can update rows in sets rather than one at a time.

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

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

If each of the tables data is in separate files, your task becomes a 1000 times easier.

With a flat file, you can import using the BULK INSERT command or BCP. (Hint: Bulk Insert is faster and better). But both of these commands would expect separate files, one for each table.

At some point you need to split your data stream into the separate tables. If it's a flat file now, separate it BEFORE putting it into the database. It'll be easier if your data is sequential.

Julian Kuiters
juliankuiters.id.au

bobsterboy SSCommitted Points: 1549 More actions · Answer 5

I would also go with Derrick on this, but here's more detail. Anyone have a better idea?

1. Import the data into a table with 2 fields. Field one is DataRowID and is int /not null / identity. Field 2 is varchar 4000 (or whatever max length your data row can be) (I gave it the great name of Field1). I named this table Test2

2. Move THAT data into a table that has 3 fields, DataRowID(same as above, but not identity), TableName, DataRow. I named this table Test3

This script had 4 parts.

1. I select all of the rows that have the tablename in them - criteria is Field1 like 'tbl_%'.

2. I select all of the rows that don't have the Field1 like 'tbl_%'

3. Join the two tables using data row > tablename row AND data row < next tablename row or, if there's no more tablename rows, < the last row number + 1.

4. The data is then inserted into Test3.

Here's the script to move the data into a nice flat table like I described.

--BEGIN SQL CODE

select DataRow.RowID, tableNameCol.tblName,

DataRow.Field1

into Test3

from (select TableNameRow.RowID,

TableNameRow.Field1

as tblName

from test2

as TableNameRow

where rowid

= (select max(MaxRowTable.RowID)

from Test2

as MaxRowtable

where Field1 like ('tbl_%')

and MaxRowTable.RowID

<= TableNameRow.rowid ) )

as TableNameCol

inner join test2 as DataRow on DataRow.RowID > TableNameCol.RowID and

DataRow.RowID < isnull((select min(MaxRowTable.RowID)

from Test2

as MaxRowtable

where Field1 like ('tbl_%')

and MaxRowTable.RowID

> TableNameCol.rowid), (select max(RowID) + 1 from Test2))

-- END SQL CODE

3. You can then select which records need to be inserted based on some part of field1 (substring(field1,11,10)). Let's just say that's the key for your insert table. That would lead to the following script. (replace <tablename> with the actual tablename in which you'll be inserting/updating records.

--BEGIN SQL CODE

Insert table <tablename>

select substring(field1,1,10) as NewField1,

substring(field1,11,10) as NewKeyField,

substring(field1,21,10) as NewField2 (yada, yada)

FROM Test3 where substring(Field1,11,10) not in (select NewKeyField from <tablename&gt and tblname = '<tablename>'

-- END SQL CODE

For updates, it would be similar

-- BEGIN SQL CODE

UPDATE TABLE <tablename>

SET NewField1 = substring(field1,1,10),

NewField2 = substring(field1,21,10)

FROM <tablename>, Test3

WHERE substring(Test3.field1,11,10) = NewKeyField

AND tblName = '<tablename>'

-- END SQL CODE

Martin Dutton SSC Rookie Points: 36 More actions · Answer 6

Thank you everyone for your posts, it would seem though that i may have left off an important detail that may have an impact upon your suggestions;

this would be more indicative of the data that needs to be imported;

tbl_table_name_1

data¦data¦data¦data¦data

tbl_table_name_1

data¦data¦data¦data¦data

tbl_table_name_1

data¦data¦data¦data¦data

tbl_table_name_2

data¦data¦data¦data¦data¦data¦data¦data¦data¦data¦

tbl_table_name_2

data¦data¦data¦data¦data¦data¦data¦data¦data¦data¦

tbl_table_name_3