Import text file data

[Guest]

Desired function: Import space-delimited numerical information from a *.txt file. When I try to do this directly using Access importing features, it returns many errors, key violations, etc. However, when I use Excel's import features to extract the same file, it works smoothly. It seems that Excel's importer is more robust or more forgiving that Access's. The way I am currently accomplishing the data import is: 1. Import *.txt to an Excel file, and place it into a named range. 2. Switch to Access. 3. In Access, use a macro employing the TransferSpreadsheet action to import the data from the spreadsheet. 4. Close the Excel spreadsheet.
Question: Is there a way to use macros or VBA in Access to either automate the opening of the Excel file, identifying which *.txt file to import, importing to Excel, then re-importing to Access? Or, better yet, is there a way to figure out why the *.txt file direct import method causes so many problems?

 

[Joe Fallon]

The Access .txt file importer is very good.
Not sure why yu are having a problem.
I personally do not like Space delimited files.
Try replacing the Space with the | character.
(See if everything looks OK and then import that. Maybe you have more spaces
than you know!)
--
Joe Fallon
Access MVP

Desired function: Import space-delimited numerical information from a

*.txt file. When I try to do this directly using Access importing features,
it returns many errors, key violations, etc. However, when I use Excel's
import features to extract the same file, it works smoothly. It seems that
Excel's importer is more robust or more forgiving that Access's. The way I
am currently accomplishing the data import is: 1. Import *.txt to an Excel
file, and place it into a named range. 2. Switch to Access. 3. In
Access, use a macro employing the TransferSpreadsheet action to import the
data from the spreadsheet. 4. Close the Excel spreadsheet.

Question: Is there a way to use macros or VBA in Access to either

automate the opening of the Excel file, identifying which *.txt file to
import, importing to Excel, then re-importing to Access? Or, better yet, is
there a way to figure out why the *.txt file direct import method causes so
many problems?

 

[John Nurick]

Hi Mark,

One possibility - especially if the text file is coming from a
non-Windows system - is the newlines (record separators). Recent
versions of Access seem to give problems if these are anything other
than the Windows standard of CR+LF - Chr(13)&Chr(10), but Unix systems
use LF only, and Macs use CR only.