[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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,
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

656 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