5 Replies Latest reply: Mar 14, 2018 6:15 AM by Daniel Sørensen RSS

    Extracting fields from filename, removing zeroes and converting date/timestamp

    Daniel Sørensen

      I am pretty sure if this question has already been asked in some sort in other topics, but apparently, I have not gotten the information needed to solve my problem, so I am hoping someone in here can help me solve the problem, and save some time.,

       

      I want to import all files from a folder and convert the file into fields that I can use to analyze the amount and content of the files in that folder.

       

      A typical file would look like this -> 0006037356_50548136_valuereport_20170831023203_2105.csv

      I then want to extract the information from this file name so I have fields with

       

      • 0006037356 (Unique hardware no)
      • 50548136 (Unique meter no)
      • 20170831 (Date)
      • 023203 (Timestamp)

       

      Right now I am doing the extraction by using this load.

       

      LOAD 
          FileName() as Filename,
          Mid(FileName(), 1, 10) as import_elvaco_enhed,
          Mid(FileName(), 12, 8) as meter_no,
          Mid(FileName(), 33, 8) as import_date,
          Mid(FileName(), 41, 6) as import_time_stamp
      
      FROM [lib://Mobistyle/*]
      (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
      

       

      QUESTION 1:

       

      I want to do the following.

      • 0006037356 (Remove the zeroes in front of the number)
      • 50548136 (If there are zeroes in front of the number, they should be removed)
      • 20170831 (Convert this to YYYY-MM-DD)
      • 023203 (Convert this to HH:MM)
      • (Create new field containing the size (KB) of the file)
      • (If possible, have a look in the file to see if the file is blank, or with information. Maybe filesize would suit the same need?)

       

      QUESTION 2:

       

      I only want to look at the filename but for some reason when loading the data, I get all the millions of lines included in the files, and even if the data load uses the time, and displays that millions of lines have been imported,  I am not able to use any of that information?
      (Not that I need it, just think it a waste of time and storage)