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

VB Application to Import selected text Files into SQL Database

Hi

I hope someone can help me out and point me in the direction I need ot be.  If anyone has anything they have used before or examples that have that may be useful it will be appreciated.  
Basically I am currenlty looking at trying to come up with a VB Executable Application using .Net or VB6 in Excel (User Form) so I can select from a folder particular files (text files) and import these into relevant table in SQL Database.

I have 2 files I am currently receving I need to start importing regular.  They need to go into SQL Database.  I currently use DTS package but this will not be an option soon.  I am after a way of having a view where files located in the chosen folder are able to be selected and imported.

Within the MIN file attached are 2 record types per tranasction.  M96 has 3 preceeding fields and S70 has 8 preceeding fields.  These 2 lines are to be imported into one.  So where field 1 = M96 import field 2, 3 and 4 inot relevant fields in table in SQL and same goes for line beginning with S70.

One thing I would like to be able to do if have this able to take into consideration other files I may receive.

How do I write procedures in vb for particular record lines that begin with unique values.  i.e if it is M96 then import here if it is M90 then import here etc and then relate the particular record lines to ceetsain files so in case of this example MIN file selected do the following.

One thing to note is within each file are header (A00) and Footer (Z99) which are not to be taken into consideration but ignored.

The attached text file should have file extension of .MIN but I changed it to .txt for purpose of uploading only

I hope you understand where I amcoming from and I hope someone can help
Code for SQL Table for MIN Data
 
USE [TestDatabase]
GO
 
