Schema.ini woes - field problems....

[rogge]

Please help with 'false' field name being found in text file...

I am trying to import a text file into access using TransferText and
schema.ini:

DoCmd.TransferText acImportDelim, , tableName, filePath & fileName


the table is linked to a table with the same name in a back-end data base,
in a different folder,

with same name. The text files lie in a different folder. The table names
are the same as is the

text file, with the exception of the extension.

I get a Run-time error '2391':
Field 'F1' doesn't exit in destination table 'Account_Specific.'

So I searched through the file, Account_Specific.csv. I could not find the
string 'F1' or Like

"*F1*". Notepad could not find the string; Excel could not find the string.

I tried appending the 'HasFieldNames' option. but that also resulted in errors
False: exact same error

true: can't find a different field: 'Process Name_CUST CPM_Cond.....'
This happens to be the first line in the text file


I have tried changing some of the settings in the schema.ini file, but no
luck, and yes, this file

is in the same folder as the text file.... Code runs before
DoCmd.TransferText that makes sure that

the data structure of the text file and the table are congruent. the table's
field names are

examined against the first line in the text file. Then the table name and
file name are placed in

an array. A procedure is called, passing these names, to create the
schema.ini file. (to be sure

the correct info is passed, i checked with MsgBox.)

schema.ini is created on the fly, just before DoCmd.TransferText is run


[Account_Specific.csv]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=OEM
DecimalSymbol=.
NumberDigits=0
CurrencySymbol=$
CurrencyPosFormat=$1
CurrencyDigits=0
CurrencyThousandSymbol=,
CurrencyDecimalSymbol=.


Front-end.mdb
<text file folder>
schema.ini
Account_Specific.csv
file1.csv
file2.csv

<data folder>
back-end.mdb

 

[John Nurick]

Hi Rogge,

Three things come to mind.

1) The sample section of schema.ini you provide doesn't include any
field specifications. I'm not sure of the relationship between
ColNameHeader=True and field specifications in schema.ini, but I suggest
you include field specifications for one table and see if that makes a
difference.

2) Make quite quite certain that your text files are well-formed. An
extra delimiter in a single line somwhere can raise the error you're
experiencing.

2) Could there possibly be a timing issue here? (i.e. TransferText is
trying to read schema.ini before your previous code has finished writing
it - or before the data has been written to file, allowing for caching
and delayed writes etc.) Maybe a DoEvents will help.

Otherwise - and in my experience this is

Please help with 'false' field name being found in text file...

I am trying to import a text file into access using TransferText and
schema.ini:

DoCmd.TransferText acImportDelim, , tableName, filePath & fileName


the table is linked to a table with the same name in a back-end data base,
in a different folder,

with same name. The text files lie in a different folder. The table names
are the same as is the

text file, with the exception of the extension.

I get a Run-time error '2391':
Field 'F1' doesn't exit in destination table 'Account_Specific.'

So I searched through the file, Account_Specific.csv. I could not find the
string 'F1' or Like

"*F1*". Notepad could not find the string; Excel could not find the string.

I tried appending the 'HasFieldNames' option. but that also resulted in errors
False: exact same error

true: can't find a different field: 'Process Name_CUST CPM_Cond.....'
This happens to be the first line in the text file


I have tried changing some of the settings in the schema.ini file, but no
luck, and yes, this file

is in the same folder as the text file.... Code runs before
DoCmd.TransferText that makes sure that

the data structure of the text file and the table are congruent. the table's
field names are

examined against the first line in the text file. Then the table name and
file name are placed in

an array. A procedure is called, passing these names, to create the
schema.ini file. (to be sure

the correct info is passed, i checked with MsgBox.)

schema.ini is created on the fly, just before DoCmd.TransferText is run


[Account_Specific.csv]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=OEM
DecimalSymbol=.
NumberDigits=0
CurrencySymbol=$
CurrencyPosFormat=$1
CurrencyDigits=0
CurrencyThousandSymbol=,
CurrencyDecimalSymbol=.


