requisiteSystem
asked on
Exporting with TransferText and schema.ini
I am sure this problem has been raised before. I am trying to do an export of a table using transferText. I need it to be tab delimited so I am creating a schema.ini just before the transfertext call.
DoCmd.TransferText acExportFixed, , "tempSubTestScoresExp", fileName, True, "", 437
I get an error when I leave out a spec argument and also when I put the path and name of schema.ini....what gives? I read that this is a known problem, but can't understand any of the workarounds given. Can someone help me out???
DoCmd.TransferText acExportFixed, , "tempSubTestScoresExp", fileName, True, "", 437
I get an error when I leave out a spec argument and also when I put the path and name of schema.ini....what gives? I read that this is a known problem, but can't understand any of the workarounds given. Can someone help me out???
I use the specification.
Do a "dummy" import of your data using the wizard. Then on the last screen before you hit "Finish" go Advanced, then "Save" or Save As r something.
GIve it a name (Ill use mySpec for this example) then you can use
DoCmd.TransferText acExportFixed, "mySpec", "tempSubTestScoresExp", fileName, True
Dave
Do a "dummy" import of your data using the wizard. Then on the last screen before you hit "Finish" go Advanced, then "Save" or Save As r something.
GIve it a name (Ill use mySpec for this example) then you can use
DoCmd.TransferText acExportFixed, "mySpec", "tempSubTestScoresExp", fileName, True
Dave
What's this 437 all about???? (maybe A97 is different???)
Dave
Dave
ASKER
I'm not interested in using specs because the export will have different field names each time its run. This is why I am creating the schema.ini on the fly from the table and then exporting it. Sorry I meant tab-delimited. Really looking for a solution that involves the schema.ini. Thanks.
ASKER
or is it possible to use vba to write directly to the system tables that have the spec info, in order to create a spec on the fly???
ASKER
I am using the function that is available on msdn to create a schema.ini.
here it is:
Private Function CreateSchemaFile(bIncFldNa mes As Boolean, _
sPath As String, _
sSectionName As String, _
sTblQryName As String) As Boolean
Dim Msg As String ' For error handling.
On Local Error GoTo CreateSchemaFile_Err
Dim ws As Workspace, db As Database
Dim tblDef As TableDef, fldDef As field
Dim i As Integer, Handle As Integer
Dim fldName As String, fldDataInfo As String
' -------------------------- ---------- ---------- -
' Set DAO objects.
' -------------------------- ---------- ---------- -
Set db = CurrentDb()
' -------------------------- ---------- ---------- -
' Open schema file for append.
' -------------------------- ---------- ---------- -
Handle = FreeFile
Open sPath & "schema.ini" For Output Access Write As #Handle
' -------------------------- ---------- ---------- -
' Write schema header.
' -------------------------- ---------- ---------- -
Print #Handle, "[" & sSectionName & "]"
Print #Handle, "ColNameHeader = " & _
IIf(bIncFldNames, "True", "False")
Print #Handle, "CharacterSet = ANSI"
Print #Handle, "Format = TabDelimited"
' -------------------------- ---------- ---------- -
' Get data concerning schema file.
' -------------------------- ---------- ---------- -
Set tblDef = db.TableDefs(sTblQryName)
With tblDef
For i = 0 To .Fields.Count - 1
Set fldDef = .Fields(i)
With fldDef
fldName = .Name
Select Case .Type
Case dbBoolean
fldDataInfo = "Bit"
Case dbByte
fldDataInfo = "Byte"
Case dbInteger
fldDataInfo = "Short"
Case dbLong
fldDataInfo = "Integer"
Case dbCurrency
fldDataInfo = "Currency"
Case dbSingle
fldDataInfo = "Single"
Case dbDouble
fldDataInfo = "Double"
Case dbDate
fldDataInfo = "Date"
Case dbText
fldDataInfo = "Char Width " & Format$(.Size)
Case dbLongBinary
fldDataInfo = "OLE"
Case dbMemo
fldDataInfo = "LongChar"
Case dbGUID
fldDataInfo = "Char Width 16"
End Select
Print #Handle, "Col" & Format$(i + 1) _
& "=" & fldName & Space$(1) _
& fldDataInfo
End With
Next i
End With
MsgBox sPath & "SCHEMA.INI has been created."
CreateSchemaFile = True
CreateSchemaFile_End:
Close Handle
Exit Function
CreateSchemaFile_Err:
Msg = "Error #: " & Format$(Err.Number) & vbCrLf
Msg = Msg & Err.Description
MsgBox Msg
Resume CreateSchemaFile_End
End Function
---------------------
this is my code:
If CreateSchemaFile(True, Mid(fileName, 1, InStrRev(fileName, "\")), Mid(fileName, InStrRev(fileName, "\") + 1), "tempSubTestScoresExp") Then
DoCmd.TransferText acExportDelim, , "tempSubTestScoresExp", fileName, True, ""
CurrentDb.TableDefs.Delete "tempSubTestScoresExp"
MsgBox ("File export complete")
End If
fileName is a global string variable with the path to the export file.
The code works, but when I look at my export file the first line(field names) is tab delimited with quotes around the field names, which is what I want, except for the quotes. however, all the data on subsequent lines is embedded in quotes and comma delimited. I don't know what is going on....I thought using the schema.ini would have given me flexibility to have a tab delimited export file without quotes. Can anyone shed light on this for me. I am increasing the points!
here it is:
Private Function CreateSchemaFile(bIncFldNa
sPath As String, _
sSectionName As String, _
sTblQryName As String) As Boolean
Dim Msg As String ' For error handling.
On Local Error GoTo CreateSchemaFile_Err
Dim ws As Workspace, db As Database
Dim tblDef As TableDef, fldDef As field
Dim i As Integer, Handle As Integer
Dim fldName As String, fldDataInfo As String
' --------------------------
' Set DAO objects.
' --------------------------
Set db = CurrentDb()
' --------------------------
' Open schema file for append.
' --------------------------
Handle = FreeFile
Open sPath & "schema.ini" For Output Access Write As #Handle
' --------------------------
' Write schema header.
' --------------------------
Print #Handle, "[" & sSectionName & "]"
Print #Handle, "ColNameHeader = " & _
IIf(bIncFldNames, "True", "False")
Print #Handle, "CharacterSet = ANSI"
Print #Handle, "Format = TabDelimited"
' --------------------------
' Get data concerning schema file.
' --------------------------
Set tblDef = db.TableDefs(sTblQryName)
With tblDef
For i = 0 To .Fields.Count - 1
Set fldDef = .Fields(i)
With fldDef
fldName = .Name
Select Case .Type
Case dbBoolean
fldDataInfo = "Bit"
Case dbByte
fldDataInfo = "Byte"
Case dbInteger
fldDataInfo = "Short"
Case dbLong
fldDataInfo = "Integer"
Case dbCurrency
fldDataInfo = "Currency"
Case dbSingle
fldDataInfo = "Single"
Case dbDouble
fldDataInfo = "Double"
Case dbDate
fldDataInfo = "Date"
Case dbText
fldDataInfo = "Char Width " & Format$(.Size)
Case dbLongBinary
fldDataInfo = "OLE"
Case dbMemo
fldDataInfo = "LongChar"
Case dbGUID
fldDataInfo = "Char Width 16"
End Select
Print #Handle, "Col" & Format$(i + 1) _
& "=" & fldName & Space$(1) _
& fldDataInfo
End With
Next i
End With
MsgBox sPath & "SCHEMA.INI has been created."
CreateSchemaFile = True
CreateSchemaFile_End:
Close Handle
Exit Function
CreateSchemaFile_Err:
Msg = "Error #: " & Format$(Err.Number) & vbCrLf
Msg = Msg & Err.Description
MsgBox Msg
Resume CreateSchemaFile_End
End Function
---------------------
this is my code:
If CreateSchemaFile(True, Mid(fileName, 1, InStrRev(fileName, "\")), Mid(fileName, InStrRev(fileName, "\") + 1), "tempSubTestScoresExp") Then
DoCmd.TransferText acExportDelim, , "tempSubTestScoresExp", fileName, True, ""
CurrentDb.TableDefs.Delete
MsgBox ("File export complete")
End If
fileName is a global string variable with the path to the export file.
The code works, but when I look at my export file the first line(field names) is tab delimited with quotes around the field names, which is what I want, except for the quotes. however, all the data on subsequent lines is embedded in quotes and comma delimited. I don't know what is going on....I thought using the schema.ini would have given me flexibility to have a tab delimited export file without quotes. Can anyone shed light on this for me. I am increasing the points!
requisiteSystem - Looks like you've received some solid advice from multiple experts. Since this question appears to be getting old, please consider awarding points for the effort and closing it. Thanks in advance. -Jim
Routinet: You are an cleanup volunteer machine today... :)
Yeah, I actually time to spare! I keep looking around for something else I SHOULD be doing, remember there isn't anything, then go to the next question. :) I knew there was a reason for holidays besides more commercials...
I think that every once in awhile, but there's always billable hours to be had...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You'll need to create and save an import spec that states tab delimeted, then refer to it in your TransferText line.
DoCmd.TransferText acExportDelim , {MyImportSpecName}, "tempSubTestScoresExp", fileName, True, "", 437
Hope this helps.
-Jim