Extract the Missing or Mismatch Fields from Table Structure

[#1]

Hi all,

I'm trying to compare the file structure with required structure and trying to extract the missing fields and stored in .txt file.

I'm working on following script but I'm not getting the exact result. 

Can someone help me through this?

Thanks in advance

FOR Each FoundFile in filelist('$(vSource)'&'\Data_level_*.csv')

LET vFileName = subfield(FoundFile,'\',-1);
LET vReportingDate = mid('$(vFileName)', 18, 6);

[Data]:
LOAD Date,
Engine,
Code,
CodeName,
Level
FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Next FoundFile;


Let vTableName = TableName(0);
Let vNoOfColumns = NoOfFields('$(vTableName)');
Trace $(vTableName);
Let vFieldList = '';
for i = 1 to $(vNoOfColumns)
vFieldList = vFieldList&','&chr(39)&FieldName($(i),'$(vTableName)')&chr(39);
next i;
vFieldList = right((vFieldList),len(vFieldList)-1);
Trace $(vFieldList);

for i = NoOfTables() - 1 to 0 step - 1
//for i = 1 to NoOfTables()-1
Let vTableName = TableName($(i));
Let vNoOfColumns = NoOfFields('$(vTableName)');
//Next i;
Trace $(vNoOfColumns);
for j=1 to $(vNoOfColumns)
vFieldName =  FieldName($(j),'$(vTableName)');
vFieldCheck = WildMatch('$(vFieldName)',$(vFieldList));

if $(vFieldCheck) =0 then
Load
'$(vFieldName)' as MisMatch_Field,
'$(vTableName)' as MisMatch_TABLE_NAME
AutoGenerate(1);

ENDIF

Trace $(vTableName) -> $(vFieldName);

next j;

next i;

[Anil_Babu_Samineni]

I'm working on following script but I'm not getting the exact result.

May be explore this part?

[Anil_Babu_Samineni]

And this part may be you need

LET vFileName = subfield(FoundFile,'\',-1);

FoundFile should be in '$(FoundFile)'

[#4]

Yes but result won't appear. I'm trying to extract the information for mismatch fields in table.

[Anil_Babu_Samineni]

May be try this? If not, I would ask you to share sample CSV files (Atleast 2 files)

SET vSource = D:\Prod\Tranformation;

FOR Each FoundFile in filelist('$(vSource)'&'\Data_level_*.csv')

LET vFileName = subfield('$(FoundFile)','\',-1);

LET vReportingDate = mid('$(vFileName)', 18, 6);

[Data]:

LOAD Date,

Engine,

Code,

CodeName,

Level

FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Next FoundFile;

Let vTableName = TableName(0);

Let vNoOfColumns = NoOfFields('$(vTableName)');

Trace $(vTableName);

Let vFieldList = '';

for i = 1 to $(vNoOfColumns)

vFieldList = $(vFieldList) &','&chr(39)&FieldName($(i),'$(vTableName)')&chr(39);

next i;

vFieldList = right((vFieldList),len($(vFieldList))-1);

Trace $(vFieldList);

for i = NoOfTables() - 1 to 0 step - 1

Let vTableName = TableName($(i));

Let vNoOfColumns = NoOfFields('$(vTableName)');

Trace $(vNoOfColumns);

for j=1 to $(vNoOfColumns)

vFieldName =  FieldName($(j),'$(vTableName)');

vFieldCheck = WildMatch('$(vFieldName)',$(vFieldList));

if $(vFieldCheck) =0 then

Load

'$(vFieldName)' as MisMatch_Field,

'$(vTableName)' as MisMatch_TABLE_NAME

AutoGenerate(1);

END IF

Trace $(vTableName) -> $(vFieldName);

next j;

next i;

[#6]

Hi Anil,

Its not working still. can you have any solution on that ?