Database trouble -- please, please help

G

Ginger Hoffman

For the past six weeks we've been entering data for a very
large survey. We used Excel with data validation codes to
perform this function -- every field has a specific
length. The only way I know how to fill the empty fields
is with zeros. Replacing blank cells with spaces doesn't
seem to result in the correct number of columns in the
end...

My problem is that the client wants a fixed-width text
file in the end, with 26827 columns per record. I've
downloaded and purchased PUP5 so that I can combine the
8125 fields into one cell. It seems to work all right,
but I really want to deliver this data file with empty
spaces instead of filling them with zeros. That's how
they'd prefer the file, but I cannot make this work.

Is there a way that we can take records entered in columns
(because there are 8125 fields) and ensure that each cell
has a specific number of "spaces"? I've tried transposing
all this data into 40 sheets, formatting the columns to
their specific width, and then saving as a text file, but
the empty cells don't end up with the correct number of
spaces.

I am so at my wits end with this project. Somebody please
help me! A tab delimited file would be sooooo much easier
to deliver. I am certain someone here knows a way to make
this happen.

THANK YOU!
Ginger Hoffman
 
E

Earl Kiosterud

Ginger,

I've heard people have done this successfully: Use a fixed-width font in
your worksheet, like courier. Set the widths of the columns to give you the
required numbers of characters per field when saved. Save as prn (File -
Save As).

One handy way to check the resulting file is to open it with Notepad, though
Excel can open it with the text import wizard, and parse the records,
putting the fields into columns as required. You supply the character count
for each field.

It should not be necessary to force a certain number of characters in data
entry, unless the data actually consistently uses a fixed number of
characters. If the fields need to be padded with zeroes or spaces, that can
be done with formulas.

I'm not sure what you mean by 8125 fields. Generally a field is a column in
the Excel sheet. Excel has only 256 columns. How many columns in the
worksheet are you using? And I think your client wants 26287 characters per
record. If you describe your setup, give examples.

A fixed-width file has a fixed number of characters in each field (padded
with zeroes or spaces as necessary), resulting in a fixed number of total
characters in a record. There are no commas or other field delimiters.
 
G

Ginger Hoffman

Thank you, Earl!

What I mean by the 8125 fields is just that there are 8125 fields. I know Excel's 256 column limit all too well; have worked on large data entry projects before. I actually purchased QuattroPro for that very reason! Argh...

Anyhow, the fields are in columns, not rows. Each record is in a column, not a row.

As for the forumlae required so that I don't have to pad everything with zeros, please tell me how. I've messed with this for so long it's literally hurting me. Each field has a specific number of spaces required. I've formatted every cell as a custom format so that each field has either 1, 2, 3, 4, or 5 digits depending on the field. There are no more or less than that number of digits per field. But again, the fields are in columns versus rows.

I tried transposing everything into 40 worksheets, formatting the columns to be a specific width, and then saving the sheets separately, contatenating each partial record in each sheet, copying and pasting into one sheet, and then concatenating again, but that took me absolutely forever and the PUP5 combine rows utility worked sooo well.

How exactly do I save as a PRN file if I'm using Excel 2000? It would be really so much easier for me to save it as a delimited file. The fixed-width file is honestly giving me fits. Every part of my being aches over this project.

Thanks again for your help.
Ginger
 
R

RagDyer

If you're asking this specific question:

<<"Is there a way that we can take records entered in columns
(because there are 8125 fields) and ensure that each cell
has a specific number of "spaces"?">>

The answer is yes.

It would require a "helper" column with a text formula, to pad the cells
with the required spaces, and then *removing* the formula, leaving the
amended data containing cells behind.

These revised cells could then be copied "over" the original ones.

Data in A1:A8125
Total spaces required per cell - incliding data = 25

For data right justified,
Enter this in B1:
=REPT(" ",25-LEN(A1))&A1

For data left justified,
Enter this in B1:
=A1&REPT(" ",25-LEN(A1))

Select B1 and *double click* on the "fill handle" (small black square in
lower right of selected cell),
And this will copy the formula in B1, as far down column B, as there is data
in column A.

Now, while column B is *still* selected, right click in the selection and
choose "Copy".
Right click again, and choose "PasteSpecial".
Click on "Values", then <OK>.

You now have your column B, where all the selected cells contain the
specified number of *places* with spaces and data combined.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

For the past six weeks we've been entering data for a very
large survey. We used Excel with data validation codes to
perform this function -- every field has a specific
length. The only way I know how to fill the empty fields
is with zeros. Replacing blank cells with spaces doesn't
seem to result in the correct number of columns in the
end...

My problem is that the client wants a fixed-width text
file in the end, with 26827 columns per record. I've
downloaded and purchased PUP5 so that I can combine the
8125 fields into one cell. It seems to work all right,
but I really want to deliver this data file with empty
spaces instead of filling them with zeros. That's how
they'd prefer the file, but I cannot make this work.

