Solved

import flat file into two tables

Posted on 2002-04-09
3
215 Views
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
http://www.geocities.com/alexlow2020/sample.txt

and i have to import into below table
http://www.geocities.com/alexlow2020/table.jpg

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

0
Comment
Question by:gotreply
3 Comments
 
LVL 142

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.

CHeers
0
 
LVL 1

Accepted Solution

by:
gotaquestion earned 200 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")
etc..

     Main = DTSTransformStat_OK
     
End Function
Function getflag()

     ''some sort of test
     if isnumeric(DTSSource("headerfield")) then
        getflag = "table1"
        else
        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.
0
 

Author Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now