Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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;
Hi E´dil,
do you have an example (like an screenshot) of your issue ?
´beck
Hi Edil,
look at this: https://www.youtube.com/watch?v=CqvINEVAr8w
i hope that resolves your issue
beck
Thanks everyone,
With all this headache I think I will just stick to adding a country field to the file.