Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Iam currently facing a problem.Iam using the following script to extract multiple excel files
idx=1
for each subdir in dirlist(*.csv)
Load *
....from $(subdir)
idx=idx+1
next subdir
The script runs and extract all the files.The problem here is it considers only the path where the qvw file is there.
i.e the source files and the qvw file should exist in the same path. If the source files are moved to another directory it gives me the error source file not found..iam not sure from how the path takes by itself.. also my relative path is unchecked..
Hi Rathish,
May be use UNC path.
UNC stands for Universal (or Uniform or Unified) Naming Convention and is a syntax for accessing folders and files on a network of computers. The syntax is as shown:
\\<computer name>\<shared directory>\
followed by any number of directories and terminated with a directory or file name.
The computer name is always preceded by a double backward slash (\\).
In UNC, the computer name is also known as the host name.
Hi neetha,
I tried with UNC ..it did not work..but on the other hand it not raise the script error. the script ran and nothing got loaded.
Can you share the script?
Hi Ralf,
Sorry for the late reply.
sub GetCSVFIleNames(Root)
for each FoundFile in filelist( Root & '\*.csv')
FoundFile = right('$(FoundFile)', 8);
Product:
LOAD
Customer Number,
Customer Name,
Product name] as [Product Name],
[Product number] as [Product Number]
FROM
['$(FoundFile)'\file1-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
next FoundFile
for each SubDirectory in dirlist( Root & '\*' )
call GetCSVFIleNames(SubDirectory)
next SubDirectory
end sub
Call GetCSVFIleNames('H:/') ;
I think you have several small mistakes.
I don't understand what you do with FoundFile = right('$(FoundFile)', 8); and ['$(FoundFile)'\file1-'$(FoundFile)'].
Is this to deal with some file name filter?
In the LOAD statement you were missing some brackets around field names, and you had two spaces in [Product number].
The code below should work to load all csv files, but if you have some further limitations of which files to load you need to state them.
sub GetCSVFIleNames(Root)
for each FoundFile in filelist( Root & '\*.csv')
Product:
LOAD [Customer Number],
[Customer Name],
[Product name],
[Product number]
FROM '$(FoundFile)'
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
next FoundFile
for each SubDirectory in dirlist( Root & '\*' )
call GetCSVFIleNames(SubDirectory);
next SubDirectory
end sub
Call GetCSVFIleNames('C:\Temp');
Hi neetha
This Load works. but iam facing 2 problems
All my files are placed in a folders like 2014.csv /2015.csv etc.. so the csv file names are like file1-2014. so here file1-2014 needs to be into product. similarly I have file2-2014...etc
sub GetCSVFIleNames(Root)
for each FoundFile in filelist( Root & '\*.csv')
FoundFile = right('$(FoundFile)', 8); if I keep this line..it takes always files from the source where the qvw file is placed.. it will not consider the H drive which I have given below. if I remove this H drive works.
Product:
LOAD
Customer Number,
Customer Name,
Product name] as [Product Name],
[Product number] as [Product Number]
FROM
['$(FoundFile)'\file1-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
next FoundFile
for each SubDirectory in dirlist( Root & '\*' )
call GetCSVFIleNames(SubDirectory)
next SubDirectory
end sub
Call GetCSVFIleNames('H:/') ;
Second problem is
H:\2014.csv\file1-H:\2014.csv' - I getting file not found error. in actual it should be 2014.csv\file1-2014.csv.
it should not reflect H Drive in the path.. it should only display the file name
hi ralf
The reason for FoundFile = right('$(FoundFile)', 8); is iam looking folders specifically like 2014.csv and 2015.csv etc
and under each folder
for ex: 2014.csv >> contains
file1-2014.csv
file2-2014.csv
file3-2014.csv
that's the reason I have put as ['$(FoundFile)'\file1-'$(FoundFile)'] > so output will be 2014.csv\file-2014.csv
My requirement is each file is dedicated to each load
so if put just as from ['$(FoundFile)'] .it also tries to load the same file as below and gives an error field not found
Product:
LOAD
Customer Number,
Customer Name,
Product name] as [Product Name],
[Product number] as [Product Number]
FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
Customer:
LOAD
Customer Name,
First Name,
Address] as [Address]
FROM
['$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
so actually it should be as below.. so this case it is not working
LOAD
Customer Number,
Customer Name,
Product name] as [Product Name],
[Product number] as [Product Number]
FROM
['$(FoundFile)'\file1-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
Customer:
LOAD
Customer Name,
First Name,
Address] as [Address]
FROM
['$(FoundFile)'\file2-'$(FoundFile)']
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
But when you do:
FoundFile = right('$(FoundFile)', 8);
you strip away the search path, so it becomes like 2014.csv.
That means this won't work:
FROM
['$(FoundFile)'\file1-'$(FoundFile)']
You don't have the path anymore in FoundFile.
I think this is easier, using the filelist filter mask:
sub GetFiles(Root)
for each FoundFile in filelist( Root & '\file1*.csv')
Product:
LOAD [Customer Number],
[Customer Name],
[Product name],
[Product number]
FROM '$(FoundFile)'
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
next FoundFile
for each FoundFile in filelist( Root & '\file2*.csv')
Customer:
LOAD
[Customer Name],
[First Name],
[Address]
FROM '$(FoundFile)'
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);
next FoundFile
for each SubDirectory in dirlist( Root & '\*' )
call GetFiles(SubDirectory);
next SubDirectory
end sub
Call GetFiles('C:\Temp');
hi ralf, even this holds good..