Is there a way that we can take records entered in columns
(because there are 8125 fields) and ensure that each cell
has a specific number of "spaces"? I've tried transposing
all this data into 40 sheets, formatting the columns to
their specific width, and then saving as a text file, but
the empty cells don't end up with the correct number of
spaces.

I am so at my wits end with this project. Somebody please
help me! A tab delimited file would be sooooo much easier
to deliver. I am certain someone here knows a way to make
this happen.

THANK YOU!
Ginger Hoffman
 
D

Dave Peterson

Maybe you could just write to a text file yourself:

Option Explicit
Sub testme()

Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long

Dim myFileName As String
Dim FileNum As Long
Dim myLine As String

myFileName = "C:\test.txt"

With ActiveSheet
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

FileNum = FreeFile
Close FileNum
Open myFileName For Output As FileNum

For iCol = FirstRow To LastRow
myLine = ""
For iRow = FirstRow To LastRow
myLine = myLine & Left(.Cells(iRow, iCol).Text & Space(40), 40)
Next iRow
Print #FileNum, myLine
Next iCol

Close FileNum
End With

End Sub

I padded each cell with up to 40 spaces.

But if your data is as nice as you right--each field is formatted the way you
want it, maybe changing:

myLine = myLine & Left(.Cells(iRow, iCol).Text & Space(40), 40)
to just
myLine = myLine & .Cells(iRow, iCol).Text
or
myLine = myLine & .Cells(iRow, iCol).Text & " "



would be sufficient.

(This also uses row 1 to find the number of columns to include and column A to
find the number of rows to include.)
 
E

Earl Kiosterud

Ginger,

So your columns are records. Ah HAH! The prn file format won't help you
now, dear, since you set column widths for character counts, and your fields
aren't in columns, but rows. Alas ):

You can make a "mirror" sheet containing formulas (below) that examine the
cells of your data sheet ("Sheet1" here), and build the fixed-width fields.
Each examines its cell, and if text, right-pads with spaces up to, but not
over the character count from sheet FldLen, later. If there are more
characters than specified in FldLen, it truncates. If numeric, it left-pads
with zeroes. If a numeric cell has more characters than specified in
FldLen, it puts "~~~Err~~~". That can be changed. Conditional formatting
could flag such errors with a bold color.

You need a third sheet called "FldLen"). Put the field lengths in each row
in column A.

Here's the formula that goes in your mirror sheet:

=IF(ISTEXT(Sheet1!A1), LEFT(Sheet1!A1,FldLen!$A1) & REPT(" ", MAX(0,
FldLen!$A1 - LEN(Sheet1!A1))), REPT("0", MAX(0, FldLen!$A1 -
LEN(Sheet1!A1))) & IF(LEN(Sheet1!A1)<=FldLen!$A1, Sheet1!A1, "~~~Err~~~"))

It's a blooming nightmare. A user-defined function would be more civilized
(easier to troubleshoot and maintain). Paste the formula from here (watch
for linefeeds) into A1 of your mirror sheet and copy down and across as far
as needed.

Now you save the mirror sheet with the text write program at
www.tusher-mehta.com. Tell it nothing for field delimiter. Or maybe you
could build in a nice little program like Dave's, leaving out the padding
stuff, since the formulas do it.

I don't know if you need to transpose the rows/columns, nor if this PUP5
program can do that for you. If not, the text write program could be
modified to do that as it goes, I think.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Ginger Hoffman said:
Thank you, Earl!

What I mean by the 8125 fields is just that there are 8125 fields. I know
Click to expand...
Excel's 256 column limit all too well; have worked on large data entry
projects before. I actually purchased QuattroPro for that very reason!
Argh...
Anyhow, the fields are in columns, not rows. Each record is in a column, not a row.

As for the forumlae required so that I don't have to pad everything with
Click to expand...
zeros, please tell me how. I've messed with this for so long it's literally
hurting me. Each field has a specific number of spaces required. I've
formatted every cell as a custom format so that each field has either 1, 2,
3, 4, or 5 digits depending on the field. There are no more or less than
that number of digits per field. But again, the fields are in columns
versus rows.
I tried transposing everything into 40 worksheets, formatting the columns
Click to expand...
to be a specific width, and then saving the sheets separately, contatenating
each partial record in each sheet, copying and pasting into one sheet, and
then concatenating again, but that took me absolutely forever and the PUP5
combine rows utility worked sooo well.
How exactly do I save as a PRN file if I'm using Excel 2000? It would be
Click to expand...
really so much easier for me to save it as a delimited file. The
fixed-width file is honestly giving me fits. Every part of my being aches
over this project.
 
R

RagDyer

Thanks for the feed-back!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

RadDyer:

You are a saviour, thank you!

It's a bit of a resource hog, but it works great!

Thank you, again.
Ginger
 
Top