File import problem

[stevietm]

Hi All,

I have a bit of a problem with my script where i want to pull only 2016 data for the current month. I have a set of 4 data files that i get daily. Exp DNI_Daily_RICA_YYYYMMDD, ERZ_Daily_RICA_YYY-MMDD, TSPC_Daily_RICA_YYYYMMDD and Tropical_Daily_RICA_YYYYMMDD.

All are txt files. See below script i use to import current month and last year same month.

File i import

LET vReportMonth = text(Date(Today()-3,'YYYYMM'));

LET vReportMonth2016 = text(Date(Today()-364,'YYYYMM'));

This script is the same for my 2017 import and it runs fine. Also i use this script on the other txt files for 2016 as well and it also runs fine. Its only on this file that i get the error.

LOAD

     @5 as ICCID,

     1 as RICAS,

          alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))AS DATE_KEY,

         alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))AS RicaDate,

    Day( alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))) as Day

FROM

[..\..\..\QVData\DNIRetailDaily\DNI_DAILY_RICA_$(vReportMonth2016)*.txt]

(txt, codepage is 1252, no labels, delimiter is '|', msq);

Error i get

File not found error

Cannot open file: 'D:\QlikView\QVData\DNIRetailDaily\DNI_DAILY_RICA_201604*.txt'

System error: Filesystem::FindImpl FindFirst call: Incorrect function: "D:\QlikView\QVData\DNIRetailDaily"

Concatenate

LOAD 

     @5 as ICCID,

     1 as RICAS,

          alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))AS DATE_KEY,

         alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))AS RicaDate,

    @9 as Client,

    Day( alt(Date(Date#(@7,'YYYY-MM-DD'),'YYYY-MM-DD'),Date(Date#(@7,'YYYY/MM/DD'),'YYYY-MM-DD'))) as Day

FROM

[..\..\..\QVData\DNIRetailDaily\DNI_DAILY_RICA_201604*.txt]

(txt, codepage is 1252, no labels, delimiter is '|', msq)

Thanks

Regards

STM

[stabben23]

Hi,

if this is Your filename DNI_Daily_RICA_YYYY-MM-DD, this DNI_DAILY_RICA_201604*.txt wont match.

Maybe keep the format in Your variable:

LET vReportMonth2016 = text(Date(Today()-364,'YYYY-MM'));

[stevietm]

Sorry all file names are YYYYMMDD with no dash.

[stabben23]

Then I Guess that this filename also is a typo? DNI_Daily_RICA_YYYYMMDD

DNI_DAILY_RICA_201604*.txt

[stevietm]

Fixed but still the same problem. I dont understand the error as all 4 files are the same except that the date format in the file will differ, and still the others for 2016 works fine.

[stevietm]

Or i am just missing something small and stupid somewhere.

This is the load of one of the other files

Usage << TROPICAL_DAILY_RICA_20160429 70 950 120 Lines fetched

Usage << TROPICAL_DAILY_RICA_20160430 70 952 033 Lines fetched

[stabben23]

try:

FROM

[..\..\..\QVData\DNIRetailDaily\DNI_DAILY_RICA_'$(vReportMonth2016)'*.txt]

(txt, codepage is 1252, no labels, delimiter is '|', msq);

add ' around the variable '$(vReportMonth2016)'

[stevietm]

Nope, but thank for the help. I think i will exclude this for now.

[pieterkup]

Hi,

I know this post is a bit old. But  I have another option for you to look into - especially if the Date format of the files differ.

What you could do is to load the files in a loop.

Start by opening a loop:

for Each zFile in FileList('..\..\..\QVData\DNIRetailDaily\DNI_DAILY_RICA_*.txt')

Now you have a variable (zFile) that contains the correct filename. Then load the data with the normal script but with: FROM ['$(zFile)']  to use the variable for the filename. The whole path should be included in the variable.

End the loop with:

next zFile;

This should solve the problems with the format of dates that differs from file to file.

Regards

Pieter