Importing Pipe Delimited File Problem

[Canadian_topher]

I'm setting up an access system to import a text file which is pipe delimited. The file contains purchase order information.

I have the layout of the file and have created an import spec in order to import it directly to a table which is setup with all required fields.

The problem I am having is if the file is more than one line, access will only import the first line.

I've found that I have to manually open the file in say notepad. go to the end of each line, press the Delete key and then the enter key. After going through all the lines I save the file and THEN access will import all of the lines.

The files are coming from our customer who uses and EDI system to generate the delimited files.

The system was working but now I seem to be having some problems and I have been assured by our customer that nothing has changed in how they generate the files.

I've also found that if I manually import the files using the import wizard it will also only import the first line. Even if I use my import spec while in the wizard it will only import the first line.

The only thing I have found is when opening the file in access instead of the file looking like

1
2
3

It looks like

1

2

3

Any ideas as to why this might be happening?

Thanks for any help.

 

[ReAn]

Can you post what that looks like in your data file:

eg: 1|2|3

Because that information is kinda needed...

 

[Canadian_topher]

Sure:

201457132|850|01|3040020004980||P00000695467|1|REQSIE04-LJP||20040720|||500|EA|7.77|ONE 1 TESTER|MAIN OFC|BLAH|1216 S 111 DR|CASHION|AZ|853299998|2022685000||train01@email.com||ONE 1 TESTER|1881B1||250050|3CASPT-04-Q-4444||||
201457132|850|01|3040030000999||P00000695468|1|REQSIE05-LJP||20040720|||800|EA|6.28|ONE 1 TESTER|POSTMASTER|BLAH|123 N 2ND ST|GAS CITY|IN|469339998|2022685000||train01@email.com||ONE 1 TESTER|185245||250050|3CASPT-04-Q-4444||||
201457132|850|01|3040030000049||P00000695469|1|REQSIE06-LJP||20040720|||700|EA|6.28|ONE 1 TESTER|POSTMASTER|BLAH|123 N 2ND ST|GAS CITY|IN|469339998|2022685000||train01@email.com||ONE 1 TESTER|185245||250050|3CASPT-04-Q-4444||||
201457132|850|01|3040030005641||P00000695470|1|REQSIE07-LJP||20040720|||280|EA|9.21|ONE 1 TESTER|POSTMASTER|BLAH|123 N 2ND ST|GAS CITY|IN|469339998|2022685000||train01@email.com||ONE 1 TESTER|185245||250050|3CASPT-04-Q-4444||||


each line ends at the 3CASPT-04-Q-4444||||
so if i open the file in notepad i can see 4 lines - it looks ok but something isn't working right.

i've triple checked the import specs and the table i'm importing into and i can't find a problem with either.

 

[Canadian_topher]

oh i forgot to mention this is access 97

 

[ReAn]

Well, im using A2K, and i had no trouble: check out what i got:

(i resized all the blank cols to width 0 so it would all fit (sorta))

I just followed the questions it asked me.

 

[dan-cat]

The only thing I have found is when opening the file in access instead of the file looking like

1
2
3

It looks like

1

2

3

Any ideas as to why this might be happening?

Thanks for any help.

This seems to suggest that you have a carriage return at the end of each line. This is the problem with text files - its so easy to break their structure.

Have another chat to your customer and ask whether they allow carriage returns in their records.

 

[Canadian_topher]

I have talked to the customer and they say there are no spaces or carriage returns coming from their system into the text file.

Their system is also completly automated - user puts in an order and the system does the rest.

I go to their FTP site - download the file and this is what happens when i import it.

Could there be any issues with the ftp? does it matter how the text file is downloaded?
or is there some utility that might be able to do something to the text file before i import it?
Would using a fixed width import spec work better than the delimited spec?

thanks guys.

 

[Crilen007]

FTP can change it. So I have heard, I have never had a problem myself.

Ask them to zip and email it to you, or update your office from the microsoft site.

 

[The_Doc_Man]

I've found that I have to manually open the file in say notepad. go to the end of each line, press the Delete key and then the enter key. After going through all the lines I save the file and THEN access will import all of the lines.

FTP in text mode has permission to change your file structure. It should be in the RFC for FTP, perhaps is #923 if you know how to look up RFCs.

In brief, what happens is that there are so many different text modes out there that every FTP package could never be written to handle them all. So the common theme is that files are either binary or text. That's it. No other types. In binary, you get byte for byte whatever was in the file. In text, the FTP on your host machine warns its partner that a text file is coming.

So now the sender uses the operating system's file input routines to read the file one record at a time. (NOT one disk block at a time.) It sends the record in a way that shows the expected length and the raw characters.

The receiver gets the records one at a time and writes them using ITS operating system file output routines to write each record. Here is where your <CR> character gets tacked on. See, FTP negotiates with its partner for binary or text. The problem is that it ASSUMES that the text coming from the remote program was in the default format for text files. But if it was not, FTP doesn't know that. And your partner FTP doesn't know it either. So it makes an assumption which is, of course, flat wrong in this case.

If we are talking PC to PC here, try getting the file in binary mode rather than text mode. PCs don't actually have a native text mode anyway. Each application has to handle records in its own inimitable way. BASIC and C variants each "roll their own." And let's not forget VBA, Office exporting, WordPad, NotePad, etc. If you are NOT using PC to PC, maybe my solution isn't right. But if it is PC to PC, it can't hurt to try binary transfer mode.