Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get external data Excel 2007 2

Status
Not open for further replies.

Creakinator

Programmer
Jun 30, 2011
21
US
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.
 

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
 
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...
Code:
select *
From [Sheet1$]
union all
select *
From [Sheet2$]
union all
select *
From [Sheet3$]
[/tt]


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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?

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

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.

 
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
 
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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

 
Why use TWO SEPARATE PROCESSES!

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

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
Creakinator said:
only have to do this once
.

sam
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top