Importing Data with | as the delimiter in Access 2007

[Eric]

Did Access 2007 remove the ability to import text files with extensions other
than .csv and .txt? I want to import data from a pipe delimited file that has
a .psv extension. In previous versions of Access, I could choose "All Files"
and change the delimiter to | in the import wizard, but with Access 2007, it
won't let me open the .psv file in the first place and launch the wizard.

 

[Douglas J. Steele]

How "previous"? Microsoft removed the ability to import all but a limited
number of file extensions as text years ago.

Why not use code to rename the file, import it, then rename it back?

 

[Douglas J. Steele]

How "previous"? Microsoft removed the ability to import all but a limited
number of file extensions as text years ago.

Why not use code to rename the file, import it, then rename it back?

 

[Eric]

Doug,

I assumed you could import other extensions in Access 2003. I guess I am
showing my age. Our office recently upgraded from Access 97 to 2007.

You mention using "code" to rename the file. Would you be more specific as
to what you had in mind? Currently, I manually rename the file from .psv to
..txt and import it, specifying | as the delimiter. An easy process, but
tedious and seemingly unnecessary if Microsoft would support other extensions.

 

[Eric]

Doug,

I assumed you could import other extensions in Access 2003. I guess I am
showing my age. Our office recently upgraded from Access 97 to 2007.

You mention using "code" to rename the file. Would you be more specific as
to what you had in mind? Currently, I manually rename the file from .psv to
..txt and import it, specifying | as the delimiter. An easy process, but
tedious and seemingly unnecessary if Microsoft would support other extensions.

 

[Douglas J. Steele]

To rename:

Name "C:\Folder\File.psv" As "C:\Folder\File.csv"

To make a copy:

FileCopy "C:\Folder\File.psv", "C:\Folder\File.csv"

To delete the copy:

Kill "C:\Folder\File.csv"

Alternatively, you could try the technique outlined in
http://support.microsoft.com/?id=306144

If you're still using MDB format in Access 2007, I believe it'll still be
exactly the same registry key. If you're using the new ACCDB format, it'll
be different, and unfortunately I don't have Access 2007 installed on this
machine so I can't check what it is.

 

[Douglas J. Steele]

To rename:

Name "C:\Folder\File.psv" As "C:\Folder\File.csv"

To make a copy:

FileCopy "C:\Folder\File.psv", "C:\Folder\File.csv"

To delete the copy:

Kill "C:\Folder\File.csv"

Alternatively, you could try the technique outlined in
http://support.microsoft.com/?id=306144

If you're still using MDB format in Access 2007, I believe it'll still be
exactly the same registry key. If you're using the new ACCDB format, it'll
be different, and unfortunately I don't have Access 2007 installed on this
machine so I can't check what it is.