Welcome guest. Before posting on our computer help forum, you must register. Click here it's easy and free.

Author Topic: Exporting / Importing File names from https to Excel / Word  (Read 11652 times)

0 Members and 1 Guest are viewing this topic.

jaidev.anand

    Topic Starter


    Rookie

    • Experience: Familiar
    • OS: Windows 7
    Exporting / Importing File names from https to Excel / Word
    « on: November 13, 2012, 06:21:36 AM »
    Hi Experts,

    I need some assistance...

    Could you please advise if I can  Export/Import Hyperlinks from an https site / file names to excel / word / notepad via Command Line. There are over 60000 links that get added to the https daily, but I need only 500-600 of them.

    Each link / File name has a unique identifier which has a 8 digit serial number like: 36614191 (File name example: 20121008-0650-36614191-in.wav). If this is possible. Could you please advise on how it can be done..

    Any help will be much much appreciated!

    Thanks in advance!

    foxidrive



      Specialist
    • Thanked: 268
    • Experience: Experienced
    • OS: Windows 8
    Re: Exporting / Importing File names from https to Excel / Word
    « Reply #1 on: November 13, 2012, 06:44:35 AM »
    Do you have to log into this site with username and password?
    And do you want just a list of the filenames, in a text document?  A CSV file is possible too.

    If the site is accessible, can you share a link as we'd need the HTML page or file list to see how it needs to be parsed.
    Or save the HTML page in your web browser and upload it somewhere.

    jaidev.anand

      Topic Starter


      Rookie

      • Experience: Familiar
      • OS: Windows 7
      Re: Exporting / Importing File names from https to Excel / Word
      « Reply #2 on: November 13, 2012, 09:39:57 AM »
      Yes, I need to login using a user name and password.

      A CSV file with the files names would be an icing on the cake.

      Actually, it is all I need - file names that have the 8 digit unique number, as I mentioned in my initial request.

      Thanks for you efforts.
       

      foxidrive



        Specialist
      • Thanked: 268
      • Experience: Experienced
      • OS: Windows 8
      Re: Exporting / Importing File names from https to Excel / Word
      « Reply #3 on: November 13, 2012, 09:52:26 AM »
      If you can save the HTML page as an example and upload it somewhere for us, then we can write a batch file.

      jaidev.anand

        Topic Starter


        Rookie

        • Experience: Familiar
        • OS: Windows 7
        Re: Exporting / Importing File names from https to Excel / Word
        « Reply #4 on: November 13, 2012, 11:14:09 AM »
        Sure, I'll share it later today, as. I'm away at the moment.

        Thanks!

        jaidev.anand

          Topic Starter


          Rookie

          • Experience: Familiar
          • OS: Windows 7
          Re: Exporting / Importing File names from https to Excel / Word
          « Reply #5 on: November 13, 2012, 01:20:12 PM »
          Just to add on the 8 digit number will be same for atleast 2 file names, but there can be 4-6 files with the same 8 digit number.  In other words, if I need to extract 500 file names I will roughly have 200 8 digit number IDs, and these would change daily.
          Thanks!

          jaidev.anand

            Topic Starter


            Rookie

            • Experience: Familiar
            • OS: Windows 7
            Re: Exporting / Importing File names from https to Excel / Word
            « Reply #6 on: November 14, 2012, 01:59:32 AM »
            Yes, I need to login using a user name and password.

            A CSV file with the files names would be an icing on the cake.

            Actually, it is all I need - file names that have the 8 digit unique number, as I mentioned in my initial request.

            Thanks for you efforts.

            Dear Foxidrive,

            Here's a link with a snapshot: http://imgmax.com/photos/20121024135105037100766.PNG.

            Hope you could help me out by looking at this...

            Thanks,

            foxidrive



              Specialist
            • Thanked: 268
            • Experience: Experienced
            • OS: Windows 8
            Re: Exporting / Importing File names from https to Excel / Word
            « Reply #7 on: November 14, 2012, 02:10:35 AM »
            It may be a pretty straight forward task but we don't have all the information to proceed.

            A) are all the 8 digit numbers of files that you need - in a text file with one 8 digit number per line?

            B) can you go to the FILE menu and select Save page as (or similar)  in your browser window and save the information about the files on the screen - and then attach the page here.  It will be useful in testing the batch file.


            jaidev.anand

              Topic Starter


              Rookie

              • Experience: Familiar
              • OS: Windows 7
              Re: Exporting / Importing File names from https to Excel / Word
              « Reply #8 on: November 14, 2012, 02:26:58 AM »
              It may be a pretty straight forward task but we don't have all the information to proceed.

              A) are all the 8 digit numbers of files that you need - in a text file with one 8 digit number per line?

              B) can you go to the FILE menu and select Save page as (or similar)  in your browser window and save the information about the files on the screen - and then attach the page here.  It will be useful in testing the batch file.

              A) are all the 8 digit numbers of files that you need - in a text file with one 8 digit number per line? - Yes, they are in an Excel file.

              B) can you go to the FILE menu and select Save page as (or similar)  in your browser window and save the information about the files on the screen - and then attach the page here.  It will be useful in testing the batch file. - Unfortunatley, that may not be available due to some restrictions.

              Thanks

              foxidrive



                Specialist
              • Thanked: 268
              • Experience: Experienced
              • OS: Windows 8
              Re: Exporting / Importing File names from https to Excel / Word
              « Reply #9 on: November 14, 2012, 05:26:25 AM »
              When you ask someone to make you dinner but don't provide any ingredients, how do you expect them to proceed?

              With the information you have provided: this will find the files given a list of serial numbers.

              Code: [Select]
              @echo off
              del "output file.txt" 2>nul
              for /f "delims=" %%a in (listofserialnumbers.txt) do (
              find "%%a" < "list of files.txt" >>"output file.txt"
              )

              jaidev.anand

                Topic Starter


                Rookie

                • Experience: Familiar
                • OS: Windows 7
                Re: Exporting / Importing File names from https to Excel / Word
                « Reply #10 on: November 14, 2012, 05:53:08 AM »
                When you ask someone to make you dinner but don't provide any ingredients, how do you expect them to proceed?

                With the information you have provided: this will find the files given a list of serial numbers.

                Code: [Select]
                @echo off
                del "output file.txt" 2>nul
                for /f "delims=" %%a in (listofserialnumbers.txt) do (
                find "%%a" < "list of files.txt" >>"output file.txt"
                )

                Extremely sorry, but what I meant was that I don't have access to it right now. I'm waiting for my superior to come. Sorry if I was misunderstood!   

                I do understand your point, and will keep it in mind. Thanks for all your help!

                I am assuming that I can comeback if I have any questions despite the above.

                Thanks much!

                foxidrive



                  Specialist
                • Thanked: 268
                • Experience: Experienced
                • OS: Windows 8
                Re: Exporting / Importing File names from https to Excel / Word
                « Reply #11 on: November 14, 2012, 06:03:02 AM »
                Yes, I and a number of others monitor these threads.

                I assume you can export the Excel file of serial numbers to a flat text file in this format

                54378129
                33378912
                34565432
                56349809


                jaidev.anand

                  Topic Starter


                  Rookie

                  • Experience: Familiar
                  • OS: Windows 7
                  Re: Exporting / Importing File names from https to Excel / Word
                  « Reply #12 on: November 14, 2012, 06:09:35 AM »
                  Yes, I and a number of others monitor these threads.

                  I assume you can export the Excel file of serial numbers to a flat text file in this format

                  54378129
                  33378912
                  34565432
                  56349809


                  Thanks a ton!

                  Yes, I can do that quite easily.

                  One more question as I'm a complete newbie to Command Prompt.

                  Just wondering, what would my process be: Do I need to simply paste this code in Command Prompt after I do the following?

                  Step 1: Create a Text file with the serial numbers
                  Step 2: Log in to the https link


                  Please advise.

                  foxidrive



                    Specialist
                  • Thanked: 268
                  • Experience: Experienced
                  • OS: Windows 8
                  Re: Exporting / Importing File names from https to Excel / Word
                  « Reply #13 on: November 14, 2012, 06:25:59 AM »
                  Just wondering, what would my process be: Do I need to simply paste this code in Command Prompt after I do the following?

                  No, you would paste the script into a text editor and save it as a filename.bat and be careful not to use a system command as the filename.

                  Then:

                  Step 1: Create a Text file with the serial numbers as a certain filename
                  Step 2: Log in to the https link
                  Step 3: save the web page as text with the list of all the files, and save it as a certain filename.
                  Step 4: put all the files together and run the batch file, which will create a filelist of the files containing the 8 digit serial numbers.


                  jaidev.anand

                    Topic Starter


                    Rookie

                    • Experience: Familiar
                    • OS: Windows 7
                    Re: Exporting / Importing File names from https to Excel / Word
                    « Reply #14 on: November 14, 2012, 06:36:15 AM »
                    No, you would paste the script into a text editor and save it as a filename.bat and be careful not to use a system command as the filename.

                    Then:

                    Step 1: Create a Text file with the serial numbers as a certain filename
                    Step 2: Log in to the https link
                    Step 3: save the web page as text with the list of all the files, and save it as a certain filename.
                    Step 4: put all the files together and run the batch file, which will create a filelist of the files containing the 8 digit serial numbers.

                    Superb! Thank you. I'll attempt this and will seek further assistance (if required).

                    I really value your inputs and thanks for your understanding as well as paitence with this request. I'm sure it will be immensely helpful and will save a lot of time.

                    Thanks again:)



                    jaidev.anand

                      Topic Starter


                      Rookie

                      • Experience: Familiar
                      • OS: Windows 7
                      Re: Exporting / Importing File names from https to Excel / Word
                      « Reply #15 on: November 15, 2012, 12:10:21 AM »
                      No, you would paste the script into a text editor and save it as a filename.bat and be careful not to use a system command as the filename.

                      Then:

                      Step 1: Create a Text file with the serial numbers as a certain filename
                      Step 2: Log in to the https link
                      Step 3: save the web page as text with the list of all the files, and save it as a certain filename.
                      Step 4: put all the files together and run the batch file, which will create a filelist of the files containing the 8 digit serial numbers.


                      Good morning Sir,

                      Sorry to come back again! But I'm some how unable to execute Step 4. Could you please advise what is exactly meant by put all the files together and run the batch file...

                      Thanks in advance!

                      foxidrive



                        Specialist
                      • Thanked: 268
                      • Experience: Experienced
                      • OS: Windows 8
                      Re: Exporting / Importing File names from https to Excel / Word
                      « Reply #16 on: November 15, 2012, 12:46:41 AM »
                      Tell me what you have done and what error messages occur or what happens.


                      jaidev.anand

                        Topic Starter


                        Rookie

                        • Experience: Familiar
                        • OS: Windows 7
                        Re: Exporting / Importing File names from https to Excel / Word
                        « Reply #17 on: November 15, 2012, 12:59:58 AM »
                        Tell me what you have done and what error messages occur or what happens.

                        I'm sure I did something terribly incorrectly. Here's what I did...

                        Step 1: Paste the script into a text editor and saved it as a filename.bat (File name = Today)

                        @echo off
                        del "output file.txt" 2>nul
                        for /f "delims=" %%a in (listofserialnumbers.txt) do (
                        find "%%a" < "list of files.txt" >>"output file.txt"
                        )
                        Step 2: Created a Text file with the serial numbers. The file name used is "IDs"
                        Step 3: Then I logged into the https link
                        Step 4: Saved the web page as text with the list of all the files (Process: Ctrl + S -> Save Type as Text File). File Name is “Nov 14”
                        Step 5: Saved 3 Files generated from Step 1, 2 and 4 in C drive
                        Step 6: I clicked on the Script File – A CMD window opened for a fraction of second and then closed. Nothing happened… :(


                        Please advise...

                        Thanks

                        foxidrive



                          Specialist
                        • Thanked: 268
                        • Experience: Experienced
                        • OS: Windows 8
                        Re: Exporting / Importing File names from https to Excel / Word
                        « Reply #18 on: November 15, 2012, 01:10:25 AM »
                        I'm sure I did something terribly incorrectly. Here's what I did...

                        Step 1: Paste the script into a text editor and saved it as a filename.bat (File name = Today)

                        @echo off
                        del "output file.txt" 2>nul
                        for /f "delims=" %%a in (listofserialnumbers.txt) do (
                        find "%%a" < "list of files.txt" >>"output file.txt"
                        )
                        Step 2: Created a Text file with the serial numbers. The file name used is "IDs"  Filename should be listofserialnumbers.txt
                        Step 3: Then I logged into the https link
                        Step 4: Saved the web page as text with the list of all the files (Process: Ctrl + S -> Save Type as Text File). File Name is “Nov 14” Filename should be  "list of files.txt"
                        Step 5: Saved 3 Files generated from Step 1, 2 and 4 in C drive
                        Step 6: I clicked on the Script File – A CMD window opened for a fraction of second and then closed. Nothing happened… :(


                        Please advise...

                        Thanks

                        Try it with the above changes.

                        jaidev.anand

                          Topic Starter


                          Rookie

                          • Experience: Familiar
                          • OS: Windows 7
                          Re: Exporting / Importing File names from https to Excel / Word
                          « Reply #19 on: November 15, 2012, 06:38:56 AM »
                          Try it with the above changes.

                          Awesome! Thanks a ton:-)

                          This is working superbly. You are the best. Guess, I can't thank you enough. Cheers!!!

                          jaidev.anand

                            Topic Starter


                            Rookie

                            • Experience: Familiar
                            • OS: Windows 7
                            Re: Exporting / Importing File names from https to Excel / Word
                            « Reply #20 on: November 16, 2012, 05:48:23 AM »
                            Awesome! Thanks a ton:-)

                            This is working superbly. You are the best. Guess, I can't thank you enough. Cheers!!!

                            Hi Sir,

                            Just one additional thing that I noticed...The file name currently being returned is: 20121114-0704-38877796-in.wav14-Nov-2012 07:05 551K

                            Is it possible to customize the script to only return this bit 20121114-0704-38877796-in.wav?

                            Please advise. Hope it is not too much of a problem!

                            Thanks,
                            Jai


                            foxidrive



                              Specialist
                            • Thanked: 268
                            • Experience: Experienced
                            • OS: Windows 8
                            Re: Exporting / Importing File names from https to Excel / Word
                            « Reply #21 on: November 16, 2012, 06:11:23 AM »
                            In your file, is there a space between .wav and the date?

                            The reason why I asked three times for a sample of the text is so I could arrange for the filename only, and you wanted CSV format too didn't you?

                            jaidev.anand

                              Topic Starter


                              Rookie

                              • Experience: Familiar
                              • OS: Windows 7
                              Re: Exporting / Importing File names from https to Excel / Word
                              « Reply #22 on: November 16, 2012, 06:18:14 AM »
                              In your file, is there a space between .wav and the date?

                              The reason why I asked three times for a sample of the text is so I could arrange for the filename only, and you wanted CSV format too didn't you?

                              Yes Sir. The exact format is 20121114-0704-38877796-in.wav 15-Nov-2012 01:04  479K. I only need 20121114-0704-38877796-in.wav

                              Thank you!

                              foxidrive



                                Specialist
                              • Thanked: 268
                              • Experience: Experienced
                              • OS: Windows 8
                              Re: Exporting / Importing File names from https to Excel / Word
                              « Reply #23 on: November 16, 2012, 06:29:32 AM »
                              Try this:

                              Code: [Select]
                              @echo off
                              del "output file.csv" 2>nul
                              for /f "delims=" %%a in (listofserialnumbers.txt) do (
                              for /f %%b in ('find "-%%a-" ^< "list of files.txt"') do (
                              set /p =""%%b"," >>"output file.csv" <nul
                              )
                              )
                              echo done
                              pause

                              jaidev.anand

                                Topic Starter


                                Rookie

                                • Experience: Familiar
                                • OS: Windows 7
                                Re: Exporting / Importing File names from https to Excel / Word
                                « Reply #24 on: November 16, 2012, 06:42:32 AM »
                                Try this:

                                Code: [Select]
                                @echo off
                                del "output file.csv" 2>nul
                                for /f "delims=" %%a in (listofserialnumbers.txt) do (
                                for /f %%b in ('find "-%%a-" ^< "list of files.txt"') do (
                                set /p =""%%b"," >>"output file.csv" <nul
                                )
                                )
                                echo done
                                pause

                                Sorry Sir, but this isn't getting the right output. I'm still getting 20121114-0704-38877796-in.wav14-Nov-2012 as the outcome. Apart from this it is coming in Row 1 instead of 1 name in 1 row.

                                Please advise.

                                « Last Edit: November 16, 2012, 06:55:02 AM by jaidev.anand »

                                foxidrive



                                  Specialist
                                • Thanked: 268
                                • Experience: Experienced
                                • OS: Windows 8
                                Re: Exporting / Importing File names from https to Excel / Word
                                « Reply #25 on: November 16, 2012, 07:15:56 AM »
                                I asked if there was a space between .wav and the date.  You said yes.  Are you now saying no?

                                jaidev.anand

                                  Topic Starter


                                  Rookie

                                  • Experience: Familiar
                                  • OS: Windows 7
                                  Re: Exporting / Importing File names from https to Excel / Word
                                  « Reply #26 on: November 16, 2012, 07:19:48 AM »
                                  I asked if there was a space between .wav and the date.  You said yes.  Are you now saying no?

                                  Sir,

                                  There is a space between .wav and the date.

                                  Thanks!

                                  foxidrive



                                    Specialist
                                  • Thanked: 268
                                  • Experience: Experienced
                                  • OS: Windows 8
                                  Re: Exporting / Importing File names from https to Excel / Word
                                  « Reply #27 on: November 16, 2012, 07:24:55 AM »
                                  Open the file "list of files.txt" and have a good look.

                                  If you had shown me the first time I asked then we would not be frustrated and writing back and forth and back and forth about the format of the list.

                                  If there was a space then it would not be in the format you say it is in in the output file.

                                  jaidev.anand

                                    Topic Starter


                                    Rookie

                                    • Experience: Familiar
                                    • OS: Windows 7
                                    Re: Exporting / Importing File names from https to Excel / Word
                                    « Reply #28 on: November 16, 2012, 07:30:49 AM »
                                    Open the file "list of files.txt" and have a good look.

                                    If you had shown me the first time I asked then we would not be frustrated and writing back and forth and back and forth about the format of the list.

                                    If there was a space then it would not be in the format you say it is in in the output file.

                                    Okay, sorry about the trouble. Will not bother. Thanks for the help though.

                                    foxidrive



                                      Specialist
                                    • Thanked: 268
                                    • Experience: Experienced
                                    • OS: Windows 8
                                    Re: Exporting / Importing File names from https to Excel / Word
                                    « Reply #29 on: November 16, 2012, 07:34:33 AM »
                                    So you take you bat and ball and go home rather than work *with* the people that are solving your problem - for free. 


                                    jaidev.anand

                                      Topic Starter


                                      Rookie

                                      • Experience: Familiar
                                      • OS: Windows 7
                                      Re: Exporting / Importing File names from https to Excel / Word
                                      « Reply #30 on: November 16, 2012, 07:49:46 AM »
                                      So you take you bat and ball and go home rather than work *with* the people that are solving your problem - for free.

                                      Sir, I think I said I'm sorry and immensely thankful:)

                                      foxidrive



                                        Specialist
                                      • Thanked: 268
                                      • Experience: Experienced
                                      • OS: Windows 8
                                      Re: Exporting / Importing File names from https to Excel / Word
                                      « Reply #31 on: November 16, 2012, 07:54:53 AM »
                                      This will only work if all the files are .wav files.

                                      EDITED: to add a delims section


                                      Code: [Select]
                                      @echo off
                                      del "output file.txt" 2>nul
                                      for /f "delims=" %%a in (listofserialnumbers.txt) do (
                                      for /f "delims=." %%b in ('find "-%%a-" ^< "list of files.txt"') do (
                                      >>"output file.txt" echo %%b.wav
                                      )
                                      )

                                      jaidev.anand

                                        Topic Starter


                                        Rookie

                                        • Experience: Familiar
                                        • OS: Windows 7
                                        Re: Exporting / Importing File names from https to Excel / Word
                                        « Reply #32 on: November 16, 2012, 08:00:07 AM »
                                        This will only work if all the files are .wav files.

                                        EDITED: to add a delims section


                                        Code: [Select]
                                        @echo off
                                        del "output file.txt" 2>nul
                                        for /f "delims=" %%a in (listofserialnumbers.txt) do (
                                        for /f "delims=." %%b in ('find "-%%a-" ^< "list of files.txt"') do (
                                        >>"output file.txt" echo %%b.wav
                                        )
                                        )

                                        It definately did. Thanks again!