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 Abhay,

Then as per suggestion from Jagan, you need to remove double quotes from source file and then reload.

Regards,

Raghvendra

[abhaysingh]

Hi Jonathan thanks for the revert

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,

above script giving me output like

Volkswagen India Private Limited|[250002 in partner label column.. againg not segregating by " | " sign

[abhaysingh]

I mean can't we do it without Removing double quotes manually,, or any other way to remove double quotes automatically?

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[abhaysingh]

Hi Jagan,

For my understanding , how you loading data in below format?

Data:

LOAD Replace

([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|], '"', '') AS

[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

[DummyDataNotepad.txt]

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

[abhaysingh]

hi jagan,

again same issue values are not segregating with "| " sign..

[abhaysingh]

hi Jagan,

by using

load replace ( field,'"','') as field

not working still '"', are showing in data output file..pls suggest

[jonathandienst]

You need a (delimiter is '|') in your load to separate the fields - Jagan pointed that out. I showed how to split the code from the name, which I assumed was what you were asking.

[jagan]

Hi,

It is working, please refer attached Qlikview file, if that is not you are expecting then attach the sample output you are looking for.

Regards,

Jagan.

[jonathandienst]

Use the 'no quotes' qualifier to ignore the quotes and then PurgeChar() to get rid of the quotes in the first and last fields:

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,

  ...

  String29,

  PurgeChar(String30, '"') As String30

FROM

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