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

[ken]

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!

[Ilyan Mishiyev]

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!

[ken]

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

[Robert Gelb]

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

[Paul Clement]

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)