Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advice on import (XP)

    Hi,
    Weekly we have a export from a web application that saves the file as a comma delimited text file. The field headings are the same each week.
    We need to bring this into excel to do various trending, so I have made a spreadsheet based on the field headings which I can then bring the data into and from there all the formulas, calculations, tables and charts etc I have done.

    I would value any advice on the best way of accompishing the data import part. Manually I have done this by
    1. rename the txt to a csv
    2. open in excel
    3. copy and paste into my workbook

    I am not sure whether to create a macro using the file system object to do the txt to csv change, then automate the import and copy and aste function etc.
    OR
    whether to use the Open for #1 etc and Input method directly into the range in my workbook.
    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice on import (XP)

    Both methods should work, but theoretically renaming to .csv would be faster, since Excel uses compiled code to read the file, while a VBA routine using Open and Input is interpreted. In practice, you probably won't notice the difference. So it's a matter of what you find easiest to code.

    You can also rename the file using the old BASIC instruction Name:

    Name "C:ExcelTest.txt" As "C:ExcelTest.csv"

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice on import (XP)

    Hi, back on this one I have completed all the coding and all works fine. I am using the InputLine method after Open ... AS #1 etc.

    Just wondered if the filesystemobject.readLine method would be quicker? Proabably not. Its fine anyway, just curios really.
    Thanks,

    pmatz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice on import (XP)

    The old BASIC instructions are usually faster than their scripting equivalents (using FileSystemObjects).

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Advice on import (XP)

    Perhaps you could proceed without using any VBA coding. Under the Import External Data item you cn choose to Import Data or set up a New Database query. Which you would use would depend on your equirements, but if you simply need import a complete file the Import Data option should suffice. You can even set it up so that it automatically refreshes each time you open the file. Furthermore you would have no need to change the file extension from txt to csv, as either could be handled.

    Andrew C

  6. #6
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice on import (XP)

    Thanks for that Andrew, hadn't thought of doing it that way - although I would need to code still, as the file to be imported needs to be located (will be same directory but name may change), and also the sheet to be imported to needs to be selected by the user. Still, it is a lot faster than the Inpuline method! I will see how to automate this method and if possible use this.

    Many thanks for the tip.
    Thanks,

    pmatz

  7. #7
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice on import (XP)

    Thanks again [img]/forums/images/smilies/smile.gif[/img]).

    I have now recoded and this is more or less instant! I am passing the sheet values and textfile name to a sub which performs the import. After that I am deleting the querytable and this just leaves the data there.

    Job Done!!
    Thanks,

    pmatz

  8. #8
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice on import (XP)

    hmm, seems like i spoke to soon!! All the coding and functionality of the spreadsheet works fine, but when I close it - saving changes... it takes AGES!

    a temp file is built in the directory where the spreadsheet is , and this gradually incrreases up the the file size of the sheet. then it closes. Any ideas what could be going on here? I presume its something to do with the fact a querytable was created and deleted? O rmaybe i missed something else.
    Thanks,

    pmatz

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Advice on import (XP)

    Without seeing your set up, I cannot really tell what might be causing the problem. How many records & fields are being imported ?

    Have you set the query to automatically update every X number of minutes ?

    I am still not convinced that you need any code to do the import. You can set a query data range properties to prompt for a file name before refreshing. Given that the data can be refreshed on file open, is it necessary to save the data with the file ?

    Andrew C

  10. #10
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice on import (XP)

    It may be a network issue, gonna troubleshoot that tomo.

    I understand that i don't NEED code to use the data import, with the refresh and prompt etc... but let me explain this spreadsheet.

    It is a spreadsheet that is used for trending. Each week a report is pulled off a web based app then this data imported into a respective sheet (52 sheets in workbook). These sheets have formulas for each row of import which are fed into 5 tables which provide 5 charts where the trending of the data can be seen.

    The data therefore needs to remain in the book as each week new data is added. The user needs to be able to select a different week than the current one (which is defaulted to) in case of any issues, like wise with the files.

    This is all done through a front sheet user interface to make the workbook user friendly and simple for those that need to use it <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    anyway, at the mo the file is less than a meg and the dataquerytables and named ranges are all cleared after the import , so i think it must be a network issue (Given our serverrs, switches and routers all got turned off abruptly at the weekend by accident by some workers! - <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15> ))
    Thanks,

    pmatz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •