How to read a FlatFile into a SQL Database

, ,
1

Good day,

I when I receive a flatfile which contains strings which is delimited with a “|” (pipe), between the fields. The string format is as below:-

<field1=140bytes>|<field2=3bytes>|<field3>|<field4=3bytes>|<field5=12bytes>|<field6>

sample message string contained in the file; within the curly brackets.
{

To be or not to be. To win or not to loose … |012|8890623|PLM|000000000002|20031029

Your wish is indeed my wishes. So wish on dude … |012|9876543|PLM|000000000003|20031029

This is not a test. This is the balance of your prepaid topup… |019|1234567|AML|000000000001|20031028

}

I am currently running webMethods 4.6.

I have used the flow services,

> getfile
> bytesToString

I only manage to get the string out. But unable to split the individual fields, …

Please advise.

William NG

2

If you want to split the whole string on “|” and need to get the result as a string list like this
To be or not to be. To win or not to loose …
012
8890623
PLM
000000000002
20031029
then you call pub.string.tokenize after your bytesToString flow. Set the delim as “|”

HTH

Thahir

3

Thanks for the advise on using the tokenize. But there is carriage return in which determines the next record. Getting the string list, how can I insert the individual fields into the database with the respectives fields?

rgds
William NG

4

I’m not sure this is helpfull but… we had a similar flat file and ended up creating a flatfile schema. The end of line character was the carriage return and the field seperator was the “|”.

The flat file schema didn’t have a default record type, allowed undefined data and had two record types. One for the text and another for the delimited fields. We then used the convertToValues service to parse the data passing in the file contents as a string and the schema.

5

Hi,

bouellet, are you using wM 6.0. Because i dont see flatfile schema under wM 4.6. i hope i can have an alternate advise on this matter. thanks. next time i think i better ask the users to provide us with XML documents to simplify things. unfortunately, their process has always been providing flatfile in this manner. thanks

rgds.

6

Yes… sorry I am using wM 6.0.