Front-end.mdb
<text file folder>
schema.ini
Account_Specific.csv
file1.csv
file2.csv

<data folder>
back-end.mdb

 

[rogge]

John: After posting i tried some other things, including field info as shown
below. Tried eliminating ColNameHeader

DoEvents() results "0" just before DoCmd.TransferText

However, when using 'specs' there is no problem...


New schema.ini:

[Account_Specific.csv]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=OEM
DateTimeFormat=yyyy-mm-dd
DecimalSymbol=.
NumberDigits=0
CurrencySymbol=$
CurrencyPosFormat=$1
CurrencyDigits=0
CurrencyThousandSymbol=,
CurrencyDecimalSymbol=.
Col1=ID Integer
Col2=Process Name Char Width 255
Col3=CUST CPM Bit
Col4=Condition Char Width 255
Col5=Created by Char Width 255
Col6=Customer Reference Char Width 50
Col7=Data Classification Char Width 50
Col8=Delete Bit
Col9=Due Date Date
Col10=Send Notification Bit
Col11=First Name Char Width 255
Col12=IBM CPM Bit
Col13=Key Date Bit
Col14=Long Description LongChar
Col15=MPS Bit
Col16=Record ID Char Width 255
Col17=Originating Organization Char Width 255
Col18=Originator Char Width 255
Col19=Assigned To Position Char Width 255
Col20=Priority Char Width 255
Col21=Program Date 1 Date
Col22=Program Date 2 Date
Col23=Program Date 3 Date
Col24=Program Date 4 Date
Col25=Program Defined 1 Char Width 255
Col26=Program Defined 2 Char Width 255
Col27=Program Defined 3 Char Width 255
Col28=Program Defined 4 Char Width 255
Col29=Program Defined 5 Char Width 255
Col30=Program Defined 6 Char Width 255
Col31=Status Reason Char Width 255
Col32=Remarks Char Width 255
Col33=Status Char Width 255
Col34=Title Char Width 255
Col35=v1 ID Char Width 255
Col36=Country Char Width 255
Col37=Program Keyword Char Width 255
Col38=Region Char Width 255
Col39=Brief Description Char Width 255
Col40=Assigned To Char Width 255
Col41=Date Created Date
Col42=Assigned Organization Char Width 255

File contents... tried with *.txt and *.csv no difference



Process Name CUST CPM Condition Created By Customer Reference Data
Classification Delete Due Date Send Notification First Name IBM CPM Key
Date Long Description MPS Record ID Originating
Organization Originator Assigned To Position Priority Program Date 1 Program
Date 2 Program Date 3 Program Date 4 Program Defined 1 Program Defined
2 Program Defined 3 Program Defined 4 Program Defined 5 Program Defined
6 Status Reason Remarks Status Title v1 ID Country Program
Keyword Region Brief Description Assigned To Date Created Assigned
Organization
N Green Heflin Y Y Rogge N N N 1-9ZZ6L Heflin WSV Blue IT PL
21 Medium 10. Draft Please delete me Heflin 2005-02-23
18:28:36 WSV Blue IT

 

[rogge]

Possible answer:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;142186

CAUSE: "The first row of the text file does not contain field names;
therefore, Microsoft Access assumes the fields are named "F1," "F2," and so
on."

Text file (first 1.5 fields, this example is comma delimited, previously i
was trying to use TAB since one can enter commas in fields)

Accepted Assessment Charge,Actual Asses.....

If we look closely, we see SPACES and NO double quotes. This is the
generated text file... scheise!

So... from the 'cause' information, I assume that Access thinks that
'Accepted' is the name of the fist field.

When comparing table field names to file field names, i search for the
delimiter and compare 'field.name' and the resultant string so this problem
with spaces is not evident. I guess I can add, at this time double qoutes...

Suggestions on how to thwart this Seibel text file would be greatly
appreciated...
I know that 'specs' can be used and work, but for every table i would need
to create at least 4: DMY, YMD, each with Tab delim. and "," delim.

 

