Need help on parsing fixed-width file and update DB

[lizok]

Hello, i am trying to parse an uploaded file which is a fixed-width format. I have predefined length for each field and the number of fields. What would be the best approach/technique to accomplish this?

example:
ID- 11 chars
Year -4 chars
SSN-9 chars
LastName-20chars
Firstname-10 chars

the file will look like this:
P01234567892005999999999Doe Jane
P01234567882005888888888Doe John

 

[TruthInSatire]

use a database to view the file as an external table and query the db.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[ecobb]

It's time to play with the Left() and Right() functions!

For example:

<cfset MyVar = "P01234567892005999999999Doe                 Jane      ">

To get the first 11 characters as the ID:
<cfset ID = Left(MyVar, 11)>

To get the Year:
<cfset Year = Right(Left(MyVar, 15),4)>

Now, with that being said, I'm not sure how CF will handle all of the extra spaces added in around the first and last name.  So, this may or may not work:

<cfset FirstName = Right(MyVar, 10)>

Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[ecobb]

Or, do what Truth said.


Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[TruthInSatire]

lol. well, if the db can't read fixed width files or there are too many files, or uncontrolled file names i was going to go your rout so between the 2 of us i think we nailed it pretty good

good job

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[lizok]

TruthInSatire,
What do you mean by "use a database to view the file as an external table and query the db. " ?

liz

 

[TruthInSatire]

some databases often have wizards to import or view text files as a table. Access for example has that ability.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.