Export Text Files into Database

Posted on 2004-04-12
Medium Priority
Last Modified: 2013-12-25
Hi Experts,

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

Thank's be 4
Question by:Ozhee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 53

Expert Comment

ID: 10810685
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


Author Comment

ID: 10810871
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 ?


Expert Comment

ID: 10814545
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,
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 10819244
thank's John, but i wanna do is juct click in my menu in VB program, it's mean , i want in code program..
LVL 53

Expert Comment

ID: 10820294
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
LVL 53

Accepted Solution

Dhaest earned 300 total points
ID: 10820299
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.


Author Comment

ID: 10820755
Thank's Very much Sir :)

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month14 days, 2 hours left to enroll

800 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