Click here to Skip to main content
15,314,119 members
Please Sign up or sign in to vote.
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:

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.

1 solution

I would define custom class like this:
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
			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

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)
Dev O'Connor 9-Sep-15 18:08pm
Hi Maciej,

Thanks for the comment, i was hoping to avoid this due to this record type has 337 columns, as well as 160 repeated 'OCCURS' PIC's which will be sent off to a child table when inserted.

Which means i would have to classify each column and then pass the value, was kinda hoping i could just declare them and fill down some how such as COBOL does.

THanks anyway, i have something similar to this already and it becoming unmanagable as this is just one record time i have a whole bunch more and many more columns to declare and fill.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900