none
Convert delimited text file to .mdf

    Question

  • How do you convert a text delimited like this into an .mdf(Microsoft SQL)?

    Textfile.dlm

    "010000001001022205001Y 5311 #0001 Y N","2802739","38","F","025","016","021"
    "010000002001022205001Y 5311 #0001 Y N","2804343","18","M","024","021","021"
    "010000003001022205001Y 5311 #0001 Y N","2805003","29","M","023","010","018"
    "010000004001022205001Y 5311 #0001 Y N","2804538","28","F","005","014","011"
    "010000005001022205001Y 5311 #0001 Y N","2804277","07","F","011","013","009"
    "010000006001022205001Y 5311 #0001 Y N","2804999","07","M","015","011","010"
    "010000007001022205001Y 5311 #0001 Y N","2805006","15","M","020","011","018"
    "010000008001022205001Y 5311 #0001 Y N","2804527","18","M","030","009","014"
    "010000009001022205001Y 5311 #0001 Y N","2804346","05","F","020","012","010"
    "010000010001022205001Y 5311 #0001 Y N","2801179","38","F","012","009","016"
    "010000011001022205001Y 5311 #0001 Y N","2801178","05","F","011","011","008"
    "010000012001022205001Y 5311 #0001 Y N","2804739","01","F","019","009","017"
    "010000013001022205001Y 5311 #0001 Y N","2804525","36","F","014","005","011"
    "010000014001022205001Y 5311 #0001 Y N","2804347","12","M","039","017","027"
    "010000015001022205001Y 5311 #0001 Y N","2804344","38","M","039","021","026"
    Thursday, March 15, 2007 6:44 AM

Answers

  • Use the textfieldparser class to read in each line from the fixed format file
    http://msdn2.microsoft.com/en-us/library/zezabash(VS.80).aspx

    and then write these to an mdf file using and ado.net insert query using the ado.net command object

    (There are numerous examples of ADO.NET to call SQL)  In this case its a simple insert query to insert data into a table.   The following is an overview of ADO.Net use examples.      Look at execute a command.

    http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/ADOPlusOverview.aspx

     

    Thursday, March 15, 2007 9:53 AM

All replies

  • Use the textfieldparser class to read in each line from the fixed format file
    http://msdn2.microsoft.com/en-us/library/zezabash(VS.80).aspx

    and then write these to an mdf file using and ado.net insert query using the ado.net command object

    (There are numerous examples of ADO.NET to call SQL)  In this case its a simple insert query to insert data into a table.   The following is an overview of ADO.Net use examples.      Look at execute a command.

    http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/ADOPlusOverview.aspx

     

    Thursday, March 15, 2007 9:53 AM
  • Got this code but I don't know how to place each current field separated by comma to each my variables (sSerialNo, sAppNo, sCourse,etc.). My plan is after i place them in my variables gona save them in .mdf
      
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("E:\VB6 Codes\CEE Utilities\Text\102106.dlm")
                MyReader.TextFieldType = FileIO.FieldType.Delimited
                MyReader.SetDelimiters(",")
                Dim currentRow As String()

                'My Variables------
                Dim sSerialNo As String
                Dim sAppNo As String
                Dim sCourse As String
                Dim sGender As String
                Dim shortNumRS As Short
                Dim shortRCRS As Short
                Dim shortITRS As Short
                ---------------------
                While Not MyReader.EndOfData
                    Try
                        currentRow = MyReader.ReadFields()
                        Dim currentField As String
                        For Each currentField In currentRow
                          'I want to place each field in my variables
                          'Save the content of the variables in a database
                        Next
                    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                        MsgBox("Line " & ex.Message & _
                        "is not valid and will be skipped.")
                    End Try
                End While
            End Using
        End Sub
    Thursday, March 15, 2007 11:40 PM
  • thank you. I was able to solve it.
    Saturday, March 17, 2007 7:29 AM