More on Importing text data...


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: More on Importing text data...

  1. #1
    Manuel De Leon Guest

    More on Importing text data...


    Very nice. Is there a way to import an entire text file into an SQL Server
    or ORACLE database in one step? (like the one you just used)

    Thanks,
    Manuel

    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >On 4 Oct 2001 08:37:42 -0700, "John D" <Aircity@aol.com> wrote:
    >
    >
    > Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    > >On 3 Oct 2001 15:45:12 -0700, "John D" <aircity@aol.com> wrote:
    > >
    > >
    > >>
    > >Paul ~~~ pclement@ameritech.net
    > >Microsoft MVP (Visual Basic)
    > First I want to thank you for your response Paul.
    > I'm not getting any error messages. The data is simply not in access.
    > However I did a model in DAO and it works fine. I'm just not doing something
    > right in the ADO model.
    > The text data looks something like this!
    > 01Perez Paul M 91884 04593859898899M98977Miami Unified 133499Jackson

    High
    > I simply want to parse this data into an ACCESS table.
    > Thankyou much
    > John
    >
    >
    >Below is how I would do it using ADO and a Schema.INI file. You would simply

    need to
    >modify the
    >Schema.INI file to support the structure of your text file.
    >
    >Sub ImportTextToAccessADO()
    >
    >Dim cnn As New ADODB.Connection
    >Dim sqlString As String
    >
    >cnn.Open _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=d:\My Documents\DB1.mdb;" & _
    > "Jet OLEDB:Engine Type=4;"
    >
    >sqlString = "SELECT * INTO [tblPeople] FROM [Text;DATABASE=d:\My Documents\TextFiles].[People.txt]"
    >
    >cnn.Execute sqlString
    >
    >End Sub
    >
    >Contents 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
    >
    >For more on Schema.INI files:
    >
    >http://msdn.microsoft.com/library/de...a_ini_file.asp
    >
    >
    >Paul ~~~ pclement@ameritech.net
    >Microsoft MVP (Visual Basic)



  2. #2
    Paul Clement Guest

    Re: More on Importing text data...

    On 4 Oct 2001 14:29:07 -0700, "Manuel De Leon" <manuel@codetel.net.do> wrote:


    Very nice. Is there a way to import an entire text file into an SQL Server
    or ORACLE database in one step? (like the one you just used)


    You should be able to use the method below. The primary difference will be the connection string
    (SQL Server instead of Access) and possibly your SQL statement (SQL Server table name).


    >Below is how I would do it using ADO and a Schema.INI file. You would simply
    need to
    >modify the
    >Schema.INI file to support the structure of your text file.
    >
    >Sub ImportTextToAccessADO()
    >
    >Dim cnn As New ADODB.Connection
    >Dim sqlString As String
    >
    >cnn.Open _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=d:\My Documents\DB1.mdb;" & _
    > "Jet OLEDB:Engine Type=4;"
    >
    >sqlString = "SELECT * INTO [tblPeople] FROM [Text;DATABASE=d:\My Documents\TextFiles].[People.txt]"
    >
    >cnn.Execute sqlString
    >
    >End Sub
    >
    >Contents 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
    >
    >For more on Schema.INI files:
    >
    >http://msdn.microsoft.com/library/de...a_ini_file.asp
    >
    >
    >Paul ~~~ pclement@ameritech.net
    >Microsoft MVP (Visual Basic)


    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