Export Text Files into Database

Posted on 2004-04-12
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
  • 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,
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why


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 100 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 VB code 9 115
VBA : retain and exclude values based on a config sheet and multiple parameters 5 61
VBA filters 2 61
Error with a code discussed on this page 5 14
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

773 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