replace function quirk when importing text file to Access table 
Author Message
 replace function quirk when importing text file to Access table

I have to frequently import a text file (about 120k bytes, 340 records, 50
fields) to an Access 2000 table.  The text file has several date fields all
in the YYMMDD format and all fields are delimited by the | character.  There
are many bogus dates in the text file like 000000, 888888, and 999999 which
have to replaced with 6 space characters before importing the text file to
an Access table.  So to do this, I open the text file and dump the whole
thing into a string variable, and then use the VBA Replace function to
replace the bogus dates with spaces.  Then I save the string to a new text
file and import the file.  This all works fine, but one strange thing is
that when I replace the 000000 dates with spaces, I have to do it TWICE,
because it never works on the first pass.  You'll see this when you examine
the resulting text file in a text editor.  I have no idea why.  I've tried
rearranging the code several ways, but for some reason, the 000000 strings
never get replaced on the first pass.  It works only when I perform the
replace function twice for this string.  All other strings that need to be
replaced work fine on the first pass.  Go figure...   Here's my code...  if
you want a copy of the text file, let me know.

Jeff

Private Sub import_Click()

  Dim aroster As String

  Open "c:\aroster.txt" For Input As 1
  aroster = Input(LOF(1), 1)
  Close 1

  aroster = Replace(aroster, ".", " ")
  aroster = Replace(aroster, "|888888|", "|      |")
  aroster = Replace(aroster, "|999999|", "|      |")
  aroster = Replace(aroster, "|000000|", "|      |")
  aroster = Replace(aroster, "|000000|", "|      |")

  Open "c:\aroster2.txt" For Output As 1
  Print #1, aroster
  Close 1

  SetOption "confirm record changes", False
  DoCmd.OpenTable "aroster"
  DoCmd.RunCommand acCmdSelectAllRecords
  DoCmd.RunCommand acCmdDelete
  DoCmd.TransferText acImportDelim, "aroster import specification",
"aroster", "c:\aroster2.txt"
  DoCmd.Close acTable, "aroster"
  SetOption "confirm record changes", True

End Sub



Thu, 11 Nov 2004 06:24:33 GMT  
 replace function quirk when importing text file to Access table
Jeff

Another approach might be to import the text file, as is, to a temporary
table (you can create one that has these "date" fields typed as text).

Then, create queries to parse the data to permanent tables.  You can still
use a procedure to convert if you wish, but instead of putting spaces, set
the date to null (the value of no-entry in a date/time field).

You can, if you aren't already, convert the YYMMDD text format to a true
date(/time) format, to gain the use of Access' date/time functions.

Good luck

Jeff Boyce
<Access MVP>



Thu, 11 Nov 2004 21:57:18 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Problem importing a text file to a linked table in Access 2000

2. Import Text file into Access 2000 table

3. Importing text files to an Access table

4. Importing a text file to a access table using VB6

5. Importing Text file to Access Table in Visual Basic 5.0

6. Importing a text file into an Access table

7. Import a text file to Access Table

8. How to Import text file into access table

9. Import Text file to Access Table with ADO

10. Importing text file into access table in vb5

11. Import Text file to Access Table with ADO

12. Importing a text file into different tables in a Access via ASP

 

 
Powered by phpBB® Forum Software