Exporting large number of columns along with 1 fixed column into separ

[akshaynr]

I have a large database consisting of about 3500 columns and about 110 rows. The first column (starting at B6) contains time values ranging from .01 seconds to 10 secs (which make up the 110 rows). Each column from D to EFR (starting at Row 6) has data corresponding to the time in that row which was entered in column B. And each column refers to a separate case. So essentially I have about 3500 different sets of data (from Column D to EFR) each corresponding to a given fixed set of times (given in column B). I also have the names for each of these 3500 cases stored in the 4th row of each corresponding column (like D4 to EFR4).

What I need is to generate text files- one for each case- wherein I have the time values that were stored in B column, a tab space, and the corresponding data in say, column D. ANd I need to do this for all 3500 columns. I need a separate text file for each of the columns from D to EFR along with the time data from column B which remains a constant. I also need the name of the file to be stored with the text string that is stored in Row 4 of the corresponding column (ie D4-EFR4). All data except the text strings in row 4 are stored as numbers.

For Example: If I have times ranging from .01 sec to 10 sec (the cells only contain the numbers) covering 110 rows in Column B (Starting at B6), and I have, say numbers 1-110 in Column D (starting at D6), and the text string stored in D4 is say, CaseD, I will need it to generate a text file with the name CaseD, having the values of the times of .01 to 10 in the left (one below the other) and the data from Column D (1-110) to each corresponding time's right -after a tab space. Like below:

.01<tabspace>1
.02<tabspace>2
.
.
10<tabspace>110

Appreciate any help that I can get with VB macros.

Regards,
AKshay

 

[Bong]

I guess I'd go for brute-force.
Loop over columns 4 through 3500.
case name is row 4
open text file
loop over rows 6 through 110
string is row,column2 & tab & row,column

for c = 4 to 3500
   case=cells(4,c).value
   Open case & ".txt" For Output As #1
   for r = 6 to 110
     strOut=cells(r,2).value & chr(9) & cells(r,c).value
     print #1 strOut
   next
   close #1
next

_________________
Bob Rashkin

 

[SkipVought]

AKshay,

It's probably too late now but...

3500 columns!!!!!

You have created a monster! Talk about shooting yourself in the foot, blindfolded, with one arm tied behind your back!

You may have created a database that seems 'easy' to enter the data as you perceive it, but this kind of data structure is horrible and makes for VERY inefficient data anaylsis and reporting. You need to educate yourself in relational database principles and understand data normalization.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

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

 

[MajP]

As Skip suggested it would be better if you normalized your data into something like below. This should be very simple to do with a little code. (This assumes you have Excel 2007 with 1M+ rows because you will need 3500 * 110)

CaseName Time Data
CaseX   0.1    1
CaseX   0.2    7
CaseX   0.3    88
CaseX   0.4    5
…		
CaseX   10     4
CaseY   0.1    2
CaseY   0.2    25
…		
Case    10     22

Although Bong's approach should work fine, this would give you many more options to analyze and work with the data. Exporting would be very quick, and you can use the MS query features now.

Pseudo code
Loop through the cases
query records where casename = the case
export Time and Data column to text file no need to open and write to a text file
save name of text file = case
next case

 

[akshaynr]

Sorry guys for the extremely late reply. But thanks for the help. And just to clear out things, I did not create that database. It was given to me.