Get external data Excel 2007 2

[Creakinator]

I have a number of csv files to combine into an excel spreadsheet. I'm doing this by hand as I only have to do this once. I pulled the data into the worksheet by "Get External Data" "From Text". I was able to pull the data from three different csv files, then the button on the ribbon was grayed out. Is there a limit to the number of external data sources that I can have connected?

Right now I am combining the csv files into one csv file before importing the csv file into excel.

Thanks.

 

[Andrzejek]

Did you pull those 3 difference csv files into 3 (default) different worksheet in Excel?
If so, if you add additional worksheet into your workbook, can you pull another csv file into it?

Have fun.

---- Andy

 

[SkipVought]

hi,

What does this mean? " I was able to pull the data from three different csv files, then the button on the ribbon was grayed out."

How did you "pull data" from these different text files? Presumably you have three different querytables (on three different sheets or where?)

WHICH button was grayed out?

If the objective is to "merge" the data from the three files, I would...
[tt]
1. Import each file into a separate sheet
2. on a fourth sheet use Get External Data (MS Query) Excel files* to query the other three sheets in a union...

select *
From [Sheet1$]
union all
select *
From [Sheet2$]
union all
select *
From [Sheet3$]

[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Creakinator]

Hi Skip,

Thanks for you suggestion. I think I described what I did by stating in my post -- I pulled the data into the worksheet by "Get External Data" "From Text". I was able to pull the data from three different csv files, then the button on the ribbon was grayed out.-- But to be more exact: I clicked the Data ribbon, clicked the "From Text" under the "Get External Data" portion of the data ribbon for three different CSV files into one worksheet. After I did this for three csv files into the same worksheet, the "From Text" button in the "Get External Data" in the "Data" ribbon was grayed out such that I couldn't click on it. When I did as you suggested and imported each into their own worksheet, the "From Text" button was not grayed out.

Is there a limit to the number of csv files that you can 'import' into one worksheet?

 

[SkipVought]

The 3 you imported into one sheet, did you import them side by side?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Creakinator]

Not sure what you mean by 'side by side'. I imported them into the same worksheet, placing each one after each other so that the one worksheet contained all of the files, one after each other.

 

[Andrzejek]

Side-by-side:

[pre]
ABC 123 [green]abc 876[/green] [blue]hgt 765[/blue]
XYZ 987 [green]xyz 654[/green] [blue]hgt 678[/blue]
[/pre]

one after each other:

[pre]
ABC 123
XYZ 987[green]
abc 876
xyz 654[/green][blue]
hgt 765
hgt 678[/blue]
[/pre]

Have fun.

---- Andy

 

[SkipVought]

The problem is that EACH IMPORT IS A SEPARATE OBJECT!

I gave you a method for importing the data into 3 separate sheets and JOINING the data inot ONE SHEET (table)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mscallisto]

When possible, I've always done what you said you were doing, that is to copy all csv files into one file then into excel.

file1.csv looks like:
aa,11
bb,22
cc,33
file2.csv looks like:
xx,11
yy,22
zz,33

to copy any number of .csv files into one .csv file you need to go to a from a DOS prompt
Start > Run > cmd

go to the folder where your files are stored:
cd \my documents\name-of-folder-where-the-csv's reside

copy all the csv's to a file named AllcsvFiles.csv
copy *.csv AllcsvFiles.csv

now open excel and get the file.

Sam

 

[SkipVought]

Why use TWO SEPARATE PROCESSES!

It can ALL be done in Excel as I have suggested.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mscallisto]

while yes your method only uses one process (worth one a star), I simply offered an alternative that required no coding and is what I like to do when I

only have to do this once

.

sam

 

[mscallisto]

I believe it's also much quicker (if you have many .csv's)

sam

 

[Creakinator]

Thanks for all of your suggestions.