import flat file into two tables

Posted on 2002-04-09
Medium Priority
Last Modified: 2006-11-17
trying to load a text files into 2 mssql tables. the text file contains rows of record where if it starts with H (header) will go to tab1 & if it starts with numeric will go to tab2.

example of the text file are like this

and i have to import into below table

the text file have fix char location & i have to extract it out to put into specific col.

anyone have any ideas how to do this or done it b4 ? thanks

Question by:gotreply
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6930551
For ANY kind of import, i do import to a staging table first, which has only varchar field(s). Then, with "simply" SQL, i can validate the data, and move it around to other tables, eventually other staging tables.

You will have to write some long SQL, specifiying all the columns, splitting up the data, but you can be assured that this is the most easy-to-support and flexible solution i found until now.


Accepted Solution

gotaquestion earned 400 total points
ID: 6937352
you should import the file into one staging table like angelll suggested.  and you should add a field to this staging table to be used for a flag as to which table the row is going to eventually insert to.  use the Data Transformation Service and
and in the ActiveX script Transformation properties
determine the flag

'  Visual Basic Transformation Script

'  Copy each source column to the destination column
Function Main()
     DTSDestination("flag") = getFlag()
     DTSDestination("f2") = DTSSource("Col002")     DTSDestination("f3") = DTSSource("Col003")

     Main = DTSTransformStat_OK
End Function
Function getflag()

     ''some sort of test
     if isnumeric(DTSSource("headerfield")) then
        getflag = "table1"
        getflag = "table2"
     end if

End Function

after the dts is done you can run an insert into your main table based on the flag field

you could so set up multiple destinations in a DTS object but that is more complicated.

Author Comment

ID: 7303807
thks for the help, have solve it after various try, ur comment did give me some idea to furthur my experiment.

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question