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
/****** Object: Table [dbo].[tblMeterInspections] Script Date: 04/22/2009 00:25:04 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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]
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
strFixed = strFixed & VbCrLf & strLine
strFixed = strFixed & strLine
Set objInputFile = Nothing
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
Set objOutputFile = Nothing
MsgBox "Done and Dusted. Now please see " & strOutputFile