Importing text files 
Author Message
 Importing text files

Importing text files
Can someone point me to an adequate tutorial on importing
text (CSV) files into access 97 via code? I haven't found
one yet that I am comfortable with.


Wed, 06 Jul 2005 06:29:34 GMT  
 Importing text files
TransferText performs the import, and assuming the text file was correctly
set up in the first place, it's not difficult. The text file can be:
- delimited (each field in quotes, commas between fields, correct number of
fields each line), or
- fixed width (broken into fields by the count of characters in the line).
With either approach, the first line can optionally have the field names.

If the import file is not correctly formatted, it's generally easier to fix
it before importing. For example, if the quotes are missing, you could use
Word to replace   comma   with   quote comma quote   , and then the
paragraph breaks (^p) with
    "^p"
(The alternative is to Open the file in VBA and parse it byte-by-byte: don't
bother unless it's the only way.)

If Access needs help interpreting the data types, pretend you're doing a
manual import (File | Get External Data | Import). After selecting the file,
click the Advanced button, and create an Import Specification. Save. You can
then use this Import Spec. in the TransferText action. This is generally a
good idea where you will be importing the same layout repeatedly (e.g.
weekly).

Often it's not possible to get Access to handle the data correctly, e.g.
where the text file contains 030118 to represent 01/18/2003. For these
cases, import into a temporary table made up largely of Text fields. Then
use an Append query (Append on Query menu) to append the data to your real
table after massaging it, e.g.:
    DateSerial(Left([MessyDate],2), Mid([MessyDate],3,2),
Right([MessyDate],2)

You may need to use a series of Update queries (Update on Query menu in
query design) to parse the data in the temp table before appending to the
real table, e.g. to break a name into Surname and FirstName fields. Below
are a couple of functions to parse the n-th word or the last word from a
field.

Use a delete query to remove the records from the temp table before the next
import:
    DELETE FROM MyTempTable;

Function ParseWord(vPhrase, n As Integer) As Variant
    'Purpose:   Return the n-th word of a phrase.
    'Arguments: - vPhrase = the phrase to search.
    '           - n = the number of the word to find (1 for first...)
    'Return:    - the n-th word (string type), or Null if not found.
    'Sample Use: Separate FirstName and Surname when importing data.

    Dim sPhrase As String       ' String of vPhrase (to hack up).
    Dim iSpacePos As Integer    ' Location of Space in vPhrase.
    Dim sWord As String         ' The current word.
    Dim iWordCount As Integer   ' Word counter.

    ' Validate Parameters
    If IsNull(vPhrase) Or n < 1 Then
        ParseWord = Null
        Exit Function
    End If

    ' Locate the word in the string.
    sPhrase = Trim$(vPhrase)
    Do
        iWordCount = iWordCount + 1
        iSpacePos = InStr(sPhrase, " ")
        If iSpacePos = 0 Then
            sWord = sPhrase
        Else
            sWord = Left$(sPhrase, iSpacePos - 1)
            sPhrase = LTrim$(Mid$(sPhrase, iSpacePos + 1))
        End If
    Loop While iWordCount < n And iSpacePos > 0

    If iWordCount < n Then
        ParseWord = Null
    Else
        ParseWord = Trim$(sWord)
    End If
End Function

Function LastWord(vPhrase As Variant) As Variant
    Dim sPhrase As String       ' String of vPhrase
    Dim iSpacePos As Integer    ' Location of space in sPhrase
    Dim iPriorSpace As Integer  ' Location of previous space

    sPhrase = Trim$(Nz(vPhrase, ""))

    Do
        iSpacePos = InStr(iSpacePos + 1, sPhrase, " ")
        If iSpacePos = 0 Then Exit Do
        iPriorSpace = iSpacePos
    Loop

    If iPriorSpace = 0 Then
        LastWord = vPhrase      ' 1 word or no words.
    Else
        LastWord = Mid(sPhrase, iPriorSpace + 1)
    End If
End Function

--
Allen Browne - Microsoft MVP (Most Valuable Professional)
Allen Browne's Database And Training - Perth, Western Australia.
Tips for MS Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> Importing text files
> Can someone point me to an adequate tutorial on importing
> text (CSV) files into access 97 via code? I haven't found
> one yet that I am comfortable with.



Wed, 06 Jul 2005 11:16:28 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help - Schema file to import text file

2. "not a valid path" error when importing text file with IISAM (Was Re: Import Text file to Access with ADO (bis) )

3. importing text file part 2

4. importing text file

5. importing text files

6. replace function quirk when importing text file to Access table

7. wish to create a button to import text files

8. Import text file

9. Importing text files into Access

10. Import Text file into Access 2000 table

11. Importing text files

12. Importing Text files with VBA code

 

 
Powered by phpBB® Forum Software