Line Input..issues w/ Import

[Bob Barnes]

"2 on bay 2, need a 4" and 8'",2008-05-14 00:00:00,"Bob, OK"
"Bad sump","",,""
"cannot see gauge needs s
cannot read gauge",,""

I'm using Line Input code w/ a text file. It has 9 fields. There are NO
issues w/ any data in Fields 1 thru 6... Fields 7 thru 9 are above. When
there's an embedded comma, double-quote, or in a carriage return/ linefeed
occurs..in Field 7 or Field 9,
the issue occurs.

Have discussed this w/ Albert K, and he recommended using a delimiter of "'
" & Chr(34).

The text file is generated from another software. The 8th Field is written
in a format either as..
,2008-05-14 00:00:00,
....or
,,
...so I can't use a delimiter of "' " & Chr(34).

When the 7th or 9th Fields have an embedded comma, double-quote, or in a
carriage return/ linefeed occurs, it eventually results in Error 3421 or
Error 9..subscript out of range. I can trap those and let the User correct
the text file.

I tried the Access Import Wizard, and it also has issues w/ the embedded
comma, double-quote, or in a carriage return/ linefeed.

Is it best to lell the users, using a Windows CE software Visual CE, not to
enter embedded comma, double-quote, or a carriage return/ linefeed (they
might do that), get a Windows CE keyboard w/ those keys disabled, trap the
errors...or another way??

TIA - Bob

 

[Albert D. Kallal]

It's probably important to note that the whole computer industry is based on
standards.

If you have a program that expects the data to be an excel format, and they
give you data in word document, what will you do?

answer: make sure they give you data in the correct format that you need. it
is REALLY that simple.

A correct delimited file does not allow linefeed/carriage returns. You can
*usually* import comma delimited data with a comma in it AS LONG as it has
a text qualifier such as " (double quotes). in fact MS access will handle
this kind of input data.

however, indebted double quotes will not work, and as far as I know comma
delimited files will not work this way.

I would enquire if you can get the data perhaps in a different file
format, perhaps such as dBase, and that would allow a mix of carriage
returns, single quotes, double quotes etc...

simply put, in plain English:

comma delimited files do NOT work this way.

If you're designing requirements need to allow quotes, double quotes,
comma's, and carriage returns/linefeeds in your data, then you simply need
to use a different technology and file format that allows these kinds of
chars in the data stream..


I translate this even further for you:

The simple solution is to get the vendors of the software to follow industry
standards, or simply run them out of town, and get them into cooking burgers
or doing Dumpster diving for a living. Comma delimited files can not be used
here.

So you can:

a) have their software produce the correctly formatted, delimited files
or
b) if you need to allow those characters in the data, then don't use
comma delimited file, and use a different format
or
c) get the vendor to build some software that pre-processes the
csv data file and removes the offending characters so you can use
standard import routines.

d) write this pre processing software your self and send someone a bill
for
this lost time...

 

[Bob Barnes]

Albert..your..
c) get the vendor to build some software that pre-processes the
csv data file and removes the offending characters so you can use
standard import routines.

That might be the solution. If not...what I had concluded in my initial Post.

Thank you - Bob