How do I export delimited text files to Access or SQL server db in VB?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: How do I export delimited text files to Access or SQL server db in VB?

  1. #1
    ken Guest

    How do I export delimited text files to Access or SQL server db in VB?


    In my VB project I need to export big commercial delimited text record file
    to ACCESS or SQL server database, how do I deal with it? Are there special
    tool or utility software to do it? Thanks!

  2. #2
    Ilyan Mishiyev Guest

    Re: How do I export delimited text files to Access or SQL server db in VB?


    You can use DTS (Data Transformation Services), the best tool I've ever used.
    You can find it in SQL Server.
    Also, you can use DTS programmatically from VB.

    -Ilyan

    "ken" <kzhao@calltrol.com> wrote:
    >
    >In my VB project I need to export big commercial delimited text record file
    >to ACCESS or SQL server database, how do I deal with it? Are there special
    >tool or utility software to do it? Thanks!



  3. #3
    ken Guest

    Re: How do I export delimited text files to Access or SQL server db in VB?


    Ilyan,

    Thank for your response! But could you tell you more detailed information
    how to deal with it in VB? I am not so familiar with DTS.

    "Ilyan Mishiyev" <mishiyev@hotmail.com> wrote:
    >
    >You can use DTS (Data Transformation Services), the best tool I've ever

    used.
    >You can find it in SQL Server.
    >Also, you can use DTS programmatically from VB.
    >
    >-Ilyan
    >
    >"ken" <kzhao@calltrol.com> wrote:
    >>
    >>In my VB project I need to export big commercial delimited text record

    file
    >>to ACCESS or SQL server database, how do I deal with it? Are there special
    >>tool or utility software to do it? Thanks!

    >



  4. #4
    Robert Gelb Guest

    Re: How do I export delimited text files to Access or SQL server db in VB?

    I normally use BCP/Bulk Insert. There is a sample class & implementation.

    http://www.vbrad.com/pf.asp?p=Source/src_bcp.htm

    Of course, if just need to do this once, use DTS. Start the "Export &
    Import Data" application in the SQL Server group.

    --
    Robert Gelb
    www.vbRad.com
    Source Code, Tips, Tricks, Components


    "ken" <kzhao@calltrol.com> wrote in message news:3b01512b$1@news.devx.com...
    >
    > Ilyan,
    >
    > Thank for your response! But could you tell you more detailed information
    > how to deal with it in VB? I am not so familiar with DTS.
    >
    > "Ilyan Mishiyev" <mishiyev@hotmail.com> wrote:
    > >
    > >You can use DTS (Data Transformation Services), the best tool I've ever

    > used.
    > >You can find it in SQL Server.
    > >Also, you can use DTS programmatically from VB.
    > >
    > >-Ilyan
    > >
    > >"ken" <kzhao@calltrol.com> wrote:
    > >>
    > >>In my VB project I need to export big commercial delimited text record

    > file
    > >>to ACCESS or SQL server database, how do I deal with it? Are there

    special
    > >>tool or utility software to do it? Thanks!

    > >

    >




  5. #5
    Paul Clement Guest

    Re: How do I export delimited text files to Access or SQL server db in VB?

    On 15 May 2001 08:28:09 -0700, "ken" <kzhao@calltrol.com> wrote:


    In my VB project I need to export big commercial delimited text record file
    to ACCESS or SQL server database, how do I deal with it? Are there special
    tool or utility software to do it? Thanks!

    You can also do this in code with ADO or DAO and a schema.ini file:

    Sub ImportTextToAccessADO()

    Dim cnn As New ADODB.Connection
    Dim sqlString As String

    cnn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\My Documents\DB1.mdb;" & _
    "Jet OLEDB:Engine Type=4;"

    sqlString = "SELECT * INTO [tblPeople] FROM [Text;DATABASE=C:\My Documents\TextFiles].[People.txt]"

    cnn.Execute sqlString

    End Sub

    From Access (but can be modified for VB):

    Sub ImportTextToAccess()

    Dim db As DAO.Database
    Dim sqlString As String

    Set db = CurrentDb

    sqlString = "SELECT * INTO [tblPeople] FROM [Text;DATABASE=C:\My Documents\TextFiles].[People.txt]"
    db.Execute sqlString

    End Sub

    Example of schema.ini file:

    [People.txt]
    ColNameHeader=False
    Format=FixedLength
    CharacterSet=ANSI
    Col1=ID long width 11
    Col2=LastName text width 30
    Col3=FirstName text width 20

    Info on schema.ini files:

    http://msdn.microsoft.com/library/bo...c8_body_34.htm


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center