How to import Text file into SQL DB using VB

Posted on 2003-03-04
Medium Priority
Last Modified: 2010-04-07
Can someone please provide a code to import a textfile into a SQL DB. The text file is in the following format.

User ID, GroupName , password
Its a MS-SQL DB.
Question by:brajar01
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

Accepted Solution

sjpedro earned 100 total points
ID: 8066553
Have you tried using the data transformation services?  If yuo are usning SQL server 7 or later, you could use the data transformatin services to create a dts package and call it from VB.  The DTS package allows you import the text file and break based on the file delimiters.  The code needed to call the DTS package is in the help files.

Assisted Solution

Da_Weasel earned 100 total points
ID: 8067064
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.

Expert Comment

ID: 8068152
Try this pseudo-code

good luck


Set cnn = New ADODB.Connection
cnn.Open <ConnectionString>
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = cnn
rst.CursorLocation = adUseClient
rst.Open "SELECT * FROM <Table> WHERE 1=2", , _
              adOpenStatic, adLockBatchOptimistic
Set rst.ActiveConnection = Nothing
' Import file
intFile = FreeFile()
Open <FileName> For Input As #intFile
Do While Not EOF(intFile)
     Input #intFile, strLine
     ' Parser the Line
     strUserID = <>
     strGroupName = <>
     strPassword = <>

     ' Add the record
     rst.Fields("UserID").Value = strUserID
     rst.Fields("GroupName").Value = strGroupName
     rst.Fields("Password").Value = strPassword
Close #intFile

rst.ActiveConnection = cnn
' Delete file
Kill <FileName>    

' Liberar Objetos
Set rst = Nothing
Set cnn = Nothing

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


Expert Comment

ID: 8072610
Why parse the file manually when it can be done with ADO, and you can use a single SQL stament to pull all of the data in?

Expert Comment

ID: 8900714
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
LVL 49

Expert Comment

ID: 9046799
Moderator, my recommended disposition is:

    Split points between: sjpedro and Da_Weasel

DanRollins -- EE database cleanup volunteer

Featured Post

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

741 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