ACCESS VBA: importing csv, mixed numeric and alpha. Bug?

[Guest]

The following is the boiled down version of a real-world problem.

I have two .csv files, workbook1 and workbookq

Workbook1 consists of 1 line, 1 field:
xxxxx

WorkbookQ consists of 2 lines, 1 field:
xxxxx
75104

I used the following code (copied from somewhere in microsoft.com) to link
to the two files.

Function LinkSchema()
Dim db As DAO.Database, tbl As DAO.TableDef, tblQ As DAO.TableDef
Set db = CurrentDb()
'*
Set tbl = db.CreateTableDef("workbook1")
tbl.Connect = "Text;DATABASE=C:\bugchaser;TABLE=workbook1.csv"
tbl.SourceTableName = "workbook1.csv"
db.TableDefs.Append tbl
db.TableDefs.Refresh
'*
Set tbl = db.CreateTableDef("workbookQ")
tbl.Connect = "Text;DATABASE=C:\bugchaser;TABLE=workbookQ.csv"
tbl.SourceTableName = "workbookQ.csv"
db.TableDefs.Append tbl
db.TableDefs.Refresh

End Function

The resulting tables are:

- Workbook1
xxxxx

- WorkbookQ
#Num!
75104

I interpret this to mean that Access is assuming that if there is a number
in a column, the field is numeric.

In the past I got round this by making the first row contain a non-numeric,
but, as you see, this does not work here.

Even if I use other methods to import into a file def where Fred is defined
as text, I get a similar result.

I am getting desperate enough to think of using excel to massage the file
before importing it to Access - it does not get much uglier than that!

I don't know if this is a bug or a feature. I do know it is a nuisance.

If anyone can shed light on this one, it would be of great help

 

[John Nurick]

The first thing that comes to mind is to create an import specification
that defines a text field, and pass its name in in the Connect strings.

However, if the files just contain one or two values I'd probably just
write VBA code to open them and read the values into variables, rather
than bother with creating linked tables.

On Tue, 10 Jan 2006 12:15:02 -0800, david h diamond <david h

 

[Guest]

Thanks for the prompt and helpful reply.

The import specification is probably the way to go. Unfortuantely the
example is boiled down from a file with many,many columns and thousands of
rows and I have never done an import specification before - great! a learning
experience!

If that doesn't work, your other suggestion will be the last resort, though
it means a significant bit of coding to read the file a line at a time and
parse for commas.

But I really would like to know if this is a BUG or a Feature. Is there
anyone from Microsoft or anywhere else who could tell us for sure? The way it
works now is not optimum.

 

[John Nurick]

You boiled the example down to something unrepresentative! Did you The
number of rows isn't usually an issue until you get into the millions,
but limitations on the number of fields (255) and the size of the record
(in practice, something over 2000 characters excluding the contents of
memo, hyperlink and OLE fields) can bite you.

The problem with the #NUM! errors arises because the Jet database engine
is linking the field in the text file as a number field even though it
contains a mix of numeric and text data. For information on the registry
entries that control this, look in the Help contents page for Jet SQL
reference, and within this (I think in the Windows Registry section) for
Initializing the Text and HTML Data Source Driver. You may be able to
solve the problem by tweaking a registry setting (though I've never
tried it myself).

Otherwise you'll need to use an import specification or schema.ini. (The
latter is an external text file that does the same job as the import
spec: it's documented in the help page mentioned above.) The one
advantage of using schema.ini is that it's easy to create one under
program control. What I'd probably do is use the existing, imperfectly
linked table to create a schema.ini using one of the techniques below,
and then edit it to turn the problematic fields from number into text
types. Re-creating the linked table should then use the schema and solve
your problem.

Access will create a schema.ini file for you if you use a make-table
query to export to a text file, e.g.
SELECT * INTO [text;database=C:\temp].[mytable#txt] FROM mytable;

See also:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090