none
Export access table into text file

    Question

  • I have written the following code to export an MS access table into a text file.

    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\test.mdb")
    Dim cmd As OleDbCommand
    Dim sSql As String = "SELECT * INTO [Text;Database=C:\test\Result;Hdr=No].[test.txt] FROM [tmpTable]"

    cmd = New OleDbCommand(sSql, conn)
    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()

    With the above code system exports the file, but I see double quotes at the begining and end of each record. So I changed the code to the following.

    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\test.mdb")
    Dim cmd As OleDbCommand
    Dim sSql As String = "SELECT * INTO [Text;Database=C:\test\Result;Hdr=No;FMT=Fixed].[test.txt] FROM [tmpTable]"

    cmd = New OleDbCommand(sSql, conn)
    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()

    This one creates the file without double quotes, but it inserts so many blank lines between each record. My table has about 1000 records, but the created table has 123777 records. Above code also created the following schema.ini file. I tried changing many settings, but couldn't get this to work. Could any one help please?

    schema.ini file contents

    [test.txt]
    ColNameHeader=False
    CharacterSet=1252
    Format=FixedLength
    FixedFormat=RaggedEdge
    Col1=F1 LongChar Attribute 32


    donsls
    • Moved by danishani Wednesday, January 18, 2012 7:11 PM VB.Net instead of VBA (From:Visual Basic for Applications (VBA))
    Tuesday, January 10, 2012 4:40 PM

All replies

  • Are you writing this in VB.NET?

     

    It looks like .NET to me, but please confirm, so I can move this thread to the VB.NET Forum.

     

    Thanks,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, January 16, 2012 6:28 AM
  • Hi Donsls,

    Welcome to the MSDN Forum.

    I have test your code, I cannot reproduce your scenario.

    In the second way, you exported the data with fix-width, so there is no quotes. For more information, you can take a look at this article:

    http://office.microsoft.com/en-us/access-help/export-data-to-a-text-file-HA010006905.aspx

    with the fixed length, every column occupies a fixed length space which is the size of that column. If the length of the text type columns is very big and the content is blank and you checked the "word wrap" of the notepad, you will see a lot of blank lines.

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Mike FengModerator Sunday, January 29, 2012 6:55 AM
    • Unmarked as answer by Donsls Wednesday, February 01, 2012 7:51 PM
    Thursday, January 19, 2012 12:23 PM
    Moderator
  • Any one know how to fix this in VB?
    donsls
    Wednesday, February 01, 2012 7:52 PM
  • Hello,

    A work-around would be to query the table into a DataTable then using the function below the LINQ statement builds an comma delimited representation of each row which gets written to a text file.

    Public Sub ExportDataToTextFile1(ByVal dt As DataTable, ByVal FileName As String)
        Dim Rows = _
        ( _
            From row In dt.Rows _
            Let fields = String.Join(",", _
                         DirectCast(row, DataRow).ItemArray.Select( _
                         Function(o) o.ToString).ToArray) Select fields _
        ).ToArray
    
        If Rows IsNot Nothing Then
            IO.File.WriteAllLines(FileName, Rows)
        End If
    End Sub
    

     


    KSG
    Wednesday, February 01, 2012 9:32 PM
  • Any one know how to fix this in VB?
    donsls

    Hi Donsls,

    How about several lines of data? Such as:

    Select top 10

    Did you check data? Is there some blank rows? 

    How about use Access export wizard?

    I look forward you.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, February 02, 2012 3:36 AM
    Moderator
  • Donsis,

    In addition to Mike and Kevin,

    Take a look at this page where the commands are described.

    http://msdn.microsoft.com/en-us/library/bb208934(v=office.12).aspx

    Otherwise the Microsoft access for developers forum gives maybe a better chance.

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/threads

     

     


    Success
    Cor
    Thursday, February 02, 2012 7:04 AM
  • The problem is some of my files got about 200000 rows and writing it to file using filestream just crashes the system. I tried it it stops around 150000 rows and hangs and doesn't release the file handler.
    donsls
    Thursday, February 02, 2012 7:28 PM
  • The problem is some of my files got about 200000 rows and writing it to file using filestream just crashes the system. I tried it it stops around 150000 rows and hangs and doesn't release the file handler.
    donsls

    Hi Donsls,

    So this is a different issue from your original one, I suggest you to start a new thread for this question. 

    Anyway, did you try to export your table with Access wizard? Is the same result? And I want to repeat my above questions:

    How about several lines of data? Such as:

    Select top 10

    Did you check data? Is there some blank rows? 

    I look forward you.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, February 03, 2012 7:52 AM
    Moderator
  • check this thread you will get an idea

    http://www.daniweb.com/software-development/vbnet/threads/67542heck this thread


    java
    Saturday, February 04, 2012 6:31 AM