• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

How to import Text file into SQL DB using VB

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.
2 Solutions
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.
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.
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.

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?
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?
Moderator, my recommended disposition is:

    Split points between: sjpedro and Da_Weasel

DanRollins -- EE database cleanup volunteer

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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