How to save/load a text file to/from SQL database table.column

I use the following approach: (pseudocode)

1.  Create a class for the record with properties equal to field names wanted
1.1  Open the text file,get value to string
2.  Parse the string based on the delimeter(s) to array
3.  Loop through each line of the text file
4.  For each loop through, instantiate your class, and set properties based on field values (that are stored in array)
5.  Add each object to a modular level collection

::This may seem like an extra step, but it allows for advanced functionality

6.  Build your datatable based on properties of collection of objects
6.1  Make sure to name each column with the exact name of the database fields
7.  Use dataadapter, to update to database, use general query for the cmd object like "select * from your table", use command builder

::This will work, I use it every day

::Backwards to save it as a text file again.

rodmjay, actually I'm writing a history report log function which allow user to save/retrieve history report file into/from database.

And I would use the following table schema:

- create date (datetime),
- report ID (char(10)),
- report content (ntext)

So I think you comment is not appropriate !!!

Thanks  

This is an easy way (much less code)

To store valuse FROM Database TO Text File

Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PATH_TO_YOUR_MDB;Persist Security Info=False" '----> Give MDB path here, OR Your SQL Server Connection String
        Dim cn As New OleDbConnection(ConStr)
        cn.Open()
'----> Modify path and File Name in this line
        Dim sqlString As String = "SELECT * INTO [Text;DATABASE=c:\MY_FOLDER].[MyFile.csv] FROM [TABLE_NAME]"
        Dim Cmd As New OleDbCommand(sqlString, cn)
        Try
            MsgBox(Cmd.ExecuteNonQuery)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        cn.Close()
        cn.Dispose()


To Read a Text File and store in DataBase

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TxtFilesFolder\;Extended Properties='text;HDR=Yes;FMT=Delimited'"  '---> you need to change the folder name here

Then Select Query like this
"SELECT * FROM data.txt"

Once you have the Data in DataTable you can insert it into the database.

arif_eqbal,

I need to handle the unformated text file (none csv) !!!!

Beware! B and C grader!

I solved it already, so administrator please help to close this question. Thanks !

Solution :

Text file to database:
            Dim tFile As New FileStream(FileName, FileMode.Open, FileAccess.Read)
            Dim tData(tFile.Length()) As Byte
            tFile.Read(tData, 0, tData.Length - 1)
            tFile.Close()

            Dim daReportLog As New SqlClient.SqlDataAdapter("select * from ...." ,cn)
            Dim cbReportLog As New SqlClient.SqlCommandBuilder(daReportLog)
            daReportLog.InsertCommand = cbReportLog.GetInsertCommand

            Dim dt As New DataTable
            daReportLog.Fill(dt)
            Dim dr As DataRow = dt.NewRow
            dr.Item("report_id") = inReportID
            dr.Item("print_time") = Format(Now, "dd/MMM/yyyy hh:mm:ss")
            dr.Item("report_context") = ZipData
            dt.Rows.Add(dr)
            daReportLog.Update(dt)

Database to text file:
            Dim da As New SqlDataAdapter("select * from ...",cn)
            Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
            Dim dt As New DataTable

            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                Dim tData() As Byte
                tData = dt.Rows(0).Item("report_context")

                Dim tFileName As String = tmpFilePath + "\" + Format(Now, "ddhhmmss") & ".TXT"
                Dim fs As New FileStream(tFileName, FileMode.OpenOrCreate, FileAccess.Write)
                fs.Write(tData, 0, tData.Length - 1)
                fs.Close()
                fs = Nothing
                ZipData = Nothing
            Else
                MsgBox("Record not found !")
            End If

Oooh, please replace all "ZipData" with "tData" if you want to use it !!!

ee_ai_construct,
No objection here concerning the question(but then I was not involved in the question)

I do wonder if anybody at EE has looked at vbhaha's apalling grading record. (5 Cs and 5 Bs 0 As in the last 10 questions)
If EE experts never deserve an A, then how come he keeps asking us?

Dabas