Notepad file to QV conversion

[abhaysingh]

Hi all,

i have one dummy data sample file which i need to automate into QV,

first raw is name of excel as output, second raws are headers(column) each header is seperated by " | " sign.

e.g

TAGFile_Extraction = outpit file name

Partner (Full Label)|Agreement (Full Label)|Scheme (Full Label)|    Headers for excel

[223] Volkswagen India Private Limited|[2500022] Volkswagen India Assistance|[250002201] Volkswagen Assistance|

i want to pull it into qv first and automate as well.

can any one suggest me this how i can do this.. the format will be standard...

[raghvendrasingh]

Hi,

Please find qvw as attachment.

In QVW you can put you file path.

Please let me know if your problem is resolved.

Regards,

Raghvendra

[jagan]

HI,

Try this script

LOAD [Partner (Full Label)],

     [Agreement (Full Label)],

     [Scheme (Full Label)],

     [Guarantee (Full Label)],

     [Service (Full Label)],

     [Cause (Full Label)],

     [Case Created By (Operator Full Name)],

     [Service Assigned By (Operator Full Name)],

     [Assignment (Creation Date)],

     [Assignment (Creation Time)],

     [Case (Creation Date)],

     [Case (Creation Time)],

     [Assignment (Reference)],

     [Assignment Status (Full Label)],

     [Expected intervention (Date)],

     [Expected intervention (Time)],

     [Real intervention beginning (Date)],

     [Real intervention beginning (Time )],

     [Real intervention end (Date)],

     [Real intervention end (Time )],

     [Case Sub Type (Full Label)],

     [Case Type (Label)],

     [Incident (Date)],

     [Incident Location (Address)],

     [Afterwards case?],

     [Case (reference)],

     [Case (Closing Date)],

     [Case (Closing Time)],

     [Case (Completed Date)],

     [Case (Completed Time)],

     [Case (Comment)],

     [Case Status (Label)],

     [Partner Case (Reference)],

     [Vehicle Brand (Label)],

     [Vehicle (Model)],

     [Vehicle (Registration)],

     [Vehicle (First Registration Date)],

     [Vehicle (Vehicle Color)],

     [Vehicle (Vehicle Mileage)],

     [Completion Duration],

     [Out of coverage?],

     [Arrival place description (garage name)],

     [Arrival Address],

     [Arrival Town (Name)],

     [Arrival Department (Name)],

     [Policy (Reference)],

     [Policy (Start Date)],

     [Policy (End Date)],

     [Incident Location (Town)],

     [Incident Location Department (Name)],

     [Service Cost Estimate Payment Amount With Tax (Foreign)],

     [Assisted (Full Name)],

     [Caller Full (Name)],

     [Caller (Phone Number)],

     [Company Caller (Name)],

     [Provider (Adress)],

     [Provider (Code)],

     [Provider (Name)],

     [Subscriber (Full Name)],

     [Purchase (Date)],

     [Mission Actual Distance Value],

     [Service Cost Estimate Payment Amount Without Tax (Foreign)],

     [Provider (Comment)],

     String1,

     String2,

     String3,

     String4,

     String5,

     String6,

     String7,

     String8,

     String9,

     String10,

     String11,

     String12,

     String13,

     String14,

     String15,

     Numeric1,

     Numeric2,

     Numeric3,

     Numeric4,

     Numeric5,

     Numeric6,

     Numeric7,

     Numeric8,

     Numeric9,

     Numeric10,

     Numeric11,

     Numeric12,

     Numeric13,

     Numeric14,

     Numeric15,

     Date1,

     Date2,

     Date3,

     Date4,

     Date5,

     Date6,

     Date7,

     Date8,

     Date9,

     Date10,

     Date11,

     Date12,

     Date13,

     Date14,

     Date15,

     Bool1,

     Bool2,

     Bool3,

     Bool4,

     Bool5,

     Bool6,

     Bool7,

     Bool8,

     Bool9,

     Bool10,

     Bool11,

     Bool12,

     Bool13,

     Bool14,

     Bool15,

     String16,

     String17,

     String18,

     String19,

     String20,

     String21,

     String22,

     String23,

     String24,

     String25,

     String26,

     String27,

     String28,

     String29,

     String30

FROM

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Regards,

jagan.

[abhaysingh]

hi, thanks,

just look at partner full label column, it is not segrgating by colon..

[abhaysingh]

Hi jagan,

thanks for revert,

i am also doing the with same script, but the problem is at [Partner (Full Label)] values.. they are not segregating

[raghvendrasingh]

Hi Abhay,

Sorry i didn't get your reply.Is that any problem in my solution.

Regards,

Raghvendra

[jagan]

Hi,

There are some double quotes in your file at the beginning of this rows, remove those quotes and reload again.

Regards,

Jagan.

[jonathandienst]

Like this, perhaps:

LOAD PurgeChar(SubField([Partner (Full Label)], ']', 1), '[') As PartnerCode

    Trim(SubField([Partner (Full Label)], ']', 2)) As PartnerLabel,

    PurgeChar(SubField([Agreement (Full Label)], ']', 1), '[') As AgreementCode

    Trim(SubField([Agreement (Full Label)], ']', 2)) As AgreementLabel,

    ...

[abhaysingh]

Hi raghvendra,

Sorry to make confusion for you,

just the open the Partner (Full Label) column and see the values of it, it is not segregating by " | " sign..

[abhaysingh]

sorry jagan i can not change anything in sample file.. this is something i have to automate.. can we do it in script or before reloading data?