Load EXCEL worksheet name with dot in it

[#1]

As you can see from the spreadsheet attached, the second tab name is 'TEST.B' which contains a dot in it which failed me with loading. Used following codes to load tab name and values:

directory;

For Each vFile in FileList('C:\Users\Desktop\Load_Test.xlsx')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRows('Sheets')-1

      LET vSheet = purgeChar(purgeChar(peek('TABLE_NAME', i, 'Sheets'), chr(39)), chr(36));

         LOAD '$(vSheet)' as [Tab Name], *

          From [$(vFile)]

          (ooxml, embedded labels);

     Next;

Next;

As the results, I like to see the data as following where the Tab Names should be exactly like what displayed in the spreadsheet.

Thank you in advance.

[JonnyPoole]

A helpful feature is to hit the 'debug' button rather than 'reload'. Then hit the 'step' button to go through the script line by line. Eventually you'll hit the exact line of code that its failing on. Also in the bottom right of the debugger, it tells you what the variables got set too with each line. You can use this to figure out if the code is pulling the vSheet value for Text.B correctly.  Maybe it isn't. It would be good to see if its an issue setting the variable correctly or  alternatively using that value in the subsequent load .  Either way, try and screenshot/share the results . 

[#3]

Thank you for your advise.

I modified a bit of my codes and it can now load the data. But the problem is that the tab name lost the dot '.'

The 'TEST.B' now is 'TEST#B'. I guess system somehow converted the dot to '#'.

How can I convert the # back to the dot? Or how can I prevent system converting at the fist time but keep it as it is?

Any advice would be very welcome.

Thank you.

[JonnyPoole · Accepted solution]

I saw that too.  For some reason the original code reads '.' as #.  You can replace # with .  with this modification,  but it would do that regardless if the # was from a .  or if it existed there originally. Consider it a workaround for limited use case .

      LET vSheet = replace(purgeChar(purgeChar(peek('TABLE_NAME', i, 'Sheets'), chr(39)), chr(36)),chr(35),chr(46));

[JonnyPoole]

Just so we are both on the same page i used this script...

--------------------------------------------

directory;

For Each vFile in FileList('C:\Temp\Load_Test.xlsx')

     ODBC CONNECT To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRows('Sheets')-1

      LET vSheet = replace(purgeChar(purgeChar(peek('TABLE_NAME', i, 'Sheets'), chr(39)), chr(36)),chr(35),chr(46));

         LOAD '$(vSheet)' as [Tab Name], *

          From [$(vFile)]

          (ooxml, embedded labels);

     Next;

Next;

[#6]

It works great. Thank you so much.