Table Structuring

[Anonymous]

Hello Everyone,

I have a set of Excel files (one file for each country or one country-per-sheet in some cases) with sales data by year, month, item name etc., but without a country field.

I want to connect to all these files and create visualizations including all of them.

Do I have to create a separate field called Country and write country name there or is there other way I can use?

Edil

[marcus_sommer · Accepted solution]

I think you need to create a Country field but you didn't do it manually. With file-functions like filebasename() you could read the filename from currently loaded file and with additional string-functions like subfield(), mid() and so on you could separate the Country value if there are further prefix/suffix within the filename.

Also the sheet-names could be read but for this you will need to load xls per ODBC like in this example: Loading Multiple Excel Sheets Dynamically along with file name and sheet name.

- Marcus

[lironbaram]

hi

if the country name is part of the filename

than you can use filename() in your script to add it as a new field in the table.

if the country name in in sheet level

you'll need to with the method that load excel via odbc connection

than every sheet is a table

[OmarBenSalem]

Hi Edil, I once did something similar to that; I had folder in which I had 3 excel files, each one contains the client, and its sales (sthing like this)

But each file have a name of a country : Tunisia.xls, Algeria.xls and so on; so every file was specific to a country;

What I did, is creating a new country field based on the name of the excel file :

here how it goes:

let vDir='D:\Formation\Flags examples\FlagConcat';

test:

LOAD '' as SalesMan,

     null() as Sales,

     '' as Pays

AutoGenerate 1;

LET vF=;

for each vF in filelist (vDir & '\*.xls' )

   let name= SubField('$(vF)', '\',5) ;

   let vName= '$(name)' ;

    Concatenate(test)

LOAD SalesMan,

     Sales,

     SubField('$(vName)','.' ,1) as Pays

FROM

(biff, embedded labels, table is Feuil1$);

next vF

NoConcatenate

Pays:

LOAD SalesMan,

  Sales,

     Pays

Resident test

where Sales<>Null();

DROP Table test;

[beck_bakytbek]

Hi E´dil,

do you have an example (like an screenshot) of your issue ?

´beck

[beck_bakytbek]

Hi Edil,

look at this: https://www.youtube.com/watch?v=CqvINEVAr8w

i hope that resolves your issue

beck

[Anonymous]

Thanks everyone,

With all this headache I think I will just stick to adding a country field to the file.