Double quote issue with VB6, Access and text files
Jerry
This has been driving me crazy on and off for years and hope someone
can help.
Using VB6 I create an Access database. I then take a pipe delimited
text file and populate the fields of the database. This works really
well 99 percent of the time. Where I run into difficulties is when a
record has double quotes. One of two things happen...either I get a
message stating that the text file is not in the expected format or it
discontinues reading the file without any error messages.
Here is a sample
|1|Change site "A" to site "B"|
Please advise...
Jerry
PAK
How are you populating the fields - using the File/Import in Access, ADO or
DAO? What version of Access are you using?
Regards,
Drew
Jeff Johnson
news:48E92DD5...@nospam.yahoo.com...
> I then take a pipe delimited text file and populate the fields of the
> database.
How, exactly? I'm sure the crux of the problem is right here.
Jerry
Drew-
Using DAO. It's been awhile since I did anything with this code. I
believe it would open in Access 97 or 95. Maybe the declaration below
will help.
Set dbGrant = wsULSFCC.CreateDatabase(DBName, dbLangGeneral,
dbVersion30)
Keep in mind...Visual Basic is creating the database, tables and
fields. Visual Basic is then programmed to open up a text file that
is pipe delimited and read each line and then writing it to the
created database.
If it will help, I can create a stripped down version of the source.
Bottom line...if there's double quotes in the text file VB doesn't
like it. (or the text driver it's using.) If I manually change to
single quotes it works fine. However, there are 13 text files so that
isn't a good option.
Thanks.
Jerry
Jerry
There is a sample here...
http://ilgrant.home.att.net/fcc/grant_sample.zip
The VB code reads CO.dat file. Then fills in the database. However,
the line with the double quotes does not get placed into the database.
The database it creates is in Access 95 format. It should open at
least up to Access 2002.
Thanks.
Jerry
--
Jerry Smith
Midwest Grant Pages
http://ilgrant.home.att.net
The #1 Chicago Area Scanner Resource on the Web
Chicago Area Radio Monitoring Assoc. http://www.carmachicago.com
Jeff Johnson
> There is a sample here...
Which none of us is going to download. POST the relevant code here.
Ralph
We still don't know exactly how you are reading the data, and I haven't run
into the same problem you are describing, but have run into other problems
with dates and numbers. However, whenever odd 'translations' occur I have
found a schema.ini file usually helps.
http://www.geocities.com/neeqies/SchemaINI.html
ACC: How to Create a Schema.ini File Programmatically
http://support.microsoft.com/kb/155512
Different Jet Drivers/Providers can also have an effect.
hth
-ralph
Jerry
Dim dbGrant As Database 'frequency database
Dim dbAdmin As Database
Dim wsULSFCC As Workspace 'Administration
Dim tblCO As TableDef
Dim fldCO(5) As Field
Dim rsGrant As Recordset
Dim rsAdmin As Recordset
Dim iCount As Integer
Dim DBName As String
DBName = App.Path & "\CO.mdb"
Set wsULSFCC = DBEngine.Workspaces(0)
Set dbGrant = wsULSFCC.CreateDatabase(DBName, dbLangGeneral,
dbVersion30)
Set tblCO = dbGrant.CreateTableDef("CO") 'create entity table
Set fldCO(0) = tblCO.CreateField("RECORD TYPE", dbText, 2)
Set fldCO(1) = tblCO.CreateField("UNIQUE SYSTEM IDENTIFIER", dbText,
9)
Set fldCO(2) = tblCO.CreateField("ULS FILE NUMBER", dbText, 14)
Set fldCO(3) = tblCO.CreateField("CALL SIGN", dbText, 10)
Set fldCO(4) = tblCO.CreateField("Date", dbText, 10)
Set fldCO(5) = tblCO.CreateField("Description", dbMemo, 260) ' was
255
For iCount = 0 To 5
tblCO.Fields.Append fldCO(iCount)
Next iCount
dbGrant.TableDefs.Append tblCO
Set dbAdmin = OpenDatabase(App.Path, False, False, "Text;")
Set rsGrant = dbGrant.OpenRecordset("CO")
Set rsAdmin = dbAdmin.OpenRecordset("CO.dat") '
Do While rsAdmin.EOF = False
rsGrant.AddNew
For iCount = 0 To 5
rsGrant.Fields(iCount) = rsAdmin.Fields(iCount)
Next iCount
rsGrant.Update
rsAdmin.MoveNext
Loop
MsgBox "Done!"
End Sub
Reference=*\G{00025E04-0000-0000-C000-000000000046}#3.5#0#..\..\..\..\Program
Files\Common Files\Microsoft Shared\DAO\dao2535.tlb#Microsoft DAO
2.5/3.51 Compatibility Library
schema.ini
[co.dat]
ColNameHeader=False
Format=Delimited(|)
MaxScanRows=0
CharacterSet=ANSI
Jerry
CO|1175558||WPMV474||REFER TO ATTACHMENT RMK 11/05/98. [D]||
CO|1186237||KUX244||500 MOBILE UNITS ASSOCIATED WITH EACH MO
FREQUENCY. [f900]||
CO|1186237||KUX244|10/04/2005|Renewal Reminder Email notification was
sent out erroneously for this callsign||
CO|1196945||KIG660||ADD: 158.985 DELETE: 156.045 TO ELIMINATE
CO-CHANNEL INTERFERRENCE. [G1]||
CO|1234649||WPIE851||COPY OF LICENSE IS ATTACHED FOR KQC948. TYPE
ACCEPTANCE INFO IS ATTACHED. SKETCHES ARE ATTACHED. FAA STUDY
95-AWP-0124-OE ATTACHED. [D3D3]||
CO|1234649||WPIE851||MOVE AUTHORIZED LOCATION 1.27 MILES IN THE
DIRECTION OF 127 DEGREES. [D3]||
CO|1255816||KOE909||COPY OF LICENSE IS ATTACHED FOR KOE909. SKETCHES
ARE ATTACHED. [D3]||
CO|1255816||KOE909||ADD 460.400, DELETE 453.225. [D3]||
CO|1287330||KNEL292||REINSTATE CALL SIGN KNEL292 IF POSSIBLE [GE]||
CO|1287637||KNHA782||KNHA782. [G1]||
CO|1301201||WNWI404||GEN: APCO HAS REGIONAL COMMITTEE APPROVAL ON
FILE. NATIONAL PLAN REGION #40. [GEN]||
CO|1301201||WNWI404||RELOCATE B, DELETE 866.5875 & 868.7125. ADD
868.5225, 868.6625 & 868.8125. [GE]||
CO|1301722||WPMJ434||GEN: APCO HAS REGIONAL PLANNING COMMITTEE
APPROVAL ON FILE. PUBLIC SAFETY NATIONALPLAN REGION #17. [D3D]||
CO|1302208||KNNK867||MC LEAN CTY PROPOSES TO: (1) ADD CHANNELS
318,358,398,290,330,AND 370 TO SITE "A"; (2) ADD CHANNELS 290, 330,
AND 370 TO SITE "B"; (3) ADD CHANNELS 318, 358, AND 398 TO SITE "C".
[D3]||
CO|1608202||KD52615|08/23/2006|KD52615, Location 2 Frequency
466.95000000 has entered the auto termination process on 08/23/2006.||
CO|1608202||KD52615|08/23/2006|KD52615, Location 2 Frequency
468.60000000 has entered the auto termination process on 08/23/2006.||
CO|1608202||KD52615|09/20/2006|Frequency 466.95000000, Antenna 1,
Location 2 SHACKLEFORDS VA: has been returned to Active status as a
result of an approved PFR.||
CO|1608202||KD52615|09/20/2006|Frequency 468.60000000, Antenna 1,
Location 2 SHACKLEFORDS VA: has been returned to Active status as a
result of an approved PFR.||
CO|1778155||WNFQ454||ADDING 2 MOBILE ONLY FREQUENCIES [D3]||
CO|1795110||WPMI598|09/10/2008|WPMI598, Location 1 Frequency
463.60000000 has entered the auto termination process on 09/10/2008.||
CO|1795110||WPMI598|09/10/2008|WPMI598, Location 3 Frequency
463.60000000 has entered the auto termination process on 09/10/2008.||
CO|1795110||WPMI598|09/10/2008|WPMI598, Location 3 Frequency
468.60000000 has entered the auto termination process on 09/10/2008.||
Jerry
I posted the source code in a different message. If there is a entry
that I can put in the schema.ini that will deal with the double quote
issue I'm open to it.
Thanks.
Jeff Johnson
Oooookay, some relevant information:
> Set dbAdmin = OpenDatabase(App.Path, False, False, "Text;")
> schema.ini
> [co.dat]
> ColNameHeader=False
> Format=Delimited(|)
> MaxScanRows=0
> CharacterSet=ANSI
So you're using Jet as your CSV parser. The first thing that comes to mind
is that the default text qualifier may be a quotation mark, so root around
in the schema.ini specification to see if it's possible to explicitly
indicate that your file has NO text qualifiers.
Jerry
There is an obscure setting in the schema.ini specification. It took
quite a bit of ferreting around on Microsoft's website to find this...
TextDelimiter=none
(I was close...I was leaving it empty which didn't make sense.)
For the benefit of others that may be following...
This will only work for databases created as an Access 2000 database.
VB will report an error for Access 95. Untested for Access 97 created
databases. To make this all work...
This line was changed from...
Set dbGrant = wsULSFCC.CreateDatabase(DBName, dbLangGeneral,
dbVersion30)
to
Set dbGrant = wsULSFCC.CreateDatabase(DBName, dbLangGeneral,
dbVersion40)
The reference is changed from...
Reference=*\G{00025E04-0000-0000-C000-000000000046}#3.5#0#..\..\..\..\Program
Files\Common Files\Microsoft Shared\DAO\dao2535.tlb#Microsoft DAO
2.5/3.51 Compatibility Library
to
Reference=*\G{00025E01-0000-0000-C000-000000000046}#5.0#0#..\..\..\..\Program
Files\Common Files\Microsoft Shared\DAO\dao360.dll#Microsoft DAO 3.6
Object Library
--