[John Nurick]

Hi Rogge,

You haven't said that you've excluded the possibility of problems in the
structure of the text files (e.g. an extra tab character on a line
somewhere). Be sure to do that. I use a little Perl script to do this
job, and have pasted it at the end of this message.

Otherwise, I'm not sure what's going on that would produce the "Field
F1" error. But sometimes if one encounters problems when using
schema.ini with TransferText, they disappear if the TransferText call is
replaced by execution of the corresopnding query. This seems to more
reliably find and use the schema.ini settings. Give it a try.

E.g.

'Make-table query
strSQL = "SELECT * INTO MyNewTable " _
& "FROM [Text;HDR=Yes;Database=C:\Temp\;].MyFile#txt;"

'Append query
strSQL = "INSERT INTO MyExistingTable SELECT * " _
& "FROM [Text;HDR=Yes;Database=C:\Temp\;].MyFile#txt;"

DBEngine(0).Workspaces(0).Execute strSQL, dbFailOnError

John: After posting i tried some other things, including field info as shown
below. Tried eliminating ColNameHeader

DoEvents() results "0" just before DoCmd.TransferText

However, when using 'specs' there is no problem...


New schema.ini:

[Account_Specific.csv]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=OEM

[snip]


############# start of code #############
#countdelims.pl
#Scans a text file and reports on the numbers of delimiters.
#Doesn't work in delimited-quoted files (e.g. CSV).

$syntax="
Syntax:

perl countdelims.pl delimiter filespec

delimiter can be a character or string.
Use \\t for tab.
Use "|" for pipe or CMD.EXE will interpret it.
";

die "$syntax\n" unless $ARGV[1];
$delim = qq($ARGV[0]);
print "Delimiter \"$delim\"\n";
shift;
$max = 0;
$min = 10e10;
while (<>) {
chomp;
$new = eval "tr/$delim/$delim/";
if ($new < $min) {
$min = $new;
$minline = $.;
}
if ($new > $max) {
$max = $new;
$maxline = $.;
}
}
print "Max: $max (e.g. line $maxline)
Min: $min (e.g. line $minline)\n";

############## End of code #################

 

[Joe Fallon]

F1 is the name that is used when importing the first field in a file without
any field names.
F2 is the second...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Your schema.ini file does not conform to the spec.
You have spaces in your column names.

ColumnName The text name of the column. If the column name contains
embedded spaces, you must enclose it in double quotation marks.


--
Joe Fallon
Access MVP

John: After posting i tried some other things, including field info as
shown
below. Tried eliminating ColNameHeader

DoEvents() results "0" just before DoCmd.TransferText

However, when using 'specs' there is no problem...


New schema.ini:

[Account_Specific.csv]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=OEM
DateTimeFormat=yyyy-mm-dd
DecimalSymbol=.
NumberDigits=0
CurrencySymbol=$
CurrencyPosFormat=$1
CurrencyDigits=0
CurrencyThousandSymbol=,
CurrencyDecimalSymbol=.
Col1=ID Integer
Col2=Process Name Char Width 255
Col3=CUST CPM Bit
Col4=Condition Char Width 255
Col5=Created by Char Width 255
Col6=Customer Reference Char Width 50
Col7=Data Classification Char Width 50
Col8=Delete Bit
Col9=Due Date Date
Col10=Send Notification Bit
Col11=First Name Char Width 255
Col12=IBM CPM Bit
Col13=Key Date Bit
Col14=Long Description LongChar
Col15=MPS Bit
Col16=Record ID Char Width 255
Col17=Originating Organization Char Width 255
Col18=Originator Char Width 255
Col19=Assigned To Position Char Width 255
Col20=Priority Char Width 255
Col21=Program Date 1 Date
Col22=Program Date 2 Date
Col23=Program Date 3 Date
Col24=Program Date 4 Date
Col25=Program Defined 1 Char Width 255
Col26=Program Defined 2 Char Width 255
Col27=Program Defined 3 Char Width 255
Col28=Program Defined 4 Char Width 255
Col29=Program Defined 5 Char Width 255
Col30=Program Defined 6 Char Width 255
Col31=Status Reason Char Width 255
Col32=Remarks Char Width 255
Col33=Status Char Width 255
Col34=Title Char Width 255
Col35=v1 ID Char Width 255
Col36=Country Char Width 255
Col37=Program Keyword Char Width 255
Col38=Region Char Width 255
Col39=Brief Description Char Width 255
Col40=Assigned To Char Width 255
Col41=Date Created Date
Col42=Assigned Organization Char Width 255