/****** Object:  Table [dbo].[tblMeterInspections]    Script Date: 04/22/2009 00:25:04 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[tblMeterInspections](
	[MPR] [nchar](15) NULL,
	[SerialNumber] [nchar](15) NULL,
	[NextInspectDate] [smalldatetime] NULL,
	[BuildingNumber] [nchar](4) NULL,
	[SubBuildingName] [nchar](30) NULL,
	[BuildingName] [nchar](50) NULL,
	[Street] [nchar](35) NULL,
	[Locality] [nchar](35) NULL,
	[PostTown] [nchar](35) NULL,
	[PostCodeOut] [nchar](4) NULL,
	[PostCodeIn] [nchar](4) NULL
) ON [PRIMARY]
 
GO
 
I created this for another purpose of manipulating text files to be reimported.  This was used to try and amalgamte the 2 different lines into one line so I could import it straight into SQL using DTS
strInputFile = "C:\temp\example.txt"
strOutputFile = "C:\temp\example_Fixed.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1
Set objInputFile = objFSO.OpenTextFile(strInputFile, intForReading, False)
strFixed = ""
While Not objInputFile.AtEndOfStream
	strLine = objInputFile.ReadLine
	If Left(strLine, 5) = ""RVI,"" Then
		If strFixed = "" Then
			strFixed = strLine
		Else
			strFixed = strFixed & VbCrLf & strLine
		End If
	Else
		strFixed = strFixed & strLine
	End If
Wend
objInputFile.Close
Set objInputFile = Nothing
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
objOutputFile.Write strFixed
objOutputFile.Close
Set objOutputFile = Nothing
MsgBox "Done and Dusted. Now please see " & strOutputFile

Open in new window

PN000011.txt
0
stefan1981
Asked:
stefan1981
  • 10
  • 9
1 Solution
 
wht1986Commented:
Heres a quick example that parses the text, skipping header and footer and builds all the parameters over the 2 line file transaction to then do an insert query. Its not perfect by any means but it should get you going in the right direction.  You may have to add checks to make sure commas dont show up in the strings themselves, etc plus i just assumed the column order of the table was the order of the records in the file.  Hope it helps.
private void button2_Click(object sender, EventArgs e)
{
    using (StreamReader sr = File.OpenText("TextFile1.txt"))
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" INSERT INTO tblMeterInspections ");
        sb.Append(" (MPR, SerialNumber, NextInspectDate, BuildingNumber, SubBuildingName, BuildingName, Street, Locality, PostTown, PostCodeOut, PostCodeIn) ");
        sb.Append(" VALUES ");
        sb.Append(" (@MPR, @SerialNumber, @NextInspectDate, @BuildingNumber, @SubBuildingName, @BuildingName, @Street, @Locality, @PostTown, @PostCodeOut, @PostCodeIn) ");
 
        using (SqlConnection conn = new SqlConnection("your connection string"))
        {
            conn.Open();
 
            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
            cmd.Parameters.Add("@MPR", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@SerialNumber", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@NextInspectDate", SqlDbType.SmallDateTime);
            cmd.Parameters.Add("@BuildingNumber", SqlDbType.NChar, 4);
            cmd.Parameters.Add("@SubBuildingName", SqlDbType.NChar, 30);
            cmd.Parameters.Add("@BuildingName", SqlDbType.NChar, 50);
            cmd.Parameters.Add("@Street", SqlDbType.NChar, 35);
            cmd.Parameters.Add("@Locality", SqlDbType.NChar, 35);
            cmd.Parameters.Add("@PostTown", SqlDbType.NChar, 35);
            cmd.Parameters.Add("@PostCodeOut", SqlDbType.NChar, 4);
            cmd.Parameters.Add("@PostCodeIn", SqlDbType.NChar, 4);
 
            string[] parts = null;
 
            while (!sr.EndOfStream)
            {
                string line = sr.ReadLine();
                parts = line.Split(',');
                switch (parts[0])
                {
                    case "\"A00\"":  // header do nothing
                        break;
                    case "\"Z99\"":  // footer do nothing
                        break;
                    case "\"M96\"":  // process M96 row and update some of the variable values
                        cmd.Parameters["@MPR"].Value = parts[1];
                        cmd.Parameters["@SerialNumber"].Value = parts[2];
 
                        if (string.IsNullOrEmpty(parts[3]))
                            cmd.Parameters["@NextInspectDate"].Value = DBNull.Value;
                        else
                        {
                            cmd.Parameters["@NextInspectDate"].Value =
                                new DateTime(
                                    int.Parse(parts[3].Substring(1, 4)),
                                    int.Parse(parts[3].Substring(4, 2)),
                                    int.Parse(parts[3].Substring(6, 2)));
                        }
                        break;
                    case "\"S70\"":  // process S70 row
                        cmd.Parameters["@BuildingNumber"].Value = parts[1];
                        cmd.Parameters["@SubBuildingName"].Value = parts[2];
                        cmd.Parameters["@BuildingName"].Value = parts[3];
                        cmd.Parameters["@Street"].Value = parts[4];
                        cmd.Parameters["@Locality"].Value = parts[5];
                        cmd.Parameters["@PostTown"].Value = parts[6];
                        cmd.Parameters["@PostCodeOut"].Value = parts[7];
                        cmd.Parameters["@PostCodeIn"].Value = parts[8];
 
 
                        // insert the record
                        cmd.ExecuteNonQuery();
 
                        break;
                }
            }
            conn.Close();
        }
        sr.Close();
    }
}

Open in new window

0
 
stefan1981Author Commented:
Hi

Thanks I think for above.  One question how am I best running this, what should I use.  As I don't want to have much Manual Alterations to import hese files what is the best way of incorporating ability to just import a particular file.  All files are received  as PN(6digits).MIN.  Theoritically they are text files but the extension is not .txt.

Does this make any difference.  I would like it if the code was generic fdor any file where extension end .MIN
0
 
wht1986Commented:
this c# code gets all the filenames from the C:\ directory that have the MIN extension

Just loop through each one and parse each file

foreach (string filename in Directory.GetFiles("C:\\", "*.MIN"))
{
    using (StreamReader sr = File.OpenText(filename))
    {
       // all the code from before in here
    }
}
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
stefan1981Author Commented:
Hi

Not having any luck with this whatso ever.  I have no insight at all into C# only VB6 and currnetly going thorugh VB.Net and ASP.Net intro.

Any ideas for a complete newbie at c#.

Not sure on programmibility and language which is where I am going wrong
0
 
wht1986Commented:
Here is a fully functional project. Just change the connection string in the settings to match your database.  You'll need to add error handling etc, but it should get you started

http://www.kevinwhite.net/minparse.zip
0
 
stefan1981Author Commented:
Hi

Having trouble with link

I am being presented with following within IE

Webhost4life Application Firewall Alert


Your request triggered an alert! If you feel that you have received this page in error, please contact the administrator of this web site.  
0
 
wht1986Commented:
It's weird I get that when i click on the link, but if i copy and paste the URL into the address bar of the browser it works fine. Can you try that, otherwise i can just email it to you.
0
 
wht1986Commented:
actually clicking the link should work now, my hosting provider changed their security settings, and i just modified my options through their control panel.
0
 
stefan1981Author Commented:
Hi

Thanks so much, I have gone through it with fine tooth comb tonight to fully understand it and I couldn't be any more happier.  One further question I do have.  
I included a snippet of vb script on original post which is used to parse a particular file I receive in my inbox.  This is similar to MIN file.  In this case the extensionis RD1.  Within RD1 is 2 records RVI and RVV these work same as M96 and S70.  Problem is in this case every transaction has RVI record with some transactions having RVV record.  How woukld I go about amending project to include these.  I uderstand how to parse the records together ie RVV record to RVI but the snippet above does not work the way I want.  Sometimes these files contain 2 or more RVV records per 1 RVI record.

If you can help I would be really grateful and points increase would be given for your much appreciated help.

Biggest issue I have is I am happy to work the way I have been but in my line of work there is only me currently in entire office that can work on these using SQL, Temp Tables etc and so my mission is to try and make this more user friendlyhence so my Boss can decrease my wage!!!
0
 
wht1986Commented:
well question about your RVV and RVI records then. It seems that in this case it is a master - detail scenario, where one RVI can have one or more RVV records.  Are you storing the data by inserting  the RVI record in one table and then storing the RVV record in another, or are both going into a single table where the columns are a combination of RVI and RVV?

Either way the logic is very similar to what i gave in the project.  

Case 1) Each in its own table. For each line parse and store in the appropriate table
Case 2) Appended columns. When you detect a RVI record store to variables all the fields you need. Then every time you hit a subsequent RVV row, you can insert the saved values of the RVI record plus that lines RVV record.  When you get to the next RVI, change the values of the variables being used to hold the RVI record.

If you post a sample text file, and tell me the database table schema, i can give you an eample of how to parse it.
0
 
stefan1981Author Commented:
Hi

Thanks for your further insight

I have added code for SQL to Create Table, this is same as MIN, the table format has been created as sequence of fields on actual file.

A hard copy of file I receive is also attached.  Similar to before the file name extension has been changed to .txt instead of original .RD1 for purpose of attaching only.

As you can see from contents of the file.  Every transaction is signified by record type of RVI , with possibility any of these being followed by RVV record.  The RVV record consists of 3 fields per record, Validation Code, Validation Reason and
USE [TestDatabase]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ReadVisitDetails](
	[SiteRefNum] [varchar](20) NULL,
	[MPR] [varchar](20) NULL,
	[ActualVisitDate] [datetime] NULL,
	[ActualVisitTime] [varchar](12) NULL,
	[ReadReasonCode] [varchar](10) NULL,
	[SerialNumber] [varchar](20) NULL,
	[MeterReading] [varchar](12) NULL,
	[ConvertorSerialNumber] [varchar](20) NULL,
	[ConvertorUncorrectedReading] [nchar](20) NULL,
	[ConvertorCorrectedReading] [nchar](20) NULL,
	[ReadType] [varchar](5) NULL,
	[BypassStatus] [varchar](5) NULL,
	[CollarStatus] [varchar](5) NULL,
	[CappedStatus] [varchar](5) NULL,
	[CrctrCondition] [varchar](5) NULL,
	[InspectorIndicator] [varchar](5) NULL,
	[ManuallyValidated] [varchar](5) NULL,
	[PGTRejectValidation] [varchar](5) NULL,
	[ServiceLevel] [varchar](5) NULL,
	[SendReasonCode] [varchar](5) NULL,
	[ReadSequenceNo] [varchar](5) NULL,
	[MeterLocationCode] [nchar](10) NULL,
	[MeterLocationDescription] [varchar](250) NULL,
	[AccessInstructions] [varchar](250) NULL,
	[MeterZeroCount] [varchar](5) NULL,
	[ConvertorZeroCount] [nchar](10) NULL,
	[MANFileIndicator] [varchar](50) NULL,
	[MeterReadRequestRef] [nvarchar](20) NULL,
	[ValidationCode_A] [varchar](6) NULL,
	[CodeDescription_A] [varchar](max) NULL,
	[MRADescription_A] [varchar](max) NULL,
	[ValidationCode_B] [varchar](6) NULL,
	[CodeDescription_B] [varchar](max) NULL,
	[MRADescription_B] [varchar](max) NULL,
	[ValidationCode_C] [varchar](6) NULL,
	[CodeDescription_C] [varchar](max) NULL,
	[MRADescription_C] [varchar](max) NULL,
	[ValidationCode_D] [varchar](6) NULL,
	[CodeDescription_D] [varchar](max) NULL,
	[MRADescription_D] [varchar](max) NULL,
	[ValidationCode_E] [varchar](6) NULL,
	[CodeDescription_E] [varchar](max) NULL,
	[MRADescription_E] [varchar](max) NULL
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

0
 
stefan1981Author Commented:
Hi

Pressed Submit by accident.

Carrying on from where I left off, the RVV consists of 3 fields per RVV record, Code, Code Descritpion and MRA Description.  Each RVI can contain any number of RVV records but not mandatory and at most can have maximum of 5 .

The code above for the table can be revamped if more efficient fields can be used.  All fields are in sequence for each record type in file i.e RVi record on file each field fits into table in sequence.  Same as RVV all 3 field son file match same order as table.

I was looking at option of having RVV located in seperate table but decided to keep it all within one table.  So keeping it as I have may be better option for me where RVI and RVV records are contained within one table and all on one line per each RVI

Hope this helps

Help much appreciated

PN000281.txt
0
 
wht1986Commented:
Ok its not to bad, i changed how i did it slightly since you said the order is exactly the order of the table and consequently the order of my query. I cant work on anything more today (work issues) but it should give you insight on how to build the row for an insert
using (StreamReader sr = File.OpenText("TextFile1.txt"))
{
    StringBuilder sb = new StringBuilder();
    sb.Append(" INSERT INTO ReadVisitDetails ");
    sb.Append(" (");
    sb.Append("SiteRefNum, MPR, ActualVisitDate, ActualVisitTime, ReadReasonCode, SerialNumber, MeterReading, ConvertorSerialNumber, ConvertorUncorrectedReading, ");
    sb.Append("ConvertorCorrectedReading, ReadType, BypassStatus, CollarStatus, CappedStatus, CrctrCondition, InspectorIndicator, ManuallyValidated, PGTRejectValidation, ");
    sb.Append("ServiceLevel, SendReasonCode, ReadSequenceNo, MeterLocationCode, MeterLocationDescription, AccessInstructions, MeterZeroCount, ConvertorZeroCount, ");
    sb.Append("MANFileIndicator, MeterReadRequestRef, ValidationCode_A, CodeDescription_A, MRADescription_A, ValidationCode_B, CodeDescription_B, MRADescription_B, ");
    sb.Append("ValidationCode_C, CodeDescription_C, MRADescription_C, ValidationCode_D, CodeDescription_D, MRADescription_D, ValidationCode_E, CodeDescription_E, MRADescription_E");
    sb.Append(") ");
    sb.Append(" VALUES ");
    sb.Append(" (");
    sb.Append("@SiteRefNum, @MPR, @ActualVisitDate, @ActualVisitTime, @ReadReasonCode, @SerialNumber, @MeterReading, @ConvertorSerialNumber, ");
    sb.Append("@ConvertorUncorrectedReading, @ConvertorCorrectedReading, @ReadType, @BypassStatus, @CollarStatus, @CappedStatus, @CrctrCondition, ");
    sb.Append("@InspectorIndicator, @ManuallyValidated, @PGTRejectValidation, @ServiceLevel, @SendReasonCode, @ReadSequenceNo, @MeterLocationCode, ");
    sb.Append("@MeterLocationDescription, @AccessInstructions, @MeterZeroCount, @ConvertorZeroCount, @MANFileIndicator, @MeterReadRequestRef, ");
    sb.Append("@ValidationCode_A, @CodeDescription_A, @MRADescription_A, @ValidationCode_B, @CodeDescription_B, @MRADescription_B, @ValidationCode_C, ");
    sb.Append("@CodeDescription_C, @MRADescription_C, @ValidationCode_D, @CodeDescription_D, @MRADescription_D, @ValidationCode_E, @CodeDescription_E, @MRADescription_E");
    sb.Append(") ");
 
    using (SqlConnection conn = new SqlConnection("your connection string"))
    {
        conn.Open();
 
        SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
        cmd.Parameters.Add("@SiteRefNum", SqlDbType.VarChar, 20);
        cmd.Parameters.Add("@MPR", SqlDbType.VarChar, 20);
        cmd.Parameters.Add("@ActualVisitDate", SqlDbType.DateTime);
        cmd.Parameters.Add("@ActualVisitTime", SqlDbType.VarChar, 12);
        cmd.Parameters.Add("@ReadReasonCode", SqlDbType.VarChar, 10);
        cmd.Parameters.Add("@SerialNumber", SqlDbType.VarChar, 20);
        cmd.Parameters.Add("@MeterReading", SqlDbType.VarChar, 12);
        cmd.Parameters.Add("@ConvertorSerialNumber", SqlDbType.VarChar, 20);
        cmd.Parameters.Add("@ConvertorUncorrectedReading", SqlDbType.NChar, 20);
        cmd.Parameters.Add("@ConvertorCorrectedReading", SqlDbType.NChar, 20);
        cmd.Parameters.Add("@ReadType", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@BypassStatus", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@CollarStatus", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@CappedStatus", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@CrctrCondition", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@InspectorIndicator", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@ManuallyValidated", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@PGTRejectValidation", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@ServiceLevel", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@SendReasonCode", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@ReadSequenceNo", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@MeterLocationCode", SqlDbType.NChar, 10);
        cmd.Parameters.Add("@MeterLocationDescription", SqlDbType.VarChar, 250);
        cmd.Parameters.Add("@AccessInstructions", SqlDbType.VarChar, 250);
        cmd.Parameters.Add("@MeterZeroCount", SqlDbType.VarChar, 5);
        cmd.Parameters.Add("@ConvertorZeroCount", SqlDbType.NChar, 10);
        cmd.Parameters.Add("@MANFileIndicator", SqlDbType.VarChar, 50);
        cmd.Parameters.Add("@MeterReadRequestRef", SqlDbType.NVarChar, 20);
        cmd.Parameters.Add("@ValidationCode_A", SqlDbType.VarChar, 6);
        cmd.Parameters.Add("@CodeDescription_A", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@MRADescription_A", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@ValidationCode_B", SqlDbType.VarChar, 6);
        cmd.Parameters.Add("@CodeDescription_B", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@MRADescription_B", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@ValidationCode_C", SqlDbType.VarChar, 6);
        cmd.Parameters.Add("@CodeDescription_C", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@MRADescription_C", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@ValidationCode_D", SqlDbType.VarChar, 6);
        cmd.Parameters.Add("@CodeDescription_D", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@MRADescription_D", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@ValidationCode_E", SqlDbType.VarChar, 6);
        cmd.Parameters.Add("@CodeDescription_E", SqlDbType.VarChar, 4000);
        cmd.Parameters.Add("@MRADescription_E", SqlDbType.VarChar, 4000);
 
        string[] parts = null;
        int RvvParameterIndex = -1;
 
        while (!sr.EndOfStream)
        {
            string line = sr.ReadLine();
            parts = line.Split(',');
            switch (parts[0])
            {
                case "\"A00\"":  // header do nothing
                    break;
                case "\"Z99\"": 
                    // insert the last record
                    cmd.ExecuteNonQuery();
                    break;
 
                case "\"RVI\"":  // process RVI row and update some of the variable values
                    
                    // do we have an open record?
                    if (RvvParameterIndex != -1)
                        cmd.ExecuteNonQuery();
 
                    // just loop through this time since you said they are in order all 27 fields
                    for (int i = 1; i < 28; i++)
                    {
                        // only index 3
                        if (i == 3)
                        {
                            cmd.Parameters[i-1].Value = new DateTime(
                                                        int.Parse(parts[3].Substring(1, 4)),
                                                        int.Parse(parts[3].Substring(4, 2)),
                                                        int.Parse(parts[3].Substring(6, 2)));
                        }
                        else
                            cmd.Parameters[i - 1].Value = parts[i];
                    }
 
                    // set the parameter index
                    RvvParameterIndex = 28;
 
                    break;
 
                case "\"RVV\"":  // process RVV row
                    // loop through and set parameters, keep track of index in case we hit another RVV
                    for (int i = 0; i < 4; i++)
                    {
                        cmd.Parameters[RvvParameterIndex].Value = parts[i];
                        RvvParameterIndex += i;
                    }
                    break;
            }
        }
        conn.Close();
    }
    sr.Close();
}

Open in new window

0
 
stefan1981Author Commented:
Hi

Thanks for above, I have had a look at the above against what you originally submitted for MIN and what you used when creating using VS and trying to do the same using VB and not C#

Any further guidance you can give is much appreciated.

I am 100% confident when it comes to the rvi insert using Vb and the insert for RVI but had do I start with the loop when it comes to possibility that there may be more than 1 RVV record.

Thanks

Stefan
0
 
wht1986Commented:
I have a microbug in the code above because i forgot to reset the index to -1.  I'll will make an example in an hour or so for you in VB.  Actually if you dont mind, I am going to switch to using a DataSet and DataTable as well. Less typing of code for the same affect.
0
 
wht1986Commented:
Here's a fuilly functional project in VB.NET that can read the sample file you provided, parse the lines, and store it into a table matching the schema you provided.

http://www.kevinwhite.net/forstefan.zip

It's more robust than the previous code as it can now skip over commas in the middle of quoted string segments.

Just download it, unzip, and open the VS project. All you need to do is change the connection string in app.config
0
 
stefan1981Author Commented:
Hi

Sorry for delay.

I am struggling with resolving an issue when debugging, when selecting only 1 file there is no problem works superb but when more than1 file is selected to import into DataGrid it comes up with following below.  The issue is with the bottom line, VS shows the error "This row already belongs to this table."

Any ideas.

Also I have taken a look side by side each different version and was wondering if we wanted to combine the 2 projects and amalgamte the 2 version sinto 1 which is easiest program to manipulate, do i reallly need to keep Datagrid view or can i just keep the dataset same as MIN project.

What I was looking at is utilising these 2 programs together on one as I have some of my own projects that use the data that I use on daily basis for exporing formatted text files in response to this data.  I have already added this to the MIN project using another dataset and export button.  Basically the data on RD1 links by first field on MIN and where equals occurrs the relevant data is exported on formatted text file. I created this thorugh VB6 using Access database and Excel front end.
                    If list(0) = "RVI" Then
 
                        ' if we are already building a record just add it back to the dataset
                        If IsNothing(record) = False Then
                            Me.DataSet11.ReadVisitDetails.AddReadVisitDetailsRow(record)

Open in new window

0
 
stefan1981Author Commented:
Hi Kevin

Just a little query, where you ahev created 2 different projects and I am going to merge the 2.  You have 2 datasets Dataset1 on MIN and Dataset11 on RD1 if I merge the 2 datasets and create relationship between the 2 tables in dataset1 or create a 3rd dataset adding in both tables for thepurpose of an new update and select query I need to run

Also with regards to Parse button and Save Database button can these 2 not be combined and code transferred from Save Database to Parse
0
 
wht1986Commented:
yes to all the above. Here is a project which processes all MIN and RD1 files in a selected directory

http://www.kevinwhite.net/forstefan2.zip

My goal at EE was not to provide free contract work but to hopefully provide insight into solving technical issues.  I think through the past examples and code, you should be able to
1) return a list of files based on an extension
2) open the file with a StreamReader
3) parse a comma seperate value line into a data table row
  -- this is includes the ability to concatonate lines into a single record
4) and store that data to a database

Take some time to learn the code. Soon you will be able to create simple code like this in no time at all.

I hope I have been able to answer your question of "VB Application to Import selected text Files into SQL Database" successfully.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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