Home  ›  Excel Vba Read Csv File Without Opening

Excel Vba Read Csv File Without Opening

Written By ۱۴۰۰ اسفند ۱۱, چهارشنبه Add Comment Edit

  1. 05-12-2020,06:33 AM #one

    Loop through CSV files without opening directly

    Hullo anybody
    I have about 20 CSV files and each file is above 350,000 records. I can loop through them past lawmaking and open each one and copy the headers
    But each file takes too long time to open.
    How can I get the headers (the outset row only) of these CSV files without opening them?

    < ----- Delight click the lilliputian star * adjacent to add reputation if my postal service helps you
    Visit Forum : From Here

  2. 05-12-2020,06:51 AM #2

    Re: Loop through CSV files without opening directly

    Can you attach a sample CSV file?

  3. 05-12-2020,06:57 AM #iii

    Re: Loop through CSV files without opening straight

    No problem but I will continue just some rows to be able to upload and exam

  4. 05-12-2020,07:10 AM #4

    Re: Loop through CSV files without opening straight

    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-trouble at a fourth dimension!!!
    If yous experience I have helped, please click on the star to left of mail [Add Reputation]
    Likewise....add a annotate if you lot like!!!!
    And think...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. 05-12-2020,07:14 AM #v

    Re: Loop through CSV files without opening directly

    Thank you a lot Sintek. I tried the codes but with no luck.

  6. 05-12-2020,07:sixteen AM #6

    Re: Loop through CSV files without opening straight

    Google

    VBA Line Input statement

  7. 05-12-2020,07:19 AM #7

    Re: Loop through CSV files without opening direct

    Last edited by sintek; 05-12-2020 at 07:27 AM. Reason: Adapted code

  8. 05-12-2020,07:31 AM #8

    Re: Loop through CSV files without opening directly

  9. 05-12-2020,07:33 AM #9

    Re: Loop through CSV files without opening straight

    Thanks a lot Jindo for guidance
    I have tried this
    Information technology worked well...
    I have heard almost ADO and I really need to accept a solution using information technology so as to try to larn something new. If you don't mind of form

  10. 05-12-2020,07:37 AM #10

    Re: Loop through CSV files without opening directly

    Quote Originally Posted by YasserKhalil View Post

    Thanks a lot Jindo for guidance
    I have tried this

    .....
    I have heard about ADO and I really demand to have a solution using information technology so as to try to learn something new. If you lot don't mind of course

    Solution with ADO is already posted in my message #8

    ͼ( ͡~ ͜ʖ ͡�)ͽ

    .

  11. 05-12-2020,07:38 AM #11

    Re: Loop through CSV files without opening directly

    Thanks a lot Sintek. The code of your working well besides
    Cheers very much

  12. 05-12-2020,07:40 AM #12

    Re: Loop through CSV files without opening directly

    Sorry Haluk. I didn't pay attending to your reply as I was busy with testing my code and Sintek'southward code
    I will try your code as I am interested in it more than

  13. 05-12-2020,07:44 AM #13

    Re: Loop through CSV files without opening directly

    @Haluk
    When testing I have changed the path of the CSV files only
    And I got error at this line
    Untitled.png

  14. 05-12-2020,07:46 AM #xiv

    Re: Loop through CSV files without opening directly

    If yous identify the Excel file within the folder which houses the CSV files, the lawmaking should work....

    .

  15. 05-12-2020,07:49 AM #15

    Re: Loop through CSV files without opening direct

    What changes in the code that I can exercise to keep the workbook with code out of the folder?

  16. 05-12-2020,07:57 AM #16

    Re: Loop through CSV files without opening directly

    Last edited by Haluk; 05-12-2020 at 08:58 AM.

  17. 05-12-2020,07:57 AM #17

    Re: Loop through CSV files without opening directly

    I have changed the path in the code and run a test. The lawmaking is still working since 4 minutes. It seems that takes too long fourth dimension
    I expected using ADO is faster ..

  18. 05-12-2020,07:59 AM #18

    Re: Loop through CSV files without opening directly

    I have tried the code in post #xvi besides but the same result. information technology works merely takes likewise long time
    I think this approach opening the files .. or am I wrong?

  19. 05-12-2020,08:07 AM #19

    Re: Loop through CSV files without opening directly

    No, the code works very fast .... you can bank check the animation from the link below.

    https://gifyu.com/image/nwxr

    .

  20. 05-12-2020,08:09 AM #twenty

    Re: Loop through CSV files without opening directly

    Glad I could contribute...tx for rep +

  21. 05-12-2020,08:09 AM #21

    Re: Loop through CSV files without opening straight

    Peradventure considering the sample I attached is likewise small.
    The original files about 20 files and each one has about 350,000 rows ..

  22. 05-12-2020,08:11 AM #22

    Re: Loop through CSV files without opening direct

    The code i offered is in the attached file, you can try ...

    .

    Last edited by Haluk; 05-12-2020 at 09:00 AM.

  23. 05-12-2020,08:28 AM #23

    Re: Loop through CSV files without opening direct

    There'southward no difference. This takes besides long fourth dimension too
    Thanks anyway for showingg me how to use ADO. I though it is faster just it seems I was wrong

  24. 05-12-2020,08:28 AM #24

    Re: Loop through CSV files without opening directly

    Quote Originally Posted by YasserKhalil View Post

    ... information technology works merely takes too long time ...

    And what nearly "ADODB.Stream" did y'all attempt ?

  25. 05-12-2020,08:xxx AM #25

    Re: Loop through CSV files without opening straight

    I think ADODB.Stream is similar to VBA Lin Input arroyo. Am I right?

  26. 05-12-2020,08:34 AM #26

    Re: Loop through CSV files without opening directly

    Well, the ADO will require some time in society to to connect to each file .... this is for sure. But your number of data in each file volition also consequence the speed of the lawmaking as well.

    By the way, y'all tin can remove the distinct command from the SQL argument, which is non necessary for this code. This may outcome the speed also.

    .

  27. 05-12-2020,08:38 AM #27

    Re: Loop through CSV files without opening directly

    Connectedness might remain open up.

    Open Task manager, close all Excel* in application and back ground process and so try once more.

  28. 05-12-2020,08:40 AM #28

    Re: Loop through CSV files without opening directly

    Removing "distinct" from the strSQL control make information technology a faster a footling just it is slow my friend.

  29. 05-12-2020,08:41 AM #29

    Re: Loop through CSV files without opening direct

    Quote Originally Posted by jindon View Post

    Connexion might remain open.

    Open Task manager, close all Excel* in application and back basis process then effort again.

    I have done this pace earlier and stop the task for the whole excel application earlier testing Haluk's file

  30. 05-12-2020,08:42 AM #30

    Re: Loop through CSV files without opening straight

    Quote Originally Posted by YasserKhalil View Post

    I retrieve ADODB.Stream is similar to VBA Lin Input approach. Am I correct?

    And did you lot check the task completion time for this method ?

    The best solution for you is when y'all collect all the methods and check which 1 is the fastest - you lot cull the most effective

  31. 05-12-2020,08:44 AM #31

    Re: Loop through CSV files without opening straight

    The almost fastest is my code and Sintek's code. They are both the aforementioned time

kelleherwhilest.blogspot.com

Source: https://www.excelforum.com/excel-programming-vba-macros/1315891-loop-through-csv-files-without-opening-directly.html

0 Response to "Excel Vba Read Csv File Without Opening"

ارسال یک نظر

اشتراک در: نظرات پیام (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel