[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1076
  • Last Modified:

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
OZ
0
Ozhee
Asked:
Ozhee
  • 3
  • 3
1 Solution
 
DhaestCommented:
1) Also have a look at (for import to SQL Server):

http://www.win2000mag.com/Articles/Index.cfm?ArticleID=494
(look for import)

http://www.freevbcode.com/ShowCode.Asp?ID=1629
(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
        .AddNew
        !Scrip = Scrip
        !Rate = CPrice
        !Date = mDt
        .Update
        .MoveFirst
        If mCnt Mod 25 = 0 Then Me.Caption = mCnt & " Closing Prices found"
    End With
Loop
Me.Caption = mCnt & " Closing Prices found"
Close
rsCP.Close

3) Here are some links that do the work

http://www.d2dsources.com/ViewSource.aspx?Type=VB&ID=102161
0
 
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 ?

Thank's
0
 
johnhydeCommented:
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,
Regards,
John.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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..
0
 
DhaestCommented:
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, ",")
    rs.AddNew
    For iField = 0 To UBound(aFields)
        rs(iField) = aFields(iField)
    Next
    rs.Update
    Wend

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
0
 
DhaestCommented:
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:
[filename.tsv]
ColNameHeader=True
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.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/workingwithtextfiles.asp


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.



http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_10253623.html?query=import+TextFile+into+Database&searchType=topic#2321485
0
 
OzheeAuthor Commented:
Thank's Very much Sir :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now