importing data (97sr2)

Topic

New Reply

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.

Reply | Quote

Replies

John

MS-Excel can open CSV files without having to worry about Macros* or Get External Data.

Now you see that * 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 .

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

John

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 ?

Let me know what you think.

Wassim

Reply | Quote

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)

Reply | Quote

Andrew

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

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

Reply | Quote

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 .

Reply | Quote

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”

Reply | Quote

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 problem, 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:=????).Execute 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 “C:DataYourFile.csv~”
Application.CommandBars.FindControl(ID:=23).Execute
End Sub

Reply | Quote

Jan Karel

I appreciate your expertise. That clears it up nicely for me.

Reply | Quote

Thank you all very much indeed. This thread has been
a) useful, and 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.)

Reply | Quote