File contents... tried with *.txt and *.csv no difference



Process Name CUST CPM Condition Created By Customer Reference Data
Classification Delete Due Date Send Notification First Name IBM CPM Key
Date Long Description MPS Record ID Originating
Organization Originator Assigned To Position Priority Program Date 1
Program
Date 2 Program Date 3 Program Date 4 Program Defined 1 Program Defined
2 Program Defined 3 Program Defined 4 Program Defined 5 Program Defined
6 Status Reason Remarks Status Title v1 ID Country Program
Keyword Region Brief Description Assigned To Date Created Assigned
Organization
N Green Heflin Y Y Rogge N N N 1-9ZZ6L Heflin WSV Blue IT PL
21 Medium 10. Draft Please delete me Heflin 2005-02-23
18:28:36 WSV Blue IT

 

[rogge]

I was afriad of those spaces... Thank you very much for your help. If you
are not doing anything this weekend, you can help ceate spec files for 29
tables, 6 date formats, and 3 delimiters... this is 348!!! Yee haw! Ya'll
have a great weekend!

I'm going to try to find a way to circumnaviagte this whole spaces thing...

 

[rogge]

Here is the deal: the first line of text must also have text qualifiers
(here using double quotes)

e.g.
"Accepted Assessment Charge","Actual Assessement Charge","Agreem..."

Each piece of data, though, does not seem to need the text qualifiers.

 

[John Nurick]

That's good to know. I only use spaces in field names myself for
specific purposes, so hadn't encountered the problem.

 

[rogge]

Another limitation found with schema.ini: uses most often used data type
rather than most forgiving...

e.g. the text file has a field with the following information:
2254
bidding
3476
1234

This field will be imported into a text field in a table. However, a
Run-time error occurs for a numeric field overflow. Why? 'bidding'

schema.ini looked at each entry and assumed that the data type to be
imported was an integer and 'bidding' is not an integer. If 'bidding' is
removed or changed to a number, no problem.

 

[John Nurick]

It's the Jet database engine that scans the file to decide field types,
not schema.ini. You can prevent this behaviour by specifying field
names, types and sizes in schema.ini, and you can control it to some
extent by modifying registry or .ini file entries. See the Access help
topic "Initializing the Text Data Source Driver" for details.



This sort of thing is to be expected if you don't specify the field
types yourself in schema.ini.

Initializing the Text Data Source Driver

 

[rogge]

Here you go John: I have also tried restarting Access to 'manually' close
the DB engine, as well as restarting the computer. In schema.ini I tried
switching 'Text' and 'Char'; expected the same result, got the same result.
Since this is a delimited file, I removed 'Width 255' and 'Width 50';
expected same result, hoped for a different result, and got the same result.
Also tried switching the character type between 'ANSI' and 'OEM'.

In the registry... Jet\4.0\ISAM Formats\Text\ has an entry not specificed
by 'Initializing the Text Data Source Driver'. It seems, according to
KB245524, this entry is correct:

FormatFunction REG_SZ txt,SOA_RptToAscii,1,MS-DOS Text (*.txt)

Looked into the ODBC Data Source Admin: MS Text Driver is Version 4.00.6304.00

Of the 21 records three have data in Col13="Cust Account Code". Two of
fields are integers, '20112' and '20222', the third is a string 'N/A'. The
file does not enclose these in singe quotes. By adding a non-numeric
character to at least one of the integers or removing 'N/A', the run-time
error does not exist.

