Import Export Text File with char conversion

[Lethugs]

Hi,

Im making a simple program which converts a encrypted text file the upload it to sql CE.
This is time in and out logs of every employee. The date is extracted from a machine and downloaded as encrypted file.
We managed to decrypt it by getting the equivalent value of each character.

I need to decrypt this file then save it to database (sql server 2008 r2)

Downloaded file looks like this, in plain text:

±²®ªÇª®°­°®­°®¯±ª®·¸²®¸±²B
´µ³ªÇª®°­°®­°®¯±ª®·¸²±¸³®N
´µ³ªÍª®°­°¯­°®¯±ª¯·¸®°¸²²T
±²®ªÍª®°­°¯­°®¯±ª°®¸®®¸°®9
±¯´ªÍª®°­°¯­°®¯±ª°®¸®®¸±²A
´µ³ªÇª®°­°°­°®¯±ª®·¸±¶¸±´X
±¯´ªÇª®°­°°­°®¯±ª¯®¸²¯¸²´C
±²®ªÇª®°­°°­°®¯±ª¯®¸²±¸°µA
´µ³ªÍª®°­°°­°®¯±ª¯·¸±¶¸±²]
´µ³ªÇª®°­°³­°®¯±ª®·¸³¯¸²µX

My code for now in uploading: (I used openfiledialog object to open the file)

cmd = New SqlCeCommand("Create Table TempDb (Rec Nvarchar (100)) ", Con)
        cmd.ExecuteNonQuery()

        Dim cmd2 As SqlCeCommand

        Using MyReader As New StreamReader(ofdBrowse.FileName)
            Dim fields As String()
            Dim line As String

            While Not MyReader.EndOfStream
                line = MyReader.ReadLine
                'Dim line As String = My.Computer.FileSystem.ReadAllText(ofdBrowse.FileName)
                fields = line.Split(vbNewLine)

                For Each field As String In fields

                    Select Case field

                        Case "®"
                            ch = "0"
                        Case "¯"
                            ch = "1"
                        Case "°"
                            ch = "2"
                        Case "±"
                            ch = "3"
                        Case "²"
                            ch = "4"
                        Case "³"
                            ch = "5"
                        Case "´"
                            ch = "6"
                        Case "µ"
                            ch = "7"
                        Case "¶"
                            ch = "8"
                        Case "·"
                            ch = "9"
                        Case "Ç"
                            ch = "I"
                        Case "Í"
                            ch = "O"
                        Case "«"
                            ch = "-"
                        Case "ª"
                            ch = ","
                        Case "­"
                            ch = "/"
                        Case "¸"
                            ch = ":"
                    End Select
                    rec = rec & ch

                Next

                cmd2 = New SqlCeCommand("Insert Into TempDb (Rec) Values (@r)", Con)
                cmd2.Parameters.AddWithValue("@r", rec)
                cmd2.ExecuteNonQuery()


            End While

        End Using


        adap = New SqlCeDataAdapter("Select * From TempDb", Con)
        Dim dt As New DataTable
        adap.Fill(dt)

        If dt.Rows.Count > 0 Then
            dgTime.DataSource = dt
        End If

        cmd = New SqlCeCommand("Drop Table TempDb", Con)
        cmd.ExecuteNonQuery()

There is no error but the uploaded data is empty. Its not null in database, just nothing (empty string)

any other approach is grately appreciated

After uploading the decrypted file, how to download or export it back to text file? ready for uploading to different program

Thanks

[tinstaafl]

A couple of things:

Your string is returning a comma-delimited line of values. You may need to split it by ","c and insert the appropriate fields into your table.

Using a dictionary will simplify your code tremendously:

ReadOnly DecryptChars As Dictionary(Of Char, Char) = New Dictionary(Of Char, Char) From
                                                {
                                                    {"®"c, "0"c},
                                                    {"¯"c, "1"c},
                                                    {"°"c, "2"c},
                                                    {"±"c, "3"c},
                                                    {"²"c, "4"c},
                                                    {"³"c, "5"c},
                                                    {"´"c, "6"c},
                                                    {"µ"c, "7"c},
                                                    {"¶"c, "8"c},
                                                    {"·"c, "9"c},
                                                    {"Ç"c, "I"c},
                                                    {"Í"c, "O"c},
                                                    {"«"c, "-"c},
                                                    {"ª"c, ","c},
                                                    {"­"c, "/"c},
                                                    {"¸"c, ":"c}
                                                }



     Dim rec As String = ""
    Using MyReader As New StreamReader(ofdBrowse.FileName)
        Dim line As String
        While Not MyReader.EndOfStream
            line = MyReader.ReadLine
            rec = ""
            'Dim line As String = My.Computer.FileSystem.ReadAllText(ofdBrowse.FileName)
            'fields = line.Split(vbNewLine)
            For Each c As Char In line
                If DecryptChars.ContainsKey(c) Then
                    rec = rec & DecryptChars(c)
                End If
            Next
            cmd2 = New SqlCeCommand("Insert Into TempDb (Rec) Values (@r)", Con)
            cmd2.Parameters.AddWithValue("@r", rec)
            cmd2.ExecuteNonQuery()
        End While
    End Using                                                   

[Lethugs]

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

[Lethugs]

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

[Lethugs]

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

[Lethugs]

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

[Lethugs]

Thank you sir,

It worked, I just dont understand that comma-delimited values
Just edited this, without System.Text.Encoding.Default it displays empty string

Using MyReader As New StreamReader(ofdBrowse.FileName,System.Text.Encoding.Default)

Can you also show easy way of exporting it text file also?

thanks again

[Lethugs]

After acquiring the desired result, I was asked to save it in database in splitted manner

01-0002,I,4/21/2014,07:34:00

There are 4 cols in my table, how can i loop through each record in a row?

result should be (in a row)

col1 - 01-0002
col2 - I
col3 - 4/21/2014
clo4 - 07:34:00

I tried this

`Dim ch As String

    For i = 0 To dt.Rows.Count - 1
        ch = Nothing

        If Not IsNothing(dt.Rows.Item("Rec") Then

            For Each s As Char In dt.Rows.Item("Rec"

                If s <> "," Then
                    ch = ch & s
                End If

                MsgBox(ch)
            Next


        End If

    Next

`
how can I assign each record in every col?

thanks again for the patience

[tinstaafl]

Assuming dt is a DataTable something like this should work:

Using MyReader As New StreamReader(ofdBrowse.FileName)
    Dim line As String
    While Not MyReader.EndOfStream
        line = MyReader.ReadLine
        rec = ""
        For Each c As Char In line
            If DecryptChars.ContainsKey(c) Then
                rec = rec & DecryptChars(c)
            End If
        Next
        Dim Fields As String() = rec.Split(","c)
        Dim NewRow as DataRow = dt.NewRow
        For I = 0 to Fields.Length - 1
            NewRow.SetField(I,Fields(I))
        Next
        dt.Rows.Add(NewRow)
    End While
End Using

This assumes that the data is strongly consistent. If there is any chance that the data will be inconsistent you will need to check that the data you want to record is there.

To save the data to a new file that you can read again later the DataTable class contains methods for Reading and Writing from and to .xml files.

[Lethugs]

Problem solved. I used split method to chop the data into required parts.
Thanks tinstaafl for giving time helping me