Advice Regarding Mapping This Type Of Data

OL Connect Professional DataMapper
1

I was just wondering if anyone can advise the best option to map the following data. I’m leaning towards a script in Workflow but before I go ahead I would like to see if there are other alternatives.

The data comes in two CSV files. File one like so…

203-010265;203-010265-08;MNR;D;WILLIAMS
203-010265;203-010265-09;MNR;D;WILLIAMS
203-010265;203-010265-17;MNR;D;WILLIAMS
203-010265;203-010265-18;MNR;D;WILLIAMS

The second data file…

203-010265-08;08/08/2016;62;Interest On Arrears;105.81
203-010265-08;08/08/2016;63;Monthly Service Fee;30
203-010265-09;08/08/2016;62;Interest On Arrears;185.3
203-010265-09;08/08/2016;63;Monthly Service Fee;30
203-010265-17;08/08/2016;62;Interest On Arrears;187.43
203-010265-17;08/08/2016;64;Monthly Insurance Premium;200
203-010265-17;08/08/2016;63;Monthly Service Fee;30
203-010265-18;08/08/2016;62;Interest On Arrears;96.96
203-010265-18;08/08/2016;63;Monthly Service Fee;30

The result should be…

203-010265;203-010265-08
203-010265-08;08/08/2016;62;Interest On Arrears;105.81
203-010265-08;08/08/2016;63;Monthly Service Fee;30
203-010265;203-010265-09
203-010265-09;08/08/2016;62;Interest On Arrears;185.3
203-010265-09;08/08/2016;63;Monthly Service Fee;30
203-010265;203-010265-17
203-010265-17;08/08/2016;62;Interest On Arrears;187.43
203-010265-17;08/08/2016;64;Monthly Insurance Premium;200
203-010265-17;08/08/2016;63;Monthly Service Fee;30
203-010265;203-010265-18
203-010265-18;08/08/2016;62;Interest On Arrears;96.96
203-010265-18;08/08/2016;63;Monthly Service Fee;30

Any advise would be greatly appreciated.

Regards,

S

2

Acutally, you could make it much easier on yourself by using the Database Query task in Workflow, using the ODBC Driver for CSV files. You can then generate an output CSV that you can pass on to the DataMapper.

For instance, say your first file is named Main and the Second file is named Details?, then you could specify the following custom SQL statement in the Database Query task:

SELECT * FROM [main.csv] as M left join [details.csv] as d on m.F2=d.F1

Make sure you select CSV as the output file emulation, you should get results similar to this:

"203-010265","203-010265-08","MNR","D","WILLIAMS","203-010265-08","2016-08-08","63","Monthly Service Fee","              30.0000000"
"203-010265","203-010265-08","MNR","D","WILLIAMS","203-010265-08","2016-08-08","62","Interest On Arrears","             105.8100000"
"203-010265","203-010265-09","MNR","D","WILLIAMS","203-010265-09","2016-08-08","63","Monthly Service Fee","              30.0000000"
"203-010265","203-010265-09","MNR","D","WILLIAMS","203-010265-09","2016-08-08","62","Interest On Arrears","             185.3000000"
"203-010265","203-010265-17","MNR","D","WILLIAMS","203-010265-17","2016-08-08","63","Monthly Service Fee","              30.0000000"
"203-010265","203-010265-17","MNR","D","WILLIAMS","203-010265-17","2016-08-08","64","Monthly Insurance Premium","             200.0000000"
"203-010265","203-010265-17","MNR","D","WILLIAMS","203-010265-17","2016-08-08","62","Interest On Arrears","             187.4300000"
"203-010265","203-010265-18","MNR","D","WILLIAMS","203-010265-18","2016-08-08","63","Monthly Service Fee","              30.0000000"
"203-010265","203-010265-18","MNR","D","WILLIAMS","203-010265-18","2016-08-08","62","Interest On Arrears","              96.9600000"
3

Hi Phil,

Thanks again for assisting. I tried what you said and keep getting a error. I must be doing something wrong.

My database connection string is…

DSN=MSTextDriver;DBQ=C:\SPOOL;DefaultDir=C:\SPOOL;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;

Dynamic SQL statement modified like so…

SELECT * FROM [Statement.csv] as M left join [Trans.csv] as d on m.F2=d.F1

The error…

W3612 : Error running SQL: [Microsoft][ODBC Text Driver] Too few parameters. Expected 2
Database Query: W1603 : Plugin failed - 15:46:30 (elapsed time: 00:00:00:135)

Should I be using the dynamic tab?

Thanks,

S

4

Looks right to me, I just tested it here again and it works fine (using the samples you provided).

Your files are apparently located in your C:\SPOOL folder. Navigate to that folder and there should be a file named Schema.ini (where the ODBC DSN settings are stored). It should contain something like:

[statement.csv]
ColNameHeader=False
Format=Delimited(;)
MaxScanRows=25
CharacterSet=ANSI
[trans.csv]
ColNameHeader=False
Format=Delimited(;)
MaxScanRows=25
CharacterSet=ANSI

If your settings are different, then you can try adjusting them by hand to match the above (make a backup copy of the Schema file before doing that, though).

Otherwise, it could simply be that the samples you provided do not accurately reflect the contents of your actual files. Try copy and pasting only the samples above and saving them to files, in the same location, then retry the operation I initially described (that’s what I did, and it works).

5

Hi Phil,

Over the weekend I tried everything and your select statement is still causing the same error. Even in SQLFiddle. I even tried a simple select statement and it worked fine. Workflow did not create the schema file you mentioned so I copied and pasted yours and added it to the spool folder. I also copied my sample data above and created sample data as you suggested. The only difference in the actual data is more personal details in the first file on the same line.

203-010265;203-010265-08;MNR;D;WILLIAMS;more;more;more;more;more;etc

Can you send me your Workflow process and I can try yours?

6

Workflow does not create that Schema file, it only uses it. It’s your ODBC application that creates it.

Since Workflow is a 32 bit application, go to Control Panel | Administrative Tools | ODBC Data Source (32 bits). Then create a system DSN that uses the CSV driver and points to your spool folder. Once you’ve done this, the Schema.INI file will be stored in that folder.

And then you will be able to use the database Query task, or any other application that can use an ODBC DSN.

If you still can’t make it work, open a call with our Support team, they will get you going in no time.

7

Hi Phil,

I was locked out of the site for a few days until my account here was fixed. I tried your latest advice and still get the same error. I have opened a support ticket. Thanks for your help.

Regards,

S