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.
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:
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()
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 ++
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.
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?
.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 ..
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?
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.
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()
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.
Your welcome, good luck with your investigations.