Store data

[vchuprina]

Hi guys,

I receive files with data in different sheet, see below name of columns in the first sheet

Model

Code

Color

Size

Sales

Price

and second

Model

Code

Color

Sales STD

Price

They have four columns with the same name.

Could you please advise how can I load data in one output.

[#2]

The best approach is talk to source of file creator why they generated the filed names differently. Try to correct field names discrepancy on their end.

Otherwise, you have manually create Field Names Mapping table & use the Rename Fields using Mapping method.

The Field Names mapping should be maintained by you manually or Look for other options how to automate the creation of Field names mapping.

[vchuprina]

Thank you for response.

In output I want to see following columns with data.

Model       Code       Color       Size       Sales       Sales STD      Price

So is this possible to put data in one output if tables have different columns number and different data in columns, for example: first sheet doesn't contain 'Size' column, but has 'Sales STD' and second sheet conversely.

[#4]

Are trying to join the these data files ?

[#5]

Please provide the sample data & desired o/p so community can help you.

[#6]

I'd recommend keeping "Code" in both tables and don't load "Model" and "Color" from one of the tables because if my guess is right, it is redundant data.

1st table:

Model

Code

Color

Size

Sales

Price

2nd table:

Model

Code

Color

Sales STD

Price

The field "Code" will link the data from both tables.

[vchuprina]

Ok. As i said before data comes in one file. Usually files have two sheets one with Last Week data (Sheet 1) and one with STD data (Sheet 2).

So I should create third table that will be contains all columns?

See my script below:

IF( WildMatch(Upper(vSheetName), '*Sheet2*') AND NOT WildMatch(vSheetName,'*WEEK*','*database*', '*_xlnm*') ) THEN

DataLW:

LOAD

FileName() as FILE_NAME,

     '$(vSheetName)' as SHEET_NAME,

     Date(SubField('$(vWKEND)',':',2),'YYYY.MM.DD') AS WEEK_ENDING_DATE,

     SubField('$(vSeason)',':',-1) AS SEASON,

     Model AS STYLE,

     Colour AS COLOR_DESC,

     Size AS SIZE,

     [Sales, pcs] AS SLS_U,

     [Price, €] AS CURRENT_PRICE

FROM

[$(vFile)]

(biff, embedded labels, header is $(vRowStart) lines, table is [$(vSheetName)$])

Where Size <> ''

;

ELSE

DataSTD:

LOAD

FileName() as FILE_NAME,

     '$(vSheetName)' as SHEET_NAME,

     Date(SubField('$(vWKEND)',':',2),'YYYY.MM.DD') AS WEEK_ENDING_DATE,

     SubField('$(vSeason)',':',-1) AS SEASON,

     Model AS STYLE,

     Colour AS COLOR_DESC,

     pcs AS SHIPMENTS_U,

     pcs1 AS STD_SLS_U,

     [€] AS CURRENT_PRICE

FROM

[$(vFile)]

(biff, embedded labels, header is $(vRowStart) lines, table is [$(vSheetName)$])

Where IsNum(€)

;

ENDIF

ENDIF

NEXT

Drop Table Temp_Tables;

NEXT

STORE ....