[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB Application to Import selected text Files into SQL Database

Posted on 2009-04-21
20
Medium Priority
?
640 Views
Last Modified: 2013-11-26
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
Comment
Question by:stefan1981
  • 10
  • 9
19 Comments
 
LVL 12

Expert Comment

by:wht1986
ID: 24201721
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
 

Author Comment

by:stefan1981
ID: 24206408
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
 
LVL 12

Expert Comment

by:wht1986
ID: 24206555
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:stefan1981
ID: 24209497
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
 
LVL 12

Expert Comment

by:wht1986
ID: 24211300
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
 

Author Comment

by:stefan1981
ID: 24213291
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
 
LVL 12

Expert Comment

by:wht1986
ID: 24213896
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
 
LVL 12

Expert Comment

by:wht1986
ID: 24213928
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
 

Author Comment

by:stefan1981
ID: 24220364
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
 
LVL 12

Expert Comment

by:wht1986
ID: 24221149
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
 

Author Comment

by:stefan1981
ID: 24223630
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
 

Author Comment

by:stefan1981
ID: 24223661
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
 
LVL 12

Expert Comment

by:wht1986
ID: 24224806
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
 

Author Comment

by:stefan1981
ID: 24228925
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
 
LVL 12

Expert Comment

by:wht1986
ID: 24230540
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
 
LVL 12

Expert Comment

by:wht1986
ID: 24230698
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
 

Author Comment

by:stefan1981
ID: 24233767
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
 

Author Comment

by:stefan1981
ID: 24233865
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
 
LVL 12

Accepted Solution

by:
wht1986 earned 1000 total points
ID: 24234455
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
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 Month18 days, 10 hours left to enroll

834 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