Load values from Web using values from another table

[ciaran_mcgowan]

Hi all,

I have an issue where I need to extract several pages of data from websites using values from another table in the URL.

In other words, the initial extracts contains a brief overview of data for each year so that looks like this:

For i=2014 to Year(Now())

DailyRecords:
LOAD ID,
     date,
     type,
     cat
FROM
[http://sharepointwebsite.net/database/dblist.php?Year=$(i)]
(html, codepage is 1252, embedded labels, table is @1);

NEXT i

Below is the load script for one day:

DailyRecordDetails:
LOAD *
FROM 
[http://sharepointwebsite.net/database/record.php?id=20150111-B2GT]
(html, codepage is 1252, embedded labels, table is @1);

However, the id at the end of the URL needs to change to look at the page relating to each ID from the DailyRecords table. Can this be done?

[jagan]

Hi,

Check below link for sample code

Load all Excel files and all sheets in a folder

Regards,

Jagan.

[ciaran_mcgowan]

Hi Jagan,

I can't view the link:

This group is a private group.

The moderator of this group must approve membership or invite you before you can view group content or participate.

[jagan]

LET vFilePath = 'C:\';

FOR EACH file in FileList('$(vFilePath)\*.xlsx');   // Loops each excel file in the given Folder

//In order to get the file information from SQLtables command making use of the ODBC connection format

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

SheetNames:

SQLtables;  // Loads all sheet names in the Excel file.

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1  // Loops for each sheet in the Excel file.

LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

TableName:

Load * ,

  FileBaseName()as FIle,

  FileDir() as Dir,

  FileName() as File_Name,

  '$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT index

DROP TABLE SheetNames;

NEXT

[jonathandienst · Accepted solution]

Like this:

For i=2014 to Year(Now()) 

  DailyRecords: 

  LOAD ID, 

  date, 

  type, 

  cat 

  FROM [http://sharepointwebsite.net/database/dblist.php?Year=$(i)] 

  (html, codepage is 1252, embedded labels, table is @1); 

Next i 

For j = 0 To NoOfRows('DailyRecords')

  Let vID = Peek('ID', j, 'DailyRecords');

  DailyRecordDetails: 

  LOAD * 

  FROM [http://sharepointwebsite.net/database/record.php?id=$(vID)] 

  (html, codepage is 1252, embedded labels, table is @1); 

Next j

[ciaran_mcgowan]

Perfect! Thank you