Using the source filename as part of the QVD filename

[#1]

Hi,

I was wondering if it would be possible to use the source file name that is being read into QV as part of naming the QVD file?

For example, Source data file name that is being read: 'ABC Data - Aug 2012.xls'

and I want to save the QVD file that I generate as 'ABC - Aug 2012.qvd'

Is it possible to do this?

Many thanks.

Raj.

[Anonymous]

Hi,

I assume by source file you mean a location where data is being loaded from, into a QV table.

I use a QVD generator to run through a directory, loading in CSV exports thrown out by an ERP application and then saving them down to QVDs. All this is achieved essentially through loops and variables using the format:

FOR EACH TableName IN FileList (FileLocation)

$(TableName):

LOAD *

FROM [$(FileLocation)\$(TableName).txt] (txt, utf8, embedded labels, delimiter is '\t', msq);

STORE $(TableName) INTO $(TableName).qvd;

NEXT

Something like that. You have to chop up some strings on the directory name though.

Jonathan

[#3]

Thanks Jonathan.

So does that mean using the MID, RIGHT and LEFT functions to get the strings I need?

e.g. RIGHT('$(TableName)',5).qvd?

[Anonymous]

Yes, to chop out the directory name you'll need to use a Mid() or a one-two combination of Right() then Left(). You may first need a FindOneOf () though, to remove the directory path by working back from the end of your FileName looking for the last slash ('\').

Jonathan

[jonathandienst]

Hi

The easiest and most reliable way to do this is with subfield(): If you just have a filename:

     Let qvdName = Subfield('$(TableName)', '.', 1) & '.qvd';

To get the filename from the path:

     Subfield(filepath, '\', -1)

Then replace the extension xls with qvd, use subfield() again, or replace(). Putting that together:

     Let qvdName = Subfield(Subfield(filepath, '\', -1), '.', 1);

Hope that helps

Jonathan