Bob Barnes said:
Within double-quotes of a CSV/ text file... In Access, we can control
those
w/ KeyPress..I'm using a handheld software where apparently I cannot
control
those w/ KeyPress...therefore Comma - Apostrophe - Carriage Return can be
entered, and are included in the Text Export controlled by that software.
As far as I know, when working w/ text files (IF a comma or an apostrophe
or
a carriage return is entered in a Field), it WILL cause the code within
Access to fail w/ things like Error Number 3421, or another error
"..subscript out of range".
No, not true, the only exception is the carriage returns (you can't have
carriage return in the csv file, since that means start a new line and
record.
so as long as the files are exported with proper texts qualifiers around the
data, MS access will correctly import them for example
FirstName,LastName <- first row contains the field names
"Albert","Kallal"
"alb,,,,ert","Kallal"
"alb"ert abc" def","smith"
The above file will import correctly if you use the import wizard and tell
access that the text qualifier is double quotes as above.
However the one thing that will not work is if there's new line characters
in the text stream.
What I would do in this case is simply write some code that the processes
the file first, and removes perhaps the single quotes, carriage returns, and
some of these other things that might be in the text.
It's not hard to read in a text file in directly MS access, and process
each of the lines by lines. however having asked cared returns in the middle
of the text is can improve quite difficult your processing retained to
distinguish whether this is just a break in text, or in fact is supposed to
be in.
The "smaple" code reads a text file line by line:
Sub ReadTextFile
Dim strFile As String
Dim intF As Integer
Dim strLineBuf As String
Dim lngLines As Long
Dim lngBlank As Long
strFile = "c:\my data\MyData.txt"
intF = FreeFile()
Open strFile For Input As #intF
Do While EOF(intF) = False
Line Input #intF, strLineBuf
If Trim(strLineBuf) = "" Then
lngBlank = lngBlank + 1
Else
lngLines = lngLines + 1
End If
Loop
Close intF
End If
MsgBox "Number non blank lines = " & lngLines & vbCrLf & _
"Blank lines = " & lngBlank & vbCrLf & _
"Total = " & lngBlank + lngLines
End Function
So one could modify the above code to strip out certain characters, parse
out the data and insert it into a table directly, and thus you would not
have to use the built in import wizards (and thus would not be restricted to
the limitations that the import wizard have)
You could also write code that reads the text file, strips characters out,
and then writes the text file back out. Then, you can use the built in
import wizards (this approach Might wind up saving you writing some code, as
you only write the code to strip out the characters, and not actually insert
the data directly into the table).
So keep in mind is that if the application produces correctly formed csv
files with commas or quotes in the middle of the text fields, MS access will
import this data correctly. I'm afraid the line returns in the data are
going to be problem however.
Another possible approaches to try opening the text filed with excel, as
excel's got a little more smarts in terms of trying to deal with this stuff,
once the data is in excel, then you pull from there into access.
