Databases (VB 6)

Topic

New Reply

Hi! I hope someone can help or point me in the right direction here. I am working on a vb app and I need to import a text file into the program(database) and I don’t have a clue as to how to do this. My app has a database named layouts that contains 4 fields one of which is a part number field. I need to match that part number field up to the part number field in a text file (the text file is generated by a proprietary system and there’s no other way of getting the info out.) and only display those matching records. (If theres a part number in the layouts database, I need only the matching records from the text file.)
The text file is a +-sign delimited file and the user needs to be able to press an Update button and have this file imported into the program and displayed in a table-like fashion or grid. The info in the text file is read only and does not need to be edited.
I am a vb newbie and really have no idea where to even begin on this. ALL help is VERY much appreciated! Thank You!
Kris

Reply | Quote

Replies

Are you actually using a table in a Jet/Access database as the back end for this, or are you storing the data in a file named layouts or somewhere else?

Reply | Quote

Yes. I created a database using Visdata within VB6. (Microsoft Jet 4.0 OLE DB Provider)

Reply | Quote

You might find this thread on importing an Excel worksheet into Access using VB of interest. It presumes you have a copy of Access so you can use the Transfer command, but would save you hours of grief in trying to write an import process that would convert your text to an Access table.

Reply | Quote

I would love to just do this in Access since I already have the database with 2 tables setup and made Access do the SQL queries for me, but the Boss wants a VB app…sigh…Thanks for the link though. (P.S. This project did begin as an Excel spreadsheet.)

Reply | Quote

Actually the thread I pointed you to is doing the transfer from VB, but is using the Access command under the covers. That might just satisfy your boss – unless you ultimately have to run it on a PC that does not have Access 2000 or 2002 installed.

Reply | Quote

Since it is just a text file, I would not use OLE to read it. I would use an Open statement, Line Input statements, and the Split function.

To give more information than that, I would need to know more about the text file, especially what you mean by +- sign deliminated. Can you put several lines into Word, change any words/data that are proprietary, and attach it to a reply to this post? Thanks! –Sam

Reply | Quote

Ok, attached is a sample of the data I need to import. Thanks for the help!

Reply | Quote

(I see no attachment to your post)

To summarize what Sam and Wendell have been telling you:

1. If you users have Access, you can use Automation to let Access do things for you from within your VB6 application. An advantage of this is that Access can import a text file into a table using the DoCmd.TransferText method; you don’t have to read the text file line by line.
2. If your users don’t have Access or if if using Access (even “under cover”) is not an option:
   • Use the Open instruction to open the text file, and read it line by line using the Input or Line Input instructions.
   • Use ADODB (or DAO) to open the appropriate table in the database as a recordset, append a record for each line read from the text file, and write the values to the fields of the record.

It’s more work than using Access, but it shouldn’t be too hard. Look up “Open statement”, “Input statement” and “Line input statement” in the help index to see how you can open and read a text file. Look up “AddNew method” to see how you can add a record to a recordset.
[/list]

Reply | Quote

Try the attachment again. Preview your message, then Browse for the attachment, then immediately Post It. If you preview again, you loose the attachment. –Sam

Reply | Quote

Thanks for the help. Maybe this time I can get the attachment here
Using Access is completely out of the question unfortunately. This all has to be done in VB.

Kris

Reply | Quote

That is VERY UGLY. Do they really have variable length fields, missing months, missing dollar signs, etc? What do the + signs mean? You originally said it was plus/minus sign deliminated? Do they throw in minus signs also? Are the numbers ever negative?

Except for the missing month, it seems that, if you ignore $ signs and plus signs, then the file is space deliminated. Is this true? –Sam

Reply | Quote

Yep, sure is ugly! The fields are fixed length and I don’t know why the + signs show up several spaces after unless the program thats exporting the data is setup to do that. (No minus signs – I misinterpreted the file wrong, my mistake.)There can be no negative numbers in any of the fields.
I have no clue how to parse the fields out either!

Kris

Reply | Quote

