Import Using Pipe as Delimiter

  • Thread starter Thread starter Bernie
  • Start date Start date
B

Bernie

I'm using Acess 2007 and need to import some text files. They are delimited
with a pipe symbol "|". I start the import wizard, tell it they are
delimited, but it won't let me enter the pipe symbol as the delimiter.

How do I import pipe delimited files?

Thanks,
Bernie
 
I'm using Acess 2007 and need to import some text files. They are
delimited with a pipe symbol "|". I start the import wizard, tell
it they are delimited, but it won't let me enter the pipe symbol
as the delimiter.

How do I import pipe delimited files?

Thanks,
Bernie
Click to expand...
For some obscure reason, the pipe character is a reserved character in
Access.
My workaround was to import the file into a 2 column table, Row as
integer and textline as text.
I then use recordset code to find the pipe values and write the values
into the destination table. This is for a daily import, so the time
creating the code was acceptable. For a one-shot import, I'd open the
file in wordpad and find and replace3 all pipe characters with tabs or
another symbol.
 
Bob,

Thanks for your quick reply. I found an interesting work around. If you tell
the wizard to use a custom charactor but don't enter a charactor and then
try to go to the next page. You will get an error dialog box saying the
charactor is missing. You can then enter the pipe charactor and proceed.

Now my problem is Access doesn't like the time/date format. The files I got
use a format like this;
05/28/1964 12:00:00 AM

Access give an error trying to convert that to a time/date. Ick!

Bernie
PS: I've got 56 of these files to import so I'm right between doing them by
hand and writing some code, hahahaha.
 
Bob,

Thanks for your quick reply. I found an interesting work around. If you tell
the wizard to use a custom charactor but don't enter a charactor and then
try to go to the next page. You will get an error dialog box saying the
charactor is missing. You can then enter the pipe charactor and proceed.

Now my problem is Access doesn't like the time/date format. The files I got
use a format like this;
05/28/1964 12:00:00 AM

Access give an error trying to convert that to a time/date. Ick!

Bernie
PS: I've got 56 of these files to import so I'm right between doing them by
hand and writing some code, hahahaha.
 
Bob,

Thanks for your quick reply. I found an interesting work around.
If you tell the wizard to use a custom charactor but don't enter a
charactor and then try to go to the next page. You will get an
error dialog box saying the charactor is missing. You can then
enter the pipe charactor and proceed.

Now my problem is Access doesn't like the time/date format. The
files I got use a format like this;
05/28/1964 12:00:00 AM

Access give an error trying to convert that to a time/date. Ick!

Bernie
PS: I've got 56 of these files to import so I'm right between
doing them by hand and writing some code, hahahaha.
Click to expand...
again, having the whole line of text in one field in a temporary
table will allow you to use the mid() function to extract the various
parts and parse them into an order that Access can digest.

otherwise, you could import it into a text field and then use vb code
to parse it into a format that Access recognizes.

note that Access has no time/date type, just date/time :-)




 
Back
Top