Solved

MySQL & Visual Basic 6.0

Posted on 2011-09-04
5
526 Views
Last Modified: 2012-05-12
I'll introduce the criteria step by step.

If you are able to achieve this using only MySQL that is OK.  
I happen to have VB 6.0, so if you need to write a routine in it i can use it.

I need to add text files from my local computer to a MySQL Database table that is online in my websites cpanel.
I have close to two hundred local text files.

Inserting the text files name into field2,
Inserting the text of the file into field3,
and inserting its ID number into field1.
The ID number is in another online table with the text files name as a reference.
The text itself is one page long.


MyDocuments Folder1: (all files in folder needed)
    FileName1.txt
        Text one page long
    FileName2.txt
        Text one page long
    FileName3.txt
        Text one page long
    .
    .
    .


NEW RECORDS NEEDED
MySQL Database Table: (insert new records.)
    Field1       Field2          Field3
       16          FileName1      Text from file  
       28          FileName2      Text from file  
         7          FileName3      Text from file  

MySQL Database ID Number Reference Table:
      ID       Description
        16       FileName1
        28       FileName2
                       7       FileName3
Thank You
0
Comment
Question by:GarySB
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:Nitin Sontakke
ID: 36482314
Hello,

First of all, i hope you are aware that the on-line database will not be able to access any of resources which are local to your computer (to the best of my current knowledge) for the purpose of security.

As such you will need to generate the INSERT scripts locally taking into consideration all of the necessary data and EXECUTE these in online database - assuming you have all the necessary permissions / access details with you.

Assuming you know VB 6 or VBScript, you can write the script to a text file.

You don't have to worry about the Id column as this information with a key field is available in target database.

Your script for one file should look like:

INSERT INTO Table1 (FIELD1, FIELD2, FIELD3)
SELECT ID, 'Filename1', 'file text' FROM Table2 WHERE filename = 'Filename1';

It is assumed that the file names are unique.

Hope it helps.
0
 

Author Comment

by:GarySB
ID: 36489005
I have all required permissions to add/modify my database.

I think the best approach is to generate a script on my local computer and save it as a text file that i will paste into the online cpanl area to run and insert the records.

In VB6, can you give me a routine that will loop and read my local text files embeding the filename and text around the script syntax needed to insert records?
0
 
LVL 3

Accepted Solution

by:
nrbreen earned 250 total points
ID: 36509599

Sub write_files_to_sql()
Dim fs As Integer, fd As Integer
Dim filename As String, filenumber As Integer
Dim foldername As String, SQLfilename As String
Dim line As String

foldername = "c:\MyDocuments\"   '  Note trailing slash
SQLfilename = "inserts.sql"   ' do not include a path in this variable

fd = FreeFile
Open foldername & "inserts.sql" For Output As fd

fs = FreeFile
filename = Dir(foldername)
While filename <> ""
  If filename <> SQLfilename Then
    filenumber = filenumber + 1
    Open foldername & filename For Input As fs
        '  change the names of the tables if required : names_table, texts_table
    Print #fd, "insert into names_table values(" & filenumber & ", '" & filename & "');"
    Print #fd, "insert into texts_table values(" & filenumber & ", '" & filename & "', '";
    While Not EOF(fs)
      Line Input #fs, line
      Print #fd, Replace(line, "'", "''")    ' must handle embedded single quotes
    Wend
    Print #fd, "');"
    Close fs
  End If
  filename = Dir()
Wend
Close fd

End Sub

Open in new window

0
 
LVL 6

Assisted Solution

by:Nitin Sontakke
Nitin Sontakke earned 250 total points
ID: 36528682
Hello,

Here is the code i believe should serve the purpose. Please note, however, i have not tested it.

Private Sub Command1_Click()
    Dim fso As FileSystemObject, fld As Folder, f As File, fs As TextStream, strText As String
    Dim out As Integer, strOut As String
    out = FreeFile
    Open "D:\TempFolder\Output.sql" For Append Access Write As #out
    Set fso = New FileSystemObject
    Set fld = fso.GetFolder("D:\TempFolder\")   'Put desired folder name here.
    For Each f In fld.Files
        If InStr(1, f.Name, ".txt", vbTextCompare) <> 0 Then
            Set fs = f.OpenAsTextStream(ForReading)
            strText = fs.ReadAll
            fs.Close
        End If
        strOut = "INSERT INTO Table1 (FIELD1, FIELD2, FIELD3) SELECT ID, '" & f.Name & "', '" & Replace(strText, "'", "''") & "' FROM Table2 WHERE filename = '" & f.Name & "';"
        Print #out, strOut
    Next
    Close #out
End Sub

Open in new window


You will need to add the reference to "Microsoft Scripting Runtime" in your VB project.

Any questions, please do let me know.
0
 

Author Closing Comment

by:GarySB
ID: 36586663
Because of time i was not able to test the code and had to load manually.  I will try them out.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now