Clever Geek Handbook

Exporting a Tab Delimited File from an Access Database

This code

db = "C:\Dokumente und Einstellungen\hom\Anwendungsdaten\BayWotch4\Neuer Ordner\baywotch.db5"
TextExportFile = "C:\Dokumente und Einstellungen\hom\Anwendungsdaten\BayWotch4\Neuer Ordner\Exp.txt"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source =" & db

strSQL = "SELECT * FROM tblAuction1"

rs.Open strSQL, cn, 3, 3

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.CreateTextFile(TextExportFile, True, True)

a = rs.GetString

f.WriteLine a

f.Close

creates a tab delimited file, however it is not suitable for import into mysql. I would like it to create a file similar to the file created by the access macro, which can be seen here:

http://www.yousendit.com/download/TTZtWmdsT01kMnVGa1E9PQ

The file generated by vbscript is located here:

http://www.yousendit.com/download/TTZtWmdsT00wVWtLSkE9PQ

I would also like to know why the file sizes differ by 50 or so.

edit: The output from the vbscript file uses newlines that are not recognized in notepad, so it looks substantially messy when viewed above. The macro doesn't seem to export HTML which explains why it is a smaller file, however vbscript doesn't look like a tab delimiter as it won't import into mysql.

edit: the files look ok on a linux system, so it might be due to Windows handling. However, this is still not the case.

0


source to share


3 answers


Both files contain what looks like tab-delimited data, as well as HTML (generated by some MS Office application, in appearance). Does tblAuction1 support any OLE objects? Perhaps when you export these objects, they export the contents of the file?



0


source


It seems to me that this is an encoding issue. I see that you are passing the Unicode parameter when you create the text file, but there is clearly a difference in encoding between the two files.



0


source


What is the purpose of this project? What is the purpose of creating the file? If you just want to move data from Access to MySQL why not do it directly with something like this


Const Access = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\Dokumente und Einstellungen\hom\Anwendungsdaten\BayWotch4\Neuer Ordner\baywotch.db5"
Const SQLServer = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=.\SQLEXPRESS"
Dim arrFields

Set SQLConn = CreateObject("ADODB.Connection")
WITH SQLConn
    .ConnectionString = SQLServer
    .Open
End WITH
Set AccessConn = CreateObject("ADODB.Connection")
WITH AccessConn
    .ConnectionString = Access
    .Open
End WITH
Set SQLRS = CreateObject("ADODB.Recordset")
WITH SQLRS
    .CursorType = 3
    .LockType = 3
End WITH
Set AccessRS = CreateObject("ADODB.Recordset")
WITH AccessRS
    .ActiveConnection = AccessConn
    .CursorType = 3
    .LockType = 3   
End WITH

strSQL = "SELECT * FROM tblAuction1" 
AccessRS.Open strSQL

If AccessRS.RecordCount <> 0 Then
    AccessRS.MoveFirst
    ReDim arrFields(AccessRS.Fields.Count)
    Do Until AccessRS.BOF OR AccessRS.EOF
        For i = 0 To AccessRS.Fields.Count - 1
            If AccessRS.Fields(i).Type = 202 Then 
                arrFields(i) = Chr(39) & AccessRS.Fields(i).Value & Chr(39)
            Else
                arrFields(i) = AccessRS.Fields(i).Value
            End If
        Next
        strSQL1 = "INSERT INTO {Table in mySQL} VALUES("
        For j = 1 To UBound(arrFields) - 2 
            strSQL1 = strSQL1 & arrFields(j) & ","
        Next
        strSQL1 = strSQL1 & arrFields(UBound(arrFields) - 1) & ")"
        SQLRS = SQLConn.Execute(strSQL1)
        AccessRS.MoveNext
    Loop
Else
    MsgBox "No records found"
End If

This will add all the records returned by the recordset to a table in the SQLExpress database, so it won't be difficult for you to customize your needs (if your needs are migrating from one database to another).

0


source






More articles:


All Articles