2 Replies Latest reply on Oct 7, 2015 1:21 PM by atranauskas

    Uploading Multiple CSV files automatically

    atranauskas

      So currently I log end user login/application use into a CSV file.

       

      The files are named in this sequence Username_YYYYMMDD_HHMM (24 hour clock) each file is generated every half hour while the user is logged in.

       

      So this should be fairly easy right? I mean a naming format that is sequential and logical should make for an easy import; and each CSV for the end user is stored in separate folders so there's no issue of having the same DateTime field overlapping.

       

      So what would be the best way to go about importing these CSVs? I mean one at a time is plan Z, but there's a large volume of these files and I'm hoping for some more automation.

       

      So far I have the table and container all set up and ready and I started to write a script to import these files. But I've hit a wall, which is I have 0 background in scripting.

       

      For the import script I have the following:

       

      InsertFile[Filters; reference; display content; Never Compress; test3::Username_*]

       

      Filters are "All Files" File type ".CSV"

       

      The Username_* is the area that references the filepath to the folder containing all the CSVs for a particular username.

       

      When I run the script it opens up the windows explorer to that file and prompts me to simply choose one.

       

      IE: All I have done is made a script that acts as an import button.

       

      What modifiers can I place in this script for it to recognize either the oldest/newest file in that folder and step through importing each one?

       

       

      Or does anyone have an example script that does generically the same thing that I can use as an example?

       

       

      Many thanks!

        • 1. Re: Uploading Multiple CSV files automatically
          user19752

          If your files have all data in it, concatenate all files into one like

          copy *.csv ..\all.csv

          (on Windows,) then import it.

           

          If you need filename for data (may be only username), you should do it each user...

          • 2. Re: Uploading Multiple CSV files automatically
            atranauskas

            Well that would solve 90% of my problem; as the filenames are the important information that I had to save as the Username_YYYYMMDD_HHMM format is what I use to show when a user is logged in for my reports. And this information doesn't populate into each csv as a field.

             

            So to fix that I used the below script to insert the file name into the first field of each csv in the folder

             

            for /r %%a in (*.csv) do (

            copy /y "%%~a" "%temp%\%%~na"

            Echo %%~na > "%%~a"

            for /f "usebackq" %%b in ("%temp%\%%~na") do Echo %%b >> "%%~a"

            del "%temp%\%%~na"

            )

             

            Then I made a second batch file with your *.csv\ all.csv idea and that worked beautifully .