read file and store to access

[SoftBa]

Hi, I have part of code to read file and show content inside list box. I wonder if someone could help me to store data to access instead list box. I'm writing now on my cellphone and don't have source code right now, but just to see is there anyone interested in this, I will paste code later or upload project.
Thanks

[djjeavons]

Hi

This can be done using ADO.NET and the OleDb objects coupled witih a series of INSERT statements, but it would be useful to know what the data is and how you plan to store it. That is, is the data to be stored in one table or many, and what is the definition of the table(s).

As a starter, you would add the following imports statement to your code file to work with the OleDb objects: Imports System.Data.OleDb.

Then you would create a connection to the database using an appropriate connection string which you can get from www.connectionstrings.com and execute a series of INSERT statements. The following is a brief air coded example:

Dim connectionString As String = "your connection string"
Dim connection As New OleDbConnection(connectionString)

'Create a command that will execute the INSERT statement
Dim command As New OleDbCommand = "INSERT INTO YourTableName (Field List seperated by commas) VALUES (@p1, @p2...)

'Open the connection to the database
connection.Open()

'Assume that you have some code here to read through your text file
Do While ...

    command.Parameters.Clear
    command.AddParameterWithValue("@p1", yourValueFromTheTextFile)
    'Further parameters if required
    command.ExecuteNonQuery()

Loop

'Close the connection to the database
connection.Displose()

HTH

[SoftBa]

Public Class Form1

  Function isDelimiter(ByVal b As Byte) As Boolean

    Dim result As Boolean
    result = False ' by default, it's NOT 

    ' all ASCII < 20
        If b < 20 Then
            result = True
        End If
    ' ASCII 32 , blankspace
        If b = 32 Then
            result = True
        End If
    isDelimiter = result

  End Function

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ListBox1.Items.Clear()
    If System.IO.File.Exists(ComboBox1.Text) Then

      Dim buf() As Byte
      buf = System.IO.File.ReadAllBytes(ComboBox1.Text)

      Dim i As Long
      Dim j As Long
      Dim str As String

      While (i < buf.Length)
        ' check from buf(i), use index j

        ' reset
        str = ""
        j = i
        While (j < buf.Length And Not isDelimiter(buf(j)))

          If Not isDelimiter(buf(j)) Then
            str = str & Chr(buf(j))
          End If

          ' move to next char
          j = j + 1
        End While

        ' str is result
                ' add to list
                ListBox1.Items.Add(str)
        ListBox1.SelectedIndex = ListBox1.Items.Count - 1

        ' move to next index
        i = j + 1

      End While

    Else
      MessageBox.Show("ERROR : cannot find file " & ComboBox1.Text)

    End If
  End Sub

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    ListBox1.Items.Clear()

  End Sub


End Class

That is code I'm curently using. Data are separated by binary zeroes '\0' .
I would like to store those data into access in a way that each \0 represents new field in a access table..
This is how data are look like edited by Notepad ++
http://s30.postimg.org/5cmw3t2ap/Untitled.png

[djjeavons]

Hi

Does the file in the screen shot represent one row of data that would be in the table of the database?

In your code you have a comment that states 'str is the result and you add that to the ListBox. Does str contain just one fields worth of data or one row?

Could you provide an example of the file and the definition of your database.

[SoftBa]

This is Print Screen how it should work one file per a line http://s30.postimg.org/nu3uvhwpt/Untitled.png

And here is sample of files I need to open http://speedy.sh/fYKuN/sad.7z

[djjeavons]

Hi

I downloaded your SAD files and ran your code and can see what you are trying to do, but I don't see how the data maps to your database. The data is difficult to read and your field names in the database provide no clue as to what data it may contain or the data type.

Are these files generated by yourself or a third party? Do you have some form of data dictionary that explains these files?

[SoftBa]

.SAD file comes from old application called ASYCUDA http://www.asycuda.org/ and application is really old. As you can see, it is hard to read data out of that application and that is why I need to brake them into pieces so that I can easily manipulate them later.
field names in database are not important in this step, I will change them later when I see how data are storen inside them (because each .sad file has its own lenght and I suppose that number of separators will follow some routine - logic - which I must figure out).

You can't see any data maps because I don't have any. Only this code which can take out parts of data separated by zeroes \0 .
Thanks for helping me. This is just a first step in project I have on my mind, but I think I could easily manipulate with data after they are stored inside access database ..

[djjeavons]

Hi

I took a look around the site and not sure if you found the following: UN Layout for Trade Documents which contains a file called rec01_UNLK_guidelines_pub_2002_ecetr270.pdf which looks like it might contains some form of data dictionary. However, from the other pieces I have read it would suggest that users of the system can use an SQL like syntax to extract data in the format that they wish.

In terms of what next, do you know how to write data to an Access database? If not, do you simply want the code to write the same data to the Access database as you are writing to the listbox currently in order to help you look for patterns?

[SoftBa]

On your last question, yes I would like to write data like they are shown in listbox, just in this case, instead of new line I thought to write each line to a new column.
This would help me a lot in a future work with these documents.

[djjeavons]

Having given this a bit more thought, I wonder whether Access is the right tool for this. It would probably be better to write the data out to a CSV file and examine it within Excel. You could also then import the data into an Access database direct from the CSV file if you really want it in Access.

I had a little play around with a couple of the files and if I ignore (or not) all whitespace then you have variable numbers of columns which would make writing it direct to Access more difficult.

The following is your code with the additional write to a CSV file. Please replace filename with your combobox value.

        Dim fileName As String = Application.StartupPath & "\12U53M.SAD"

        Dim sb As New System.Text.StringBuilder

        ListBox1.Items.Clear()

        If System.IO.File.Exists(fileName) Then
            Dim buf() As Byte
            buf = System.IO.File.ReadAllBytes(fileName)
            Dim i As Long
            Dim j As Long
            Dim str As String
            While (i < buf.Length)
                ' check from buf(i), use index j
                ' reset
                str = ""
                j = i
                While (j < buf.Length And Not isDelimiter(buf(j)))
                    'If Not isDelimiter(buf(j)) Then
                    str = str & Chr(buf(j))
                    'End If
                    ' move to next char
                    j = j + 1
                End While
                ' str is result
                ' add to list
                ListBox1.Items.Add(str)
                ListBox1.SelectedIndex = ListBox1.Items.Count - 1

                'Append currentline to stringbuilder if it has a value, using comma delimited
                If str.Trim.Length > 0 Then sb.Append(str & ",")

                ' move to next index
                i = j + 1
            End While
        Else
            MessageBox.Show("ERROR : cannot find file " & fileName)
        End If

        'Write out the contents of the string builder to a new CSV file
        Dim sw As New System.IO.StreamWriter("c:\temp\test.csv")
        sw.Write(sb.ToString.Substring(0, sb.ToString.Length - 1))
        sw.Close()

HTH

[SoftBa]

Thanks for your help. Today I have tried your code, and after few adjustments I have it read data and store them inside .txt with | as a delimiter. I think that | is better in this case as that document allready contains ',' inside.
Now I'm trying to figure out logic of how data are stored. For example, if there are 1 product, file have description for that, and if there are for example 4 product, they all have description for themselfs.

Thanks one mor time, I'll try to see how data are stored (logic) and if I figure out, then my next step is to read data I want.

[djjeavons]

Your welcome, good luck with your investigations.