Importing Text File with 856 Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A vendor provides a flat file with about 856 fields delimited with a "|".
Right or wrong, this is what we pay for.
I can bring it into Excel using one of its nice tools which recognize the
fact a worksheet is too small and allows me to move on to the next worksheet.
From there I link to each worksheet with Access.
I'm not a programmer.
However, would you give me some key words/commands to look up in order to
pull in the first 200 or so fields, then repeat with the next 200 or so?
The fields are relatively consistent.
 
PeteC said:
A vendor provides a flat file with about 856 fields delimited with a
"|". Right or wrong, this is what we pay for.
I can bring it into Excel using one of its nice tools which recognize
the fact a worksheet is too small and allows me to move on to the
next worksheet. From there I link to each worksheet with Access.
I'm not a programmer.
However, would you give me some key words/commands to look up in
order to pull in the first 200 or so fields, then repeat with the
next 200 or so?
The fields are relatively consistent.
Click to expand...

I would do this:

- open textfile in VBA
(open datei for input...)

- read each line in a variable
line input...

- make a array
split(strLine, "|")

- create nessesary count of input-tables
write data from array to tables
how needed

To say more it would be better to know the content
of the textfile

Regards Acki
 
I'm not a programmer.
Click to expand...

This sounds like it's going to need a procedural solution, but it should
be possible to get help
However, would you give me some key words/commands to look up in order
to pull in the first 200 or so fields, then repeat with the next 200
or so?
Click to expand...

Line Input # is the one I would look up first. With this many fields,
you are going to want to read in a whole line, parse out the relevant
entities and post the values into various tables.
The fields are relatively consistent.
Click to expand...

Ouch!! *Relatively* consistent? You may be on your own here: if you are
really paying for this file, you should be either (a) demanding that it
is rock-solid consistent (and clean) or (b) requesting your money back.

If you are able to provide some more detail of the input file, we may be
able to help a bit more.

Best of luck


Tim F
 
Thank you. I bought the $99 VB.net to learn something about programming.
With the words you gave me I can start playing.
The data is just a bunch of rows with little in them.
A small example would be:
one row (very abbreviated):
SEQ103024|10,10'-|OXYBISPHENARSAZINE||||||||||||||||||||||||||||||||||||
next row:
SEQ356|1,1,2,2-|TETRABROMOPENTANE|||||||||||||||||||||||||||||||||||||||
etc for 50,000 rows or so.
 
Tim, thank you. I'm not running this on but just fyi.
The data is just a bunch of rows with little in them.
A small example would be:
one row (very abbreviated):
SEQ103024|10,10'-|OXYBISPHENARSAZINE||||||||||||||||||||||||||||||||||||
next row:
SEQ356|1,1,2,2-|TETRABROMOPENTANE|||||||||||||||||||||||||||||||||||||||
etc for 50,000 rows or so.
 
PeteC said:
Thank you. I bought the $99 VB.net to learn something about programming.
With the words you gave me I can start playing.
The data is just a bunch of rows with little in them.
A small example would be:
one row (very abbreviated):
SEQ103024|10,10'-|OXYBISPHENARSAZINE||||||||||||||||||||||||||||||||||||
next row:
SEQ356|1,1,2,2-|TETRABROMOPENTANE|||||||||||||||||||||||||||||||||||||||
etc for 50,000 rows or so.
Click to expand...

Ah, Chemistry... my first love....!
 
Hi Pete,

If you download the Unix utilities from
http://unxutils.sourceforge.net/, you'll find "cut", which is a
command-line tool for extracting selected fields from a textfile like
yours into another, narrower file - which Access will be able to import.

Alternatively, if you have or can install Perl on your computer, I can
sent you a command-line tool I've written that can convert extra-wide
text files into very tall narrow ones that are easily imported into a
normalised Access structure.
 
John, if you have used cut.exe would you tell me how? I've tried without luck.
given a standard text file whose data is delimited by a pipe, |, I used
C:\cutr f=200-400 d=| c:\allna.txt
hoping f would bring in fields 200 through 400 and identifying the delimeter
as the |,
the same results occur when using -f and -d
 
Tim, thank you. I'm not running this on but just fyi.
The data is just a bunch of rows with little in them.
A small example would be:
Click to expand...

Well, at face value this looks parseable; and I guess a bit of command line
utils with or without RegExes should be able to help (see downthread).

If you don't have access to a real unix box, this page may help (if you
don't already know about it...)
<http://gnosis.cx/publish/programming/text_utils.html>

All the best


Tim F
 
Probably you'll have got the syntax sorted out from the link Tim posted.
If not, try it like this, which I've just used successfully to extract
fields 1 to 4 and 6:

C:\TEMP\Tony>cut -f1-4,6 -d"|" petepipe.txt > narrow.txt

Note that the pipe delimiter must be quoted; otherwise the Windows
command shell will interpret as a pipe instruction.

The more verbose Posix syntax would be

C:\TEMP\Tony>cut --fields=1-4,6 --delimiter="|" petepipe.txt >
narrow.txt
 
Back
Top