Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    importing data (97sr2)

    Comments welcome, please, on the relative merits of the 'do a macro' and 'get external data' routes for inserting into a workbook several columns from another data source (saved as csv), which is updated once or twice a month. The 'get external data' route may have the advantage that it offers to copy down formulas in the 'receiving' workbook, but there are no doubt other issues, too.
    Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: importing data (97sr2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    MS-Excel can open CSV files without having to worry about Macros<font color=red>*</font color=red> or Get External Data.

    Now you see that <font color=red>*</font color=red> up there, and I have it for this reason.

    If the CSV file contains more than 65536 rows in total then a macro is nice to have to "import" the CSV file into multiple worksheets. You can still do this manually but again twice a month is way too many times to do anything manually <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>.

    So let me know if you have more than 65536 rows in the CSV file, and I'll help you with the VBA code. I have something written already, so you need to only modify it and that is it.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data (97sr2)

    Thanks v much, Wassim.
    It might help if I were clearer about the Bigger Picture:
    The source csv file is the output from a DOS accounts package, and contains 115 fields and at present about 300 records (this will grow as membership grows, but is unlikely to breach the 65,000-record for 'some time'.) Total size ~250kB, or 25kB zipped.
    The 'target' application is an xl workbook which looks at the data in about 20 of these fields (non-contiguous), which are about people, where they live and how old they are, when they joined and aspects of their finances like their savings, their loan and the repayment history of their loan.
    From this, do_it_all.xls (modest filename, eh?) does a number of useful things, like adding up total savings and loans, producing an insurance report in which members' ages are an element, looking for dodgy loan repayment history and generating suitable letters, alerting to rising - x's where the x's are watershed ages, and there are more planned, like creating charts. Another useful thing it does is work round the insistence of Bill Gates that anyone with a date of birth like 23-12-18 must have a negative age!
    So far it's about 2 MB so a bit big to transmit by email to other users, even if zipped (c. 0.5MB) And this will get worse.
    It's designed for people like cashiers who aren't particulalrly computerate.
    Therefore there are a number of 'point and shoot' macros triggered by big buttons, and hyperlinks.
    At present the most ambitious macro is the one which fetches the fields it wants from the source data, reformats that data so that dates look like dates and money looks like money, pastes it into my toy (replacing the existing data), and closes the csv file.
    My question is, is this the most sensible way of doing it, or would the 'get external data' trick in the Tools menu be more efficient?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data (97sr2)

    Okay...so maybe I'm a little bias, I've used MS-Query ("Get External Data") quite a bit and I have little experience with macros. Using the Query allows you to pull off the data any way you want using as many parameters as you want. I think it would be very useful if you are trying to get different data at different times, for example, you can use query to get all of the people over a certain age, with more than x in savings and a loan balance less than y.
    If you need to pull the same data once or twice a month to update your records the Query will work well for that too. Each time you run the query (you can set it up to run every time the workbook is opened or just when you tell it to) it will copy adjacent coloumns containing formulas you require.
    I'm sure the folks who are proficient at macros will say the same thing about their tools of choice but I find the Query to be very easy and quick to work with.

    Stats

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data (97sr2)

    Normally the standard importers are great - I'd use them in preference to a Macro.
    The times I've found that I had to write a Macro are when the data is 'difficult' and the importers are automating incorrectly for the data.

    This situation happens in several key areas

    1 where numbers need to be treated as text (e.g. partnumbers with leading zeros, or part numbers like 1234E15)

    2 where quotes are important (or not)

    3 where dd/mm/yy style dates are used (dates up to 12/mm/yy become valid dates, and anything else gets very complicated)

    Sometimes a Macro is also required for Output - especially where quotes are important. (Excel insists on only quoting where it feels it has to)

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: importing data (97sr2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> for the detailed description, here is my humble opinion:

    As long as you are using CSV files, opening these files directly into MS-Excel is the best, easiest, fastest, least hassle way of working this situation.

    GetExternalData is quite elegant when you are working with true databases, but might be an overkill in CSV cases.

    Once you get your data into MS-Excel, then your macro will format, delete, and present the data into the format you like best.

    As to sharing the data, think of the opposite. Have your application save the formatted data back into a CSV file and ship this CSV file(s) to the ultimate users. The CSV files are manageable from the size issue, and very easy to work with via e-mail.

    Yes, this will result in more VBA coding on your part, but you love it don't you <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23> ?

    Let me know what you think.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: importing data (97sr2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Andrew

    In reference to <<< <font color=blue>3 where dd/mm/yy style dates are used (dates up to 12/mm/yy become valid dates, and anything else gets very complicated)
    </font color=blue>
    >>>

    Your Regional Settings should take care of that, unless you get data from the US and then other parts of the World. This is why I prefer a standard yyyy/mm/dd and then change it via macros or Regional Settings to what the user will like to see.

    Check your settings in the Control Panel and make sure they are set properly.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data (97sr2)

    Wassim

    Yes, I just checked and they are. Better than that, I rechecked a date import and it worked fine.

    I must be working from history - my apologies to the thread <img src=/S/blush.gif border=0 alt=blush width=15 height=15>.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data (97sr2)

    Wassim

    While I may have been working from history - there are definitely issues that still surface from time to time. See the attached quote (posted today) from the Microsoft bulletin board
    I tested the Macro - and Greg is correct with his assertion.

    "This error has been around in various guises since Office
    95, and unbelievably persists in Office XP
    1. When the Regional Setting is dd/mm/yyyy (or any day-
    Month setting), and
    2. CSV's are loaded via VBA, or
    3. Date values are transferred to VBA variables
    and
    4. Transferred back to a worksheet cell

    the day and month are transposed.
    See the example below

    Values entered in cells A1:A7
    10-Jan-02
    07/11/2002
    15/11/2002
    10/01/2002
    01/11/2002
    09-Dec-02
    12-Sep-02

    A macro is run:

    Sub testDates()
    Dim varRange As Variant
    varRange = Range("A1:A7")
    Range("B1:B7") = varRange
    End Sub

    The Results are:
    01-Oct-02
    11/07/2002
    11/15/2002
    01/10/2002
    11/01/2002
    12-Sep-02
    09-Dec-02

    And Day(B3) returns #Value! surprise surprise.
    That really is a shocker!

    Greg Lesnie
    Sydney"

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data (97sr2)

    It is -as Microsoft says- "by design".

    VBA speaks US English in all it's work and uses US locale in all Excel functions called by VBA, including importing. Therefore a recorded macro of an import that worked perfect will yield problematic code, because from the UI the import speaks locale and from VBA the same, recorded, import speaks US english. It is a tremendous PITA, but one to live with. There is something to say in favour, but only if one creates utilities for more than just oneself (As I do).

    In Excel XP a number of options has been added to overcome these problems. In XP one can tell XL what local settings it is to use.

    An excellent chapter on this subject may be found in this book:

    Excel 2000/2002 VBA Programmers Reference. Stephen Bullen, John Green, Rob Bovey, Robert Rosenberg. Wrox Press.

    That being said, what can be done?

    - Use XL XP and use the locale options in your code
    - Reformat the dates so whatever locale is set, the dates come out OK because you rework them in a macro (suggested already)
    - use sendkeys and application.commandbars.FindControl(Id:=????).Exec ute to display and OK the file open dialog (this forces Excel to think it was called from the UI and XL will use locale settings):

    Sub test()
    SendKeys "CataYourFile.csv~"
    Application.CommandBars.FindControl(ID:=23).Execut e
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data (97sr2)

    Jan Karel

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> I appreciate your expertise. That clears it up nicely for me.

  12. #12
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: importing data (97sr2)

    Thank you all very much indeed. This thread has been
    a) useful, and [img]/forums/images/smilies/cool.gif[/img] interesting, especially because of the hare which I hadn't intended to start running!
    As I've got to do a rejig of my application to handle data from a different source shortly, I think I'll try it both ways, and see which my users prefer.
    Strikes me that if MS Query is not installed by default, that's an issue (but scarcely hard to rectify.)

Posting Permissions

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