(BTW DAMN those spaces in the field names!)

[RFS.csv]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=OEM
TextDelimiter="
DateTimeFormat=yyyy-mm-dd
CurrencySymbol=€
CurrencyPosFormat=($1)
CurrencyDigits=0
CurrencyNegFormat=(€1)
CurrencyThousandSymbol=,
CurrencyDecimalSymbol=.
DecimalSymbol=.
NumberDigits=0
NumberLeadingZeros=0
Col1="Accepted Assessment Charge" Text Width 50
Col2="Actual Assessement Charge" Text Width 50
Col3="Agreement Reference" Text Width 255
Col4="Agreement Reference Details" Text Width 255
Col5=Area Text Width 255
Col6=BTT Text Width 255
Col7="Billing Completed" Date
Col8="CUST CPM" Bit
Col9="Canceled the RFS" Date
Col10=Category Text Width 255
Col11=Condition Text Width 255
Col12="Created By" Text Width 255
Col13="Cust Account Code" Text Width 255
Col14="Customer Approver" Text Width 255
Col15="Customer Contact" Text Width 255
Col16="Customer Reference" Text Width 255
Col17="Data Classification" Text Width 255
Col18="Date Cust Accepted Charge" Date
Col19=Delete Bit
Col20="Delivery Authorized" Date
Col21=Dependency Text Width 255
Col22="Send Notification" Bit
Col23="Estimated Assessement Charge" Text Width 255
Col24="First Name" Text Width 255
Col25="IBM Account Code" Text Width 255
Col26="IBM CPM" Bit
Col27="Implementation Completed" Date
Col28="Implementation Planned" Date
Col29="Implementation Requested" Date
Col30="Implementation Revised" Date
Col31="In Scope" Bit
Col32="Key Date" Bit
Col33="Level of Approval" Text Width 255
Col34="Local Price" Text Width 50
Col35=Location Text Width 255
Col36="Long Description" LongChar
Col37=MPS Bit
Col38="Opportunity Owner" Text Width 255
Col39="Opportunity Type" Text Width 255
Col40="Record ID" Text Width 255
Col41="Originating Organization" Text Width 255
Col42=Originator Text Width 255
Col43="Assigned To Position" Text Width 255
Col44=Price Text Width 50
Col45=Priority Text Width 255
Col46="Program Date 1" Date
Col47="Program Date 2" Date
Col48="Program Date 3" Date
Col49="Program Date 4" Date
Col50="Program Defined 1" Text Width 255
Col51="Program Defined 2" Text Width 255
Col52="Program Defined 3" Text Width 255
Col53="Program Defined 4" Text Width 255
Col54="Program Defined 5" Text Width 255
Col55="Program Defined 6" Text Width 255
Col56="Proposal Completed" Date
Col57="Proposal Planned" Date
Col58="Proposal Planned Revision" Date
Col59="Proposal Requested" Date
Col60="Proposal Response Accepted" Date
Col61="Proposal Response Planned" Date
Col62="Proposal Response Rejected" Date
Col63="Status Reason" Text Width 255
Col64="Rejected the RFS" Date
Col65=Remarks Text Width 255
Col66="Request Accepted" Date
Col67="Request Submitted" Date
Col68="Requirements Agreed" Date
Col69="Service Provider Org" Text Width 255
Col70="Solution Accepted" Date
Col71="Solution Completed" Date
Col72="Solution Planned" Date
Col73=Status Text Width 255
Col74=System Text Width 255
Col75=Title Text Width 255
Col76="v1 ID" Text Width 255
Col77=Country Text Width 255
Col78="Program Keyword" Text Width 255
Col79=Region Text Width 255
Col80="Related ID" Text Width 255
Col81="Related Title" Text Width 255
Col82="Related Type" Text Width 255
Col83=State Text Width 255
Col84="Brief Description" LongChar
Col85="Assigned To" Text Width 255
Col86="Date Created" Date
Col87="Assigned Organization" Text Width 255