Export Text Files into Database

Hi Experts,

   can you all help me to find way to export data text file into SQL database

Thank's be 4
Who is Participating?
Or you could use the following Text driver connection string, and then just manipulate/import/read the text file like you do any other database using your choice of ADO, DAO, RDO...and so on.

Here is the info you need:
Connection string:
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\somepath\;Extensions=asc,csv,tab,txt;"

You will need to have a schema.ini file in the same directory as the text file you are importing and it should contain the following:
FName Text Width 20
LName Text Width 20
Age Number
DOB Date
NickName Text

ColNameHeader should be true if the file contains the field names on the first row and false (or removed) if it doesnt.
Each line after that defines the fields in the text file in the order in which they appear.  You should be safe using Text, Number and Date as the datatypes but there are others, should the need come up.  The Width # statement at the end of each field definition is optional.  Below is a link to more info about schema files.  Unfortunatly MSDN is has a bad case of "404-itis" right now, and the good article about schema files are not showing up.

In your connection string you specify the path to the database file, and you specify the actual file in the SQL like so:

SELECT * FROM filename#tsv

The period in the file name must be replaced with a #.
Running select statement runs pretty quick but try to keep mass updates and mass inserts to a minimum when dealing with data in delimited file format.

1) Also have a look at (for import to SQL Server):

(look for import)

(BCP in VB)

2) Another small example
rsCP.Open "DailyPrice", Con, adOpenStatic, adLockOptimistic, adCmdTable
Open mTxt For Input As #1
Do Until EOF(1)
    mCnt = mCnt + 1
    Line Input #1, strRow
    intPos = InStr(strRow, "|")

    ' For multiple fields use loop at this section
    Scrip = Val(Left(strRow, intPos - 1))
    CPrice = Val(Right(strRow, Len(strRow) - intPos)) / 100
    With rsCP
        !Scrip = Scrip
        !Rate = CPrice
        !Date = mDt
        If mCnt Mod 25 = 0 Then Me.Caption = mCnt & " Closing Prices found"
    End With
Me.Caption = mCnt & " Closing Prices found"

3) Here are some links that do the work

OzheeAuthor Commented:
Ups i' m so sorri sir..., i mean  export data SQL Database into Text Files..
once again im so sory sir.., coud you help me, to find way sir ?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Is this something you can do when necessary, or do you need it coded into a program for frequent use?

If you just need to get the data from a table out to a text file once in a while, you can do it manually through SQL Enterprise Manager:

Right-click the file you need to export
Select All Tasks
Select Export Data

This starts up the DTS Import/Export Wizard.

Click Next and it will bring up a form with your current database already selected.
Click Next again and select text File ast the destination type, then enter a file name and location.
Clcik Next and it will ask you whether you want your file delimited or fixed length - select the options you need.
Click next and it will be ready to run.

If you're going to export the same table in the future, check the "Save DTS Package" and gibe the DTS file a name.  It will save the package in the Data Transformation Services area of your current server by default, and you can open this up and re-run the package whenever it's needed from Enterprise Manager.

Hope this helps,
OzheeAuthor Commented:
thank's John, but i wanna do is juct click in my menu in VB program, it's mean , i want in code program..
a) You can use DTS to do the import. Since two table formats are mixed up, you will have to run two imports and eliminate the non-matching data each time
b) If using VB to import this, you should use ADO. Loop through the table using a recordset and for each line, determine whether it is a "DAY" or "SEC" record using the Instr function. You can then extract the fields using the Mid function. Then you can either call insert statements directly using conn.execute(sql) where sql is "insert into DAY"...etc) or write a stored procedure that does the actual inserts and call the stored procedure using ADO command and parameter objects.

The first one is explained by "johnhyde"

An example with using ado:

Something like this...

  Dim cn As New ADODB.Connection 'Connection object
    Dim rs As New ADODB.Recordset   'Recordset object
    Dim strDBPath As String
    Dim sRecord As String
    Dim iField As Integer
    Dim aFields As Variant
    strDBPath = "C:\FileDB.mdb"
    'Set the native OLEDB provider for Jet (Access database)
    cn.Provider = "Microsoft.Jet.OLEDB.3.51"
    'Open connection for FileDB database
    cn.Open strDBPath
    'Open recordset for the MyTable table
    rs.Open "Select * from MyTable", cn, adOpenDynaset

    Open "C:\FlatFile.dat" For Input As 1    
    While Not EOF(1)
    Line Input #1, sRecord
    'Assuming a Comma Delimiter
    aFields = Split(sRecord, ",")
    For iField = 0 To UBound(aFields)
        rs(iField) = aFields(iField)

    Set rs = Nothing
    Set cn = Nothing
OzheeAuthor Commented:
Thank's Very much Sir :)
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.

All Courses

From novice to tech pro — start learning today.