Vb.Net Push Delimited Data to Different variable types

Question

0.00/5 (No votes)

See more:

Good Morning,

I am wondering if you can help me, i have a text file that is pipe "|" delimited, the issue i have is that there are several variable types within this, for example

5009|0011730|00|0000|0000|0000|N|N|29/03/2007|N|      0|Y|      0|    0|      0.00 |06|655|A|  |A| | |

This has been taken from a COBOL system with fixed width and Zero Suppression and i am trying to insert this into a SQL database.

In COBOL you can define PIC's to the length of the field and it would push the data down through the values however given i have over 200,000 records and there are about 20 different types of data covering over 2000 fields, i do not want to have to type each variable manually.

Is there any sort of 'Auto' fill that can help me with this, for example:

VB

Public Structure DataType_1
    Sub New(ByVal DelimitedString)
        'Do Something Here
        'to push the values without
        'having to write up each variable'
        'and assign it a value

    End Sub
    Public Var1 As Integer
    Public Var2 As Integer
    Public Var3 As Integer
    Public Var4 As Integer
    Public Var5 As Integer
    Public Var6 As Integer
    Public Var7 As String
    Public Var8 As String
    Public Var9 As Date
    ' and so no per each delimted value

End Structure

From this i can then create the SQL from it to push it into a database table. I am not sure if assigning each variable is the right way either, maybe a collection?

Any thoughts would be helpful.

Posted 8-Sep-15 22:22pm

Dev O'Connor

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-09-09T10:15:00

I would define custom class like this:

VB

Public Class MyData
	Private Var1? As Integer '(Of Nullable)
	Private Var2? As Integer
	Private Var3? As Integer
	Private Var4? As Integer
	Private Var5? As Integer
	Private Var6? As Integer
	Private Var7 As String 
	Private Var8 As String 
	Private Var9? As Date 

	Public Sub New(ByVal PipedString As String)
		Dim data As String() = PipedString.Split("|")
		Var1 = data(0)
		Var2 = data(1)
		Var3 = data(2)
		Var4 = data(3)
		Var5 = data(4)
		Var6 = data(5)
		Var7 = data(6)
		Var8 = data(7)
		Var9 = Convert.ToDateTime(data(8), New System.Globalization.CultureInfo("de-DE"))
	End Sub
	
        Public Property Data1 As Integer
		Get 
			Return Var1
		End Get
		Set (value As Integer)
			Var1 = value
		End Set
	End Property
	
	'define other properties here



	Public Overrides Function ToString() As String
		Return "Date is: " & Var9.ToString()
	End Function

End Class

Usage:

VB

Dim s As String  = "5009|0011730|00|0000|0000|0000|N|N|29/03/2007|N|      0|Y|      0|    0|      0.00 |06|655|A|  |A| | |  "


Dim md As MyData = New MyData(s)
Console.WriteLine("{0}", md.ToString())

md.Data1 = 5010
Console.WriteLine("{0}", md.Data1)