Well, if they are really fixed-length, then it’s easy. Check to see if they are really fixed length by trying to open the file in Excel and selecting fixed length records. Can you adjust the widths so that all of the fields come in OK. If so, record the lengths and post them. –Sam

Reply | Quote

Ok, now I’m slightly confused. Anyway, I could not get Excel to import the data as fixed length, I had to change it to delimited using the plus sign as the delimiter. If you import as fixed length it seperates all the plus signs into their own columns. (Attached is the spreadsheet)

Kris

Reply | Quote

> it seperates all the plus signs into their own columns

But, you can double-click on a line to remove that breakpoint. Notice that with your sample data the numbers seem to be shifted to the left on each line. I assumed that you had a larger file that you could experiment with. Before you try the import again, edit the file with Notepad and replace all of the plus-signs with blanks. We need a firm input specification before we can write code. –Sam

Reply | Quote

Ok, I removed the plus signs and re-imported the file into Excel. It’s attached.

Kris

Reply | Quote

Maybe this code can serve as starting point. It uses the originally attached text file with “+” as separator. And it’s written for DAO, because that’s what I’m most familiar with, but modifying it for ADO shoudn’t be difficult. As it stands, it needs a reference to the DAO object library.

To make it work better, you need to add data cleaning. For instance, there are some very strange dates in the text file.

Private Sub Command1_Click()
Dim strLine As String
Dim strFields
Dim i As Integer
Dim wsp As DAO.Workspace
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Err_Command1_Click

Set wsp = DAO.DBEngine.Workspaces(0)
Set dbs = wsp.OpenDatabase(“C:MyProjectMyDatabase.mdb”)
Set rst = dbs.OpenRecordset(“tblMyTable”)

Open “C:MyProjectMyText.txt” For Input As #1
‘ Get field names
Line Input #1, strLine
strFields = Split(strLine, “+”)
‘ Get records
Do While Not EOF(1)
Line Input #1, strLine
strFields = Split(strLine, “+”)
‘ Add record to table
rst.AddNew
For i = LBound(strFields) To UBound(strFields)
rst.Fields(i) = strFields(i)
Next i
rst.Update
Loop

Exit_Command1_Click:
‘ Clean up
On Error Resume Next
Close #1
rst.Close
Set rst = Nothing
Set dbs = Nothing
Set wsp = Nothing
Erase strFields
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click
End Sub

Reply | Quote

I finally found the time to put some code together. I used the Add In | Visual Data Manager to create an Access database with one table with your 8 fields. Then I used the Project | Add Form | VB Data Form Wizard to create a form for that table and fields (remember to place the fields in the same order as in the text file) without any controls. This creates a form with an array of text boxes, txtFields, which are bound to your database via an ADO control, datPrimaryRS. I added a button to the form to read your file and replaced all of the code that the wizard generated with the code below. It follows the scheme of parsing on the plus sign. Note that I did no error checking. From the looks of your file, you will want to add alot of that. HTH –Sam

Option Explicit
Private Sub Command1_Click()
Dim s As String
Dim v As Variant
Dim i As Integer
    Open App.Path & "New.txt" For Input As #1
    Line Input #1, s    ' Skip header
    Do
        Line Input #1, s
        s = Replace(s, "$", " ") ' Pitch the dollar signs
        v = Split(s, "+")   ' Parse with plus sign deliminator
        datPrimaryRS.Recordset.AddNew
        For i = 0 To txtFields.Count - 1
            txtFields(i) = Trim(v(i))
        Next i
        datPrimaryRS.Recordset.Update
    Loop Until EOF(1)
    Close #1
End Sub

Reply | Quote

Thank you everyone for your help!!
Another question?? Can I delete the recordset everytime before updating it?

Kris

Reply | Quote

I don’t think that I understand the question. Do you mean that you want to delete all of the old records in the database before opening the file and adding the new lines to the database?

Reply | Quote

Yes. Everytime the user clicks the Update button the database is emptied of previous data.

Kris

Reply | Quote