Mailinglisten-Archive |
This is a multi-part message in MIME format.
------=_NextPart_000_0006_01C0DA22.05721640
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
Hallo Ahmed,
Anbei uebersende ich Dir ein Script.
Die Anleitung zur Nutzung findest du im Script selbst.
Das Script bewerkstelligt das konvertieren von MS-Access 2000 Datenbanken
nach MySQL.
Es erstellt einen Dump,
den du dann am besten mit MyPHPAdmin einliest.
Ich hoffe das hilft Dir weiter. Ob es das Datums Problem loest kann ich Dir
leider so auf die Schnelle nicht sagen.
Beste Gruesse
Denis
--
Unix is an operating system, OS/2 is half an operating system, Windows
is a shell, and DOS is a boot partition virus.
-- Peter H. Coffin
> Ich möchte Daten von eine Tabelle (Bestellung von Nordwind-DB-Access)
> importieren, das Problem die Access-Daten werden im dd-mm-yyyy
> Format gespeichert,
> und bei MySQL-Daten, werden die im yyyy-mm-dd Format gespeichert.
> damit nicht zu Probleme führt, wie kann ich das Problem lösen ? mindesten
> von Access Seite.
------=_NextPart_000_0006_01C0DA22.05721640
Content-Type: text/plain;
name="convert_access2k_mysql.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="convert_access2k_mysql.txt"
'Exports the database contents into a file in mysql format
'IS NOT SELECTIVE! (exports ALL tables)
=20
'INSTRUCTIONS
=20
'Paste this function into a Microsoft Access module of a database =
that has the
'tables you want to export. Create a macro with the function RunCode =
and the
'argument export_mysql (). Run the macro to start the export.
=20
'Access 2000
'------------
'Under Tools, References ensure you have the following selected:
'Visual Basic for Applications
'Microsoft Access 9.0 Object Library
'Microsoft DAO 3.6 Object Library
'OLE Automation
'Microsoft Visual Basic for Applications Extensibility 5.3
=20
'*** Unselect ActiveX Data Objects references!!!
=20
=20
Dim dbase As Database, tdef As Recordset, i As Integer, fd As =
Integer, tname As String, j As Integer, iname As String
Dim s As String, found As Integer, stuff As String, idx As Index, k =
As Integer, f As Integer, fld As Field, istuff As String
Set dbase =3D CurrentDb()
=20
'Open the file to export the defintions and data to. Change this to =
suit your needs ****
=20
Open "c:\temp\dump.txt" For Output As #1
Print #1, "# Converted from MS Access to mysql "
Print #1, "# by Brian Andrews, (c) InforMate (www.informate.co.nz), =
brian_(at)_informate.co.nz, 1997-2000"
Print #1, ""
'Go through the table definitions
For i =3D 0 To dbase.TableDefs.Count - 1
=20
=20
' Let's take only the visible tables
=20
If ((dbase.TableDefs(i).Attributes And DB_SYSTEMOBJECT) Or =
(dbase.TableDefs(i).Attributes And DB_HIDDENOBJECT)) Then
=20
Else
=20
' We DROP the table if it already exists
' and then create it again
=20
tname =3D "" & dbase.TableDefs(i).Name
=20
'remove spaces from tablename
=20
For j =3D 1 To Len(tname)
If j < Len(tname) Then
If Mid$(tname, j, 1) =3D " " Then
s =3D Left$(tname, j - 1)
s =3D s & "" & Right$(tname, Len(tname) - j)
j =3D j + 1
found =3D True
tname =3D s
End If
End If
Next j
=20
'restrict tablename to 19 chars
=20
tname =3D Left$(tname, 19)
=20
=20
'comment out these lines if the table doesn't exist or else =
create it first
=20
Print #1, ""
Print #1, ""
Print #1, "DROP TABLE IF EXISTS " & tname & ";"
=20
=20
Print #1,
Print #1, "CREATE TABLE " & tname & " ("
=20
=20
' Step through all the fields in the table
=20
For fd =3D 0 To dbase.TableDefs(i).Fields.Count - 1
=20
=20
Dim tyyppi As String, pituus As Integer, comma As String
Select Case dbase.TableDefs(i).Fields(fd).Type
Case DB_BOOLEAN
tyyppi =3D "SMALLINT"
Case DB_INTEGER
tyyppi =3D "SMALLINT"
Case DB_BYTE
tyyppi =3D "TINYBLOB"
Case DB_LONG
If dbase.TableDefs(i).Fields(fd).Attributes =3D =
17 Then
tyyppi =3D "INT AUTO_INCREMENT"
Else
tyyppi =3D "INT"
End If
Case DB_DOUBLE
tyyppi =3D "DOUBLE"
Case DB_SINGLE '
tyyppi =3D "REAL"
Case DB_CURRENCY
tyyppi =3D "DOUBLE (8,4)"
Case DB_TEXT
pituus =3D dbase.TableDefs(i).Fields(fd).Size
tyyppi =3D "CHAR (" & pituus & ")"
Case dbAutoIncrField
' tyyppi =3D "INT NOT NULL AUTO_INCREMENT"
=20
'Access Date fields are set as the mysql date type - =
you can change this to
'DATETIME if you prefer.
=20
Case DB_DATE
tyyppi =3D "DATE"
Case DB_MEMO, DB_LONGBINARY
tyyppi =3D "BLOB"
=20
End Select
=20
=20
'Print the field definition
=20
'remove spaces from fieldname
=20
stuff =3D "" & dbase.TableDefs(i).Fields(fd).Name
=20
'we had a table called Index which mysql doesn't like
=20
If stuff =3D "Index" Then stuff =3D "Indexm"
=20
For j =3D 1 To Len(stuff)
If j < Len(stuff) Then
If Mid$(stuff, j, 1) =3D " " Then
s =3D Left$(stuff, j - 1)
s =3D s & "" & Right$(stuff, Len(stuff) - j)
j =3D j + 1
found =3D True
stuff =3D s
End If
End If
Next j
=20
stuff =3D Left$(stuff, 19)
=20
'not null
k =3D 0
=20
For Each idx In dbase.TableDefs(i).Indexes
If (dbase.TableDefs(i).Indexes(k).Fields(0).Name =3D =
dbase.TableDefs(i).Fields(fd).Name) Then
tyyppi =3D tyyppi & " NOT NULL"
Exit For
End If
k =3D k + 1
Next
=20
'default value
=20
If (Not =
(IsNull(dbase.TableDefs(i).Fields(fd).DefaultValue)) And =
dbase.TableDefs(i).Fields(fd).DefaultValue <> "") Then
=20
If dbase.TableDefs(i).Fields(fd).Required =3D False =
Then
tyyppi =3D tyyppi & " NOT NULL "
End If
=20
If Left$(dbase.TableDefs(i).Fields(fd).DefaultValue, =
1) =3D Chr(34) Then
tyyppi =3D tyyppi & " DEFAULT '" & =
Mid$(dbase.TableDefs(i).Fields(fd).DefaultValue, 2, =
Len(dbase.TableDefs(i).Fields(fd).DefaultValue) - 2) & "'"
Else
tyyppi =3D tyyppi & " DEFAULT " & =
dbase.TableDefs(i).Fields(fd).DefaultValue
End If
=20
End If
=20
'print out field info
comma =3D ","
=20
If fd =3D dbase.TableDefs(i).Fields.Count - 1 Then
=20
If dbase.TableDefs(i).Indexes.Count =3D 0 Then
comma =3D ""
Else
comma =3D ","
End If
End If
=20
Print #1, " " & stuff & " " & tyyppi & comma
=20
Next fd
=20
=20
'primary key and other index declaration
=20
k =3D 0
=20
=20
For Each idx In dbase.TableDefs(i).Indexes
=20
'Check Primary property
=20
k =3D k + 1
=20
If idx.Primary Then
istuff =3D " PRIMARY KEY ("
Else
istuff =3D " KEY ("
End If
=20
f =3D 0
=20
For Each fld In idx.Fields
f =3D f + 1
iname =3D fld.Name
=20
For j =3D 1 To Len(iname)
If j < Len(iname) Then
If Mid$(iname, j, 1) =3D " " Then
s =3D Left$(iname, j - 1)
s =3D s & "" & Right$(iname, Len(iname) - j)
j =3D j + 1
found =3D True
iname =3D s
End If
End If
Next j
=20
istuff =3D istuff & iname
=20
If f < idx.Fields.Count Then
istuff =3D istuff & ","
End If
=20
Next fld
=20
If k < dbase.TableDefs(i).Indexes.Count Then
=20
Print #1, istuff & "),"
Else
Print #1, istuff & ")"
=20
End If
=20
=20
Next idx
=20
=20
=20
Print #1, ");"
Print #1, ""
=20
=20
Dim recset As Recordset
Dim row As String, it As String
Dim is_string As String, reccount As Integer, x As Integer
=20
Set recset =3D dbase.OpenRecordset(dbase.TableDefs(i).Name)
=20
reccount =3D recset.RecordCount
=20
=20
If reccount <> 0 Then
=20
' Step through the rows in the table
=20
recset.MoveFirst
Do Until recset.EOF
=20
row =3D "INSERT INTO " & tname & " VALUES ("
=20
' Go through the fields in the row
=20
For fd =3D 0 To recset.Fields.Count - 1
=20
is_string =3D ""
stuff =3D "" & recset.Fields(fd).Value
=20
Select Case recset.Fields(fd).Type
Case DB_BOOLEAN
=20
'true fields are set to 1, false are set to 0
=20
If recset.Fields(fd).Value =3D True Then
stuff =3D "0"
Else
stuff =3D "1"
End If
=20
Case DB_TEXT, DB_MEMO, 15, DB_LONGBINARY
is_string =3D "'"
Case DB_DATE
is_string =3D "'"
=20
'format date fields to YYYY-MM-DD. You may want =
to add time formatting as
'well if you have declared DATE fields as =
DATETIME
=20
If stuff <> "" And Not (IsNull(stuff)) Then
stuff =3D Format(stuff, "YYYY-MM-DD")
End If
Case Else
=20
'default empty number fields to 0 - comment this =
out if you want
=20
If stuff =3D "" Then
stuff =3D "0"
End If
End Select
=
=20
'**** escape single quotes
x =3D InStr(stuff, "'")
=20
While x <> 0
s =3D Left$(stuff, x - 1)
s =3D s & "\" & Right$(stuff, Len(stuff) - x + =
1)
stuff =3D s
x =3D InStr(x + 2, stuff, "'")
Wend
=20
'**** convert returns to <br>'s
=20
x =3D InStr(stuff, Chr(13))
=20
While x <> 0
s =3D Left$(stuff, x - 1)
If x =3D Len(stuff) Then
s =3D s & "<br>"
Else
s =3D s & "<br>" & Right$(stuff, Len(stuff) =
- x - 1)
End If
=20
stuff =3D s
x =3D InStr(x + 2, stuff, Chr(13))
Wend
=20
row =3D row & is_string & stuff & is_string
=20
If fd < recset.Fields.Count - 1 Then
row =3D row & ","
End If
Next fd
=20
' Add trailers and print
=20
row =3D row & ");"
Print #1, row
=20
' Move to the next row
=20
recset.MoveNext
Loop
=20
recset.Close
Set recset =3D Nothing
=20
End If
End If
Next i
=20
=20
=20
Close #1
=20
dbase.Close
Set dbase =3D Nothing
End Function
------=_NextPart_000_0006_01C0DA22.05721640--
---
*** Weitere Infos zur Mailingliste und MySQL unter http://www.4t2.com/mysql
php::bar PHP Wiki - Listenarchive