Importing Delimted Files

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

I ran into something very weird and was curious if anyone
else has seen this.

I have two identical files, one using comma delimiting,
the othe using pipe delimiting. To match, I have two
Import Specifications stored in my database. I can
theoretically import either format to the same table,
since the field names are the same in both specs.

When I used the pipe delimted file & spec, the ONLY way I
could get the fields to populate correctly is if the table
structure had the fields listed in the same sequence as
they are in the import file. I had to go to the table
design and change the field sequence.

When I use the comma delimited spec, the fields in the
table are populated correctly. It did not seem to matter
that the structure of the table did not match the import
file & spec.

If the table structure did not match, Access modified the
Import Spec to match the table structure messing up the
current and future imports.

Has anyone else seen this?
 
Hi Martin,

I can reproduce it or something similar in Access 2003. IIRC there are
some other ways in which files with non-standard delimiters are treated
as "second-class citizens".

A work-round would - I think - be the same technique used when importing
files whose field names don't match those in the destination table:
access the file as a linked table (using an import spec as necessary)
and then use an append query to move the data into the destination table
while sorting out the field mapping.

Alternatively, run the pipe-delimited files through a script to convert
them to CSV (double any quote marks in the data, enclose text fields in
quotes, and replace pipes with commas).
 
The reason I was attempting to use pipe delimiting is
because of a problem I have with imbedded double-quotes
("). My plan was to use the pipe delimiter to get around
that problem, but then ran into this problem.

I was flabbergasted that one delimiter would work
differently than another. Not to mention the forced
changes to the import specs.

For me, it is just as easy to resequence the fields in the
table to match the imported file, since that is not
critical to my application. The resequence won't afffect
the comma delimited file, and it will still work with the
pipe.

Thanks.

-----Original Message-----
Hi Martin,

I can reproduce it or something similar in Access 2003. IIRC there are
some other ways in which files with non-standard delimiters are treated
as "second-class citizens".

A work-round would - I think - be the same technique used when importing
files whose field names don't match those in the destination table:
access the file as a linked table (using an import spec as necessary)
and then use an append query to move the data into the destination table
while sorting out the field mapping.

Alternatively, run the pipe-delimited files through a script to convert
them to CSV (double any quote marks in the data, enclose text fields in
quotes, and replace pipes with commas).


I ran into something very weird and was curious if anyone
else has seen this.

I have two identical files, one using comma delimiting,
the othe using pipe delimiting. To match, I have two
Import Specifications stored in my database. I can
theoretically import either format to the same table,
since the field names are the same in both specs.

When I used the pipe delimted file & spec, the ONLY way I
could get the fields to populate correctly is if the table
structure had the fields listed in the same sequence as
they are in the import file. I had to go to the table
design and change the field sequence.

When I use the comma delimited spec, the fields in the
table are populated correctly. It did not seem to matter
that the structure of the table did not match the import
file & spec.

If the table structure did not match, Access modified the
Import Spec to match the table structure messing up the
current and future imports.

Has anyone else seen this?
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
I find this to be very strange.
I use Pipe delimited files all the time. I have never seen the problem you
describe.
I just tested it quickly in both A97 and A2003 and the re-arranged table
worked fine when importing the file.
I suggest you try it in a new mdb file and let us know what you find.
--
Joe Fallon
Access MVP



Martin said:
The reason I was attempting to use pipe delimiting is
because of a problem I have with imbedded double-quotes
("). My plan was to use the pipe delimiter to get around
that problem, but then ran into this problem.

I was flabbergasted that one delimiter would work
differently than another. Not to mention the forced
changes to the import specs.

For me, it is just as easy to resequence the fields in the
table to match the imported file, since that is not
critical to my application. The resequence won't afffect
the comma delimited file, and it will still work with the
pipe.

Thanks.

-----Original Message-----
Hi Martin,

I can reproduce it or something similar in Access 2003. IIRC there are
some other ways in which files with non-standard delimiters are treated
as "second-class citizens".

A work-round would - I think - be the same technique used when importing
files whose field names don't match those in the destination table:
access the file as a linked table (using an import spec as necessary)
and then use an append query to move the data into the destination table
while sorting out the field mapping.

Alternatively, run the pipe-delimited files through a script to convert
them to CSV (double any quote marks in the data, enclose text fields in
quotes, and replace pipes with commas).


I ran into something very weird and was curious if anyone
else has seen this.

I have two identical files, one using comma delimiting,
the othe using pipe delimiting. To match, I have two
Import Specifications stored in my database. I can
theoretically import either format to the same table,
since the field names are the same in both specs.

When I used the pipe delimted file & spec, the ONLY way I
could get the fields to populate correctly is if the table
structure had the fields listed in the same sequence as
they are in the import file. I had to go to the table
design and change the field sequence.

When I use the comma delimited spec, the fields in the
table are populated correctly. It did not seem to matter
that the structure of the table did not match the import
file & spec.

If the table structure did not match, Access modified the
Import Spec to match the table structure messing up the
current and future imports.

Has anyone else seen this?
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
PMFJI...

Do you have Name AutoCorrect selected in your database? Try deselecting it
(Tools | Options | General tab) and see if you then obtain the results that
Joe posted. This feature has many "strange" effects on databases, some not
so good, and perhaps this is another one of them.

--

Ken Snell
<MS ACCESS MVP>

martin said:
I just tried it again with a new mdb. I get the same
result.

I used the same pipe delimited file and imported it to two
tables in the new mbd--one has the field sequence that
matches the text file, the other has a mismatch in the
sequence. The text file imports correctly to the matching
table, but not to the table with the different field
sequence.

As near as I can tell, I am running Access 2002.

-----Original Message-----
I find this to be very strange.
I use Pipe delimited files all the time. I have never seen the problem you
describe.
I just tested it quickly in both A97 and A2003 and the re- arranged table
worked fine when importing the file.
I suggest you try it in a new mdb file and let us know what you find.
--
Joe Fallon
Access MVP



The reason I was attempting to use pipe delimiting is
because of a problem I have with imbedded double-quotes
("). My plan was to use the pipe delimiter to get around
that problem, but then ran into this problem.

I was flabbergasted that one delimiter would work
differently than another. Not to mention the forced
changes to the import specs.

For me, it is just as easy to resequence the fields in the
table to match the imported file, since that is not
critical to my application. The resequence won't afffect
the comma delimited file, and it will still work with the
pipe.

Thanks.


-----Original Message-----
Hi Martin,

I can reproduce it or something similar in Access 2003.
IIRC there are
some other ways in which files with non-standard
delimiters are treated
as "second-class citizens".

A work-round would - I think - be the same technique used
when importing
files whose field names don't match those in the
destination table:
access the file as a linked table (using an import spec
as necessary)
and then use an append query to move the data into the
destination table
while sorting out the field mapping.

Alternatively, run the pipe-delimited files through a
script to convert
them to CSV (double any quote marks in the data, enclose
text fields in
quotes, and replace pipes with commas).


On Wed, 1 Sep 2004 07:34:09 -0700, "Martin"

I ran into something very weird and was curious if
anyone
else has seen this.

I have two identical files, one using comma delimiting,
the othe using pipe delimiting. To match, I have two
Import Specifications stored in my database. I can
theoretically import either format to the same table,
since the field names are the same in both specs.

When I used the pipe delimted file & spec, the ONLY way
I
could get the fields to populate correctly is if the
table
structure had the fields listed in the same sequence as
they are in the import file. I had to go to the table
design and change the field sequence.

When I use the comma delimited spec, the fields in the
table are populated correctly. It did not seem to
matter
that the structure of the table did not match the import
file & spec.

If the table structure did not match, Access modified
the
Import Spec to match the table structure messing up the
current and future imports.

Has anyone else seen this?




--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...


.
Click to expand...
Click to expand...
 
I unchecked the Perform Name Autocorrect option and tried
the import again--no luck. I went back and unchecked the
Track Name Autocorrect Info option and tried the import
again--same result.

It seems that the only solution is to resequence the
fields in the table design window.

-----Original Message-----
PMFJI...

Do you have Name AutoCorrect selected in your database? Try deselecting it
(Tools | Options | General tab) and see if you then obtain the results that
Joe posted. This feature has many "strange" effects on databases, some not
so good, and perhaps this is another one of them.

--

Ken Snell
<MS ACCESS MVP>

I just tried it again with a new mdb. I get the same
result.

I used the same pipe delimited file and imported it to two
tables in the new mbd--one has the field sequence that
matches the text file, the other has a mismatch in the
sequence. The text file imports correctly to the matching
table, but not to the table with the different field
sequence.

As near as I can tell, I am running Access 2002.

-----Original Message-----
I find this to be very strange.
I use Pipe delimited files all the time. I have never seen the problem you
describe.
I just tested it quickly in both A97 and A2003 and the
Click to expand...
re-
arranged table
worked fine when importing the file.
I suggest you try it in a new mdb file and let us know what you find.
--
Joe Fallon
Access MVP



The reason I was attempting to use pipe delimiting is
because of a problem I have with imbedded double- quotes
("). My plan was to use the pipe delimiter to get around
that problem, but then ran into this problem.

I was flabbergasted that one delimiter would work
differently than another. Not to mention the forced
changes to the import specs.

For me, it is just as easy to resequence the fields
Click to expand...
in
the
table to match the imported file, since that is not
critical to my application. The resequence won't afffect
the comma delimited file, and it will still work with the
pipe.

Thanks.


-----Original Message-----
Hi Martin,

I can reproduce it or something similar in Access 2003.
IIRC there are
some other ways in which files with non-standard
delimiters are treated
as "second-class citizens".

A work-round would - I think - be the same technique used
when importing
files whose field names don't match those in the
destination table:
access the file as a linked table (using an import spec
as necessary)
and then use an append query to move the data into the
destination table
while sorting out the field mapping.

Alternatively, run the pipe-delimited files through a
script to convert
them to CSV (double any quote marks in the data, enclose
text fields in
quotes, and replace pipes with commas).


On Wed, 1 Sep 2004 07:34:09 -0700, "Martin"

I ran into something very weird and was curious if
anyone
else has seen this.

I have two identical files, one using comma delimiting,
the othe using pipe delimiting. To match, I have two
Import Specifications stored in my database. I can
theoretically import either format to the same table,
since the field names are the same in both specs.

When I used the pipe delimted file & spec, the ONLY way
I
could get the fields to populate correctly is if the
table
structure had the fields listed in the same sequence as
they are in the import file. I had to go to the table
design and change the field sequence.

When I use the comma delimited spec, the fields in the
table are populated correctly. It did not seem to
matter
that the structure of the table did not match the import
file & spec.

If the table structure did not match, Access modified
the
Import Spec to match the table structure messing up the
current and future imports.

Has anyone else seen this?




--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top