Automatically import several CSV's into one Excel WB

[Welshbird]

I'm sure this must be an easy thing to do if you were someone else than me, but for me - I need some help.

I produce a report from Oracle for a client, and at the moment I produce lots of CSV's - about 20 I guess (which bit is easy) and then manually copy all of the sheets into one and save as an XLS.

This is ok for one, but this report might change next year to be one workbook per region etc, so will quickly become a real pain, and actually f this were easily possible then it would add to the reporting abilities because it would just give me more time.

I just wondered if anyone knew of a way for me to automate this last bit somehow.

(I posted this in the office forum, but xlhelp kindly pointed out that here was the correct place..)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SkipVought]

Hi,

"I produce a report from Oracle for a client, and at the moment I produce lots of CSV's"

Are the CSV's produced from Oracle data?

Why not query Oracle directly from Excel sans CSV?

Skip,

_[red][/red]

 

[Welshbird]

I don't think I can link to it directly as part of the security in place - it sits on a different domain (in fact in a different country..)

But I haven't every tried - I'll look into doind that. - Thanks - good suggestion.

In some cases I have saome data form Oracle and then some from a mainframe as well, so I guess I might still need to solve this at some point though.

Thanks,





Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Ok - I can do that, but I can't see how it helps me at the moment.

It seems to be less automated that it was before. I think I still need to automate the opening of several CSV as seperate worksheets into one Excel workbook.

Thanks for the thought though.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SkipVought]

All you need is read access to the group/tables.

But short to that, you could use Data/Get External Data/Import Data for each of the CSV's to import. Import each to a separate sheet.

NOTE: Add an import QueryTable ONCE. Data/Refresh to get new data (as the CSV changes)

Then use Data/Get External Data/New Database Query... to access and join the imported data for analysis and reporting.

Skip,

_[red][/red]

 

[Welshbird]

Well the analysis is already done in Oracle, and the SQL scripts already export the tables as CSV into a given directory.

I guess to do this I would have to change that part of the process then.

The other reason for wanting to continue in the way I have done it, is that the access to the DB is limited to me (even for read-only data - some of it is sensitive) - but the combining of data into a spreadhseet for dispatch could be done by the production team rather than myself.

Maybe it is just not as easy to do that as I had hoped.

Ah well...

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SkipVought]

Did you read the follow-on regarding the IMPORT?

But short to that, you could use Data/Get External Data/Import Data for each of the CSV's to import. Import each to a separate sheet.

NOTE: Add an import QueryTable ONCE. Data/Refresh to get new data (as the CSV changes)

Then use Data/Get External Data/New Database Query... to access and join the imported data for analysis and reporting.

Skip,

_[red][/red]

 

[Welshbird]

Yeah I did - I'm not sure if I am not understanding your answer or not explaining my issue well enough,

I tried that, but I have to select each CSV seperately it seems.

But, what I need is a way to 'bulk select' csv's and open in Excel. This may grow to a larger than 20ish number of files, and this task needs to be performed by someone even less technical than me.

I guess I was hoping for a way of having a button - highlighting a set of CSV file names, and Bob being my Uncle.

Maybe it just isn't possible to do this.

The reports are often ad-hoc, so I can't have a spreadsheet that gets refreshed - often the CSV's will be one-off's but still needing to be opened in one workbook for delivery.

Appreciate your efforts though.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SkipVought]

Are all the CSV's in the same folder?

Are ALL the CSV's in the folder candidates for IMPORT?

Is the Folder Path constant?

If the answer is yes, yes, yes, then it can be a button.

But there are other issues like...

Does this happen is a brand new workbook or an existing workbook?

If existing, are all new sheets added or are sheets replaced by new data for the same CSV?

And I question the burgeoning number of CSV's. I have many application that use either databases or text files as source data, but the number of files is CONSTANT or the file FORMAT is CONSTANT by the NAME changes with a DATE suffix, like Loans_2006-12-04.txt, Loans_2006-12-11.txt. What's with the increasing number?

Skip,

_[red][/red]

 

[Welshbird]

It isn't always the same report - I work on an ad-hoc basis, so sometimes there may only be two or three csv's, and sometimes twenty or thirty.

It would always be a brand new workbook rather than updating parts of a workbook, and all CSV's would be in one folder which gets created as part of the export process, so the folder path is not constant.

The business aim of this is so that I can produce the data and the production team can quickly amalgamate all csv's into one excel workbook which then gets sent to the client.

Access to the Oracle data is hugely limited due to the nature of the data so I'm really trying to remove some workload from me to someone else to give me more time to work on more data. I can work on Oracle but don't have to time to arrange the delivery, and the team that could do that for me cannot have access to the Oracle database.

Sorry - I should have explained the business reasoning behind this right at the beginning.

So, to summarise, what I was hoping to do was find a way that the production team can start with a blank workbook, open all the csv's into seperate sheets in one (hoefully easy) hit and then be able to save the workbook, zip with encryption (and password) and deliver it on my behalf.

Is that at all possible?

I'm guessing it may involve some kind of macro or VBS or something, but we don't have a specialist in that here - my knowledge is in the data and SQL rather than this area.

Thanks again Skip. If you can point me at some way to do this I'll be hugely grateful.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[xlbo]

Hi Fee - whilst there is merit to Skip's suggestion, I think I know the situation you are facing.

This can be done quite easily but depends on a couple of factors:

1: are all files in the same folder ?
2: are all files in the same format ?
3: does the data just need to be appended or does it need to do anything "funky" ?

Skip's suggestion is good but would probably take more time given that you would need to import each to a seperate sheet..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Welshbird]

All files are in the same folder.

The files will all be CSV with comma's delimiting the fields, but will not have the same number of columns / rows or the same headings.

The data doesn't need to be appended or anthing 'funky' - I can obviously open each file individually, and I gguess what I was really hoping for was to open them en-masse into one workbook with each csv on different sheets.

I can't know how many columns or what sort of data will be included in them at this stage - it will change on a project-by-project basis.

At present I opne them individually and then right-click, move worksheet - and pick one book, then when I have moved them all I save-as an xls.

For projects that have only 2 csv outputs this is fine, but those that have lots it becomes a real pain.

I appreciate any help Geoff! I hope I have explained enough.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

I've tried importing each csv into worksheet and recording the macro, but it looks to me like I would need to know how many files and where they were before I started.

I'm guessing my VBA is really not up to much - this is not a huge suprise to me!

Ah well...

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SkipVought]

Hi,

Macro record doing a Data/Get External Data/IMPORT.

From there the only thing to do is look thru a folder or select the files in a folder and then import into a new sheet. That's where your recorded code will go but with each new filename.

Should be pretty simple.

Have a look at the FileSystem Object. That will enable you to loop thru a folder.

Skip,
[sub]
[glasses] [b][red][/red][/b]
[b][/b] [tongue][/sub]

 

[Welshbird]

Ok - Thanks - I'll have a go at that.

(I might well comeback and post some not-completely-functioning-code though!)



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SkipVought]

Feel free to do so.

Skip,

_[red][/red]

 

[HughLerwill]

Checkout my post in thread707-955487 for some csv/ QueryTable code.

HTH Hugh

 

[xlbo]

Fee - best bet depends on whether the file names remain constant or if they change each export

If they remain constant, your best bet is the Query (Data>Get External Data). If they are changing, it is probably better to use the FileOpenDialog to select a folder and then just loop through the contents of the folder - that way the file names don't really matter.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question