DoCmd.TransferText using schema.ini

[patriciaxxx]

I have the code writing out the appropriate schema.ini like so. The schema resides in the correct folder with the database and text file.i need to use schema and not export / import specification.

Open "C:\Grouping\schema.ini" For Output As #4
sch1 = strTblName
sch2 = "ColNameHeader = True"
sch3 = "CharacterSet = ANSI"
sch4 = "Format = Delimited(,)"
sch5 = "TextDelimiter=^"
sch6 = "Decimal=."
sch7 = "MaxScanRows=1"
Print #4, "[" & sch1 & "]"
Print #4, sch2
Print #4, sch3
Print #4, sch4
Print #4, sch5
Print #4, sch6
Print #4, sch7
Close #4

And I have the Export going like this:

DoCmd.TransferText acExportDelim, schema, "tblMyTableName", CurrentProject.Path & "\MyTableName.txt", True


And what I get is a text file where the FIRST ROW ONLY (i.e. the headers) use ^ as the text delimiter, and all the subsequent lines use quotes, the schema settings just don't seem to effect them at all.

I have tried every permutation of headers true / false, including them or not, and tried .txt and .csv files, but I can’t work out what is wrong or how to get it to work properly.

Please can anyone help me.

 

[vbajock]

The "textDelimiter" parameter is a poorly supported "undocumented" parameter, a Microsoft euphemism for the phrase "you will be lucky to get it to work". You'll have better luck writing your file out line by line to a text file, I use an ADO loop, using the field type property to select the proper delimiters, this should help:

Private Function FieldIsString(FieldObject As ADODB.Field) _
As Boolean

'Input = ADODB.Field Object

'EXAMPLE USAGE
'After connecting to data source via ADO.
'Dim myRS As ADODB.Recordset
'Dim bIsString as boolean
'Set myRS.ActiveConnection = myADOConnection
'myRS.Open "SELECT * FROM MyTABLE"
'bIsString = FieldIsString(myRS.Fields(0))


'could raise an error here

If Not TypeOf FieldObject Is ADODB.Field Then Exit Function
Select Case FieldObject.Type
Case adBSTR, adChar, adVarChar, adWChar, _
adVarWChar, adLongVarChar, adLongVarWChar
FieldIsString = True
Case Else
FieldIsString = False
End Select

End Function

 

[DrSimon]

While I agree that the use of schema.ini is poorly documented, I have used it successfully to import data from a legacy system.
Don't know if it helps but here's the contents of an example schema.ini I used to import 3 files (they were for Biochemistry data). I couldn't use import specification because the output changed and I had to write the schema.ini each time to correspond with the exported files. Sadly this was a number of years ago and I can't remember much more about it!


[res.txt]
ColNameHeader=False
Format=TabDelimited
Col1="IPRN" Char
Col2="Date" Datetime
Col3="Time" Datetime
Col4="DFT" Char
Col5="HB" Char
Col6="WBC" Char
Col7="NEUT" Char
Col8="PLT" Char
Col9="NA" Char
Col10="K" Char
Col11="UREA" Char
Col12="CREAT" Char
Col13="CCAL" Char
Col14="ALB" Char
Col15="TBIL" Char
Col16="GGT" Char
Col17="ALT" Char
Col18="AST" Char
Col19="ALP" Char
Col20="CEA" Char
Col21="CA199" Char
Col22="CA125" Char
Col23="CA153" Char
Col24="PSA" Char
Col25="LDH" Char
Col26="HCG" Char
Col27="AFP" Char
Col28="URATE" Char

[dmg.txt]
ColNameHeader=False
Format=TabDelimited
Col1="IPRN" Char
Col2="MPI" Char
Col3="Surname" Char
Col4="Forename" Char
Col5="DOB" Char
Col6="Sex" Char
Col7="Address" Char


[id.txt]
ColNameHeader=False
Format=TabDelimited
Col1="CLN_Code" Char
Col2="CLN_Desc" Char
Col3="CLN_Custom_Spr_Path" Char