Using the Text Qualifier in the Import Wizard

[Guest]

I'm currently using Access 2007 in Vista. My question is how do you, if
possible, write in VBA to import a CSV file and make the import recognize the
text qualifier of quotes? I can't have a specification done, because this is
a tool that will use the same code to import differet files, having different
number of columns and different data all together.

I know it's a docmd.transfertext but I haven't seen anywhere to be able to
use that text qualifier. Unless I'm just missing it.

Thanks,
Nancy

 

[Guest]

If your text qualifier is double-quotes you're OK, because that is the
default. Just use ACImportDelim in your TransferSpreadsheet command and leave
the spec blank. I'm not sure how you'd do it with single quotes, other than
by creating a spec. There may be a way to do it that some of the more expert
users here know about.

 

[Guest]

So you're saying that by use the acImportDelim, that it's assumming that all
the data fields coming across has double quotes around them? My data doesn't
have double quotes, it only has single quotes and the single quotes are only
on data fields that have the comma inside the data field. Like having a
field that is storing city, state. So that field would be the only field
that would have single quotes around it. Will this default double quote work
for that? Actually I'm guessing not, since when I try and run the
transfertext, it puts the data in seperate columns where the comma is. What
if I had all columns have the double quotes? Would that be a workaround?

 

[Guest]

If you take the single quotes that are used to delimit fields with a comma
and change those to double quotes then that should work. The only time you
need text delimiters is when you have a field that has a comma in it. Are
there other times where you have a comma in a field other than with city and
state? Shouldn't city and state be separate fields? Even if they need to be
together and they're put in separate fields in the table, can't you connect
them again through VBA code?

 

[Guest]

It's actually not City and State field. I was just using that as an example.
The field is a set of steps, if you will. So it's open for any type of text
to be entered into it. Therefore commas can go in there to seperate
different tasks or something like that. Kind of hard to explain what the
field really is. Do you have any thoughts about leading zeros being dropped
off when importing a CSV file? What I would really like is to have all
fields import as text no matter what's in the fields. Again, I can't use a
specification because it's the same code that will be importing all different
types of data, and number of columns. So that's an unknown. Well, actually
I know how many columns there will be, but each file is different. So I set
up a table that lists out the column headers and so forth so I can use that
in my programming to know how many columns to go out and grab from these
files. Do you know of anyway to force the transfertext command to make all
fields a text field?

 

[Guest]

Sorry, but my experience is somewhat limited. I've always used Import specs.
Your situation is a bit unusual. If you know of a practical limit to the
number of columns you could just create specs for each number of columns.
You'd be stuck with field names like FIeld1, Field2, ..., but I can't think
of anything else if your input files are so unpredicatble. You might want to
do a web search on TransferText and see if there's something out there. And
try posting here again - one of the MVPs may know of a way to do what you
want, but may not see this post since it's down a ways in the list and has
several replies in it.

 

[John Nurick]

Hi Nancy,

As Jim said, if you use TransferText acImportDelim with no file
specification, Access/Jet uses default settings of
Delimiter: ,
Qualifier: "
and it creates field types on the basis of what what it finds in the
text file.

It sounds as if you have
Delimiter: ,
Qualifier: '
and you also want all the data to be imported into text fields.

In that case the choices are

1) write code to pre-process the file before import, changing the
qualifier to " before importing the file

2) use an import specification or schema.ini file.

(1) requires replacing all ' qualifiers with ", and adding " qualifiers
to previously unqualified fields. This is simple if ' and " never occur
in the actual data but can get a bit more complicated otherwise.

For (2) the idea would be to write code that creates a schema.ini file
containing the field names and specifications for the file you want to
import. Provided schema.ini is in the same folder as your file, and
contains a section with the right filename, TransferText will find it
and use it just like an import specification. (You don't have to give
TransferText a specification name.)

If you already know the number and names of the fields it's not to hard
to write code that creates a custom schema.ini. See these links for
more:

How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

 

[Guest]

Thank you for this information. I'll look into this to see if I can get it
to work for me. But a little more detail on the files that I'm importing.
The data is coming as a CSV. When I open the file in Notepad, there aren't
any quotes, unless there is a comma in the field itself, like an example
would be "last name, first name". This would be the only field that actually
shows up with quotes around it when looking at the data in Notepad. So, I'm
not sure how to go about changing the text qualifier for all of the fields,
when they all don't have quotes around them? Will this pose a problem? And
this program that would do this, is this written in Access (VBA) to access
the external file before importing it? Or were you thinking of it being
written using something else? Thanks again, Nancy

 

[Guest]

I had previously responded to your email before I got involved with looking
into the information you had linked. I have done so now. And I have a
question that I've come across. I know I said I know how many columns there
are. Well, that's true to an extent. There is a set columns and column
names that I will be using later in the program I've written, however the CSV
file could possible have more columns that what I'm acklodging. So there's
one of two things. If there was a way to ignore the columns that aren't part
of the rest of the program, then how would I do that. Or if there's away to
take the CSV file and create the table, instead of passing it the table name.
So like I don't know the table name or structure, but I would like to create
that from the CSV. Is that possible? Thanks again for your help! So far I
think I'm on the right track, just need to get a couple things ironed out.

 

[John Nurick]

The data is coming as a CSV. When I open the file in Notepad, there aren't
any quotes, unless there is a comma in the field itself, like an example
would be "last name, first name". This would be the only field that actually
shows up with quotes around it when looking at the data in Notepad.

There's a bit of confusion somewhere, Nancy. Jim said earlier,
correctly, that if you have a CSV file where text fields are qualified
with double quotes you don't normally need an import specification. You
said that your files have single quotes.

Now you give us an example

"last name, first name"

which has double quotes. What are they really?

In a regular CSV file, it's not compulsory for text fields to be
qualified _unless_ they contain a comma.

However, if you have an unqualified field that contains numeric values,
Access will import it as a number field. To import into a text field
instead you have to do one of the following:

1) qualify the field.
2) use an import specification or schema.ini
3) create the table first with the field types you want: you can import
a numeric field from a CSV file into a text field in an existing table.

 

[John Nurick]

A) Does the text file have its field names in the first row of text?
B) Are those field names the same as the ones you'll be using later, or
do they at least have a 1:1 correspondence with them?

If so, then
1) use DoCmd.TransferText acLink to create a linked table (lets call it
tblLink) connected to the text file.

2) set up and save an append query that transfers the fields you
actually want from the linked table to your 'real' table.

When this is working, delete the linked table and write code to do the
following:

1) get the file's name and location from the user
2) create tblLink as above
3) run the query
4) delete tblLink

If A and B don't hold, then how do you know which field in the text file
is which?