Export data to csv/excel with specified fixed width lengths

[linzig]

Hi,

I was asked to export data from a query which I did, using the output to excel feature. I was then asked if I could export the data with fixed lengths which they specified.

Is this possible?

If it is, these are the lengths they would like:

1st column - length 20
2nd column - length 255
3rd column - length 2
4th column - length 1
5th column - length 50

Thanks for any help you can give me.

 

[grtammi]

Hey linzig ... you're in luck!

I was asked to create a program which allows my company to transmit ASCII files to our banking institution - fields had to be set widths, padded with either zeros on the left side (for numeric fields) or spaces on the right side (for alphanumeric fields) - it sucked to design!

Anyways, here's the function I came up with for padding the fields.

Public Function AddSpaces(intFieldSize As Integer, strFieldData As String) As String
  '*---------------------------------------------------------------------*
  ' function written by Greg Tammi 06/20/02 for ATS                      *
  ' this function is designed to first, identify the field length of     *
  ' the alpha-numeric data, and then determine the number of spaces that *
  ' need to be added prior to the data.                                  *
  ' Note:  Error checking is done previous to this function being called.*
  '        Therefore, no error checking needed in this function.         *
  '*---------------------------------------------------------------------*
  
  On Error GoTo Err_Handler 'if error does happen to occur

  'declare function variables
  Dim intLength As Integer 'used to field data length
  Dim intAddSpace As Integer 'used in for loop to append spaces to field data
  'retrieve length of field data using the Len function
  intLength = Len(strFieldData)
  'if the length of the field data and the field size are equal, simply return
  'the value of the field data
  If intLength = intFieldSize Then
    'field lengths are equal
    AddSpaces = strFieldData
    Exit Function
  Else
    'field lengths are not equal - append field data with the appropriate
    'number of spaces.
    For intAddSpace = 1 To (intFieldSize - intLength)
      strFieldData = strFieldData & Chr$(32)
    Next
    'all spaces have been added; return the string, and exit the function
    AddSpaces = strFieldData
    Exit Function
  End If

Err_Handler_Exit:

  AddSpaces = ""
  Exit Function

Err_Handler:

  'display a message box with the error number and the error description
  'and then resume the code at the Err_Handler_Exit subroutine, which
  'will kick the user out of the function and return a null value
  MsgBox Err.Number & vbCrLf & vbCrLf & Err.Description
  Resume Err_Handler_Exit

End Function

Now, that's the function to pad spaces onto the end of the field data ... what you will need to do in code is the following (Note: I prefer simply writing to a file instead of using the export to excel function)

Public Sub ExportData()
  Dim dbs As Database, rst As Recordset
  Set dbs = CurrentDb <or database path>
  Set rst = dbs.OpenRecordset(<table name>)
  With rst
    Open <name of text file to export to> For Output As #1
    While Not .EOF
      Print #1, AddSpaces(20, <field 1 name>) & &quot;,&quot; _
       & AddSpaces(255, <field 2 name>) & &quot;,&quot; _
       & AddSpaces(2, <field 3 name>) & &quot;,&quot; _
       & AddSpaces(1, <field 4 name>) & &quot;,&quot; _
       & AddSpaces(50, <field 5 name>)
      .MoveNext
    Wend
    Close #1
  End With
  rst.Close
  dbs.Close
End Sub

... and tada!  You're all set - you can now import this data in your Excel spreadsheet. :)

HTH

Greg

[COLOR=blue][b]When you don't understand, just nod and smile![/b][/color]

 

[MichaelRed]

I found Greg Tammi's routines a bit cumbersome, and remembered some exercices I used in documenting Ms. A's Schema long away and far ago. In particular, I found the setting of the field widths for the exported data to be rather suspect, so I (of course) found an even LESS satisfying soloution - but at least I do not depend on you programmer to figure it out entirely!

Fortunately, some bits of remberance at least got the first layer of rust off of the memory bank, and only several visits to various help screens and the ObjectBrowser wended me to the below.

Public Function basExpData(MyTblName As String, MyFilName As String)

    'Michael Red    7/24/2002 to Export a table (or any recordset?) to
    'a POTF (Plain Old Text File) in Fixed width format.

    'Sample Usage.
    '? basExpData("tblTestExpFixed", "C:\MsAccess\TextExp.txt")

    'Please note this routine is offered as a simple 'snippet' of code.  Utterly devoid
    'of error trapping or other documentation.  The "Widths" of the various
    'field types are somewhat near the MAXIMUM which the data types can be expected
    'to generate.

    'A REASONABLE approach to (or enhancement of) the 'procedure' might be to
    'include a sampling (or exhaustive stats) on the (SizeOf) actual values.

    'Another enhancement could be to ACTUALLY format some of the fields to a desired
    'setting - and adjust the field sizes accordingly.
    
    'You (the USER) should also check the various 'intrinsic constants'
    'if you change the db connection (esp to ADO*).

    'For some purposes, it may be desired to pad specific field types with either leading
    'spaces or other specific characters.  Since this is NOT specified in detail, AND it
    'is a trivial exercise, I utterly and abjectly leave this as an exercise.

    'The "original" question included references to creating the a "CSV" file for
    'importing to Excel AND the generation of a fixed width file (for the same purpose?),
    'however, this doesn't make much sense to me, as excel is MUCH more accepting
    'of the CSV format than fixed width.  I also bequeath this condrum to future (if ANY)
    'perusers.

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb   '<or database path>
    Set rst = dbs.OpenRecordset(MyTblName)

    Dim IsChar As Boolean
    Dim FilNum As Integer
    Dim Idx As Integer
    Dim MyPad As Integer
    Dim MyRec As String

    FilNum = FreeFile
    Open MyFilName For Output As #FilNum

    With rst
        While Not .EOF
            Idx = 0
            While Idx < .Fields.Count
                IsChar = False
                Select Case .Fields(Idx).Type
                    Case Is = adInteger
                        MyPad = 6

                    Case Is = dbLong
                        MyPad = 15

                    Case Is = dbSingle
                        MyPad = 12

                    Case Is = dbDouble
                        MyPad = 12

                    Case Is = dbBoolean
                        MyPad = 6

                    Case Is = dbCurrency
                        MyPad = 25

                    Case Is = dbDecimal
                        MyPad = 40

                    Case Is = dbDate
                        MyPad = 35

                    Case Is = dbText
                        MyPad = rst.Fields(Idx).Size
                        IsChar = True

                End Select

                If (IsNull(.Fields(Idx))) Then
                    MyRec = MyRec & Space(MyPad)
                 Else


                    If (IsChar = True) Then
                        MyRec = MyRec & Right(Space(MyPad) & .Fields(Idx), MyPad)
                     Else
                        MyRec = MyRec & Right(Space(MyPad) & Str(.Fields(Idx)), MyPad)
                    End If
                End If

                Idx = Idx + 1

            Wend

            Print #FilNum, MyRec
            MyRec = &quot;&quot;
            .MoveNext

        Wend

    End With

    Close #FilNum
    rst.Close
    dbs.Close

End Function
[code] MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[grtammi]

Hey! It's what we're here for - pick which one suits you best and go with it ... mine assumes that your fields will always be the specified width, regardless of data type, while Michael's, equally as functional, sets width based on data type.

 

[linzig]

Hi Greg,

Thanks for your reply. I'm having trouble though in the code where I enter the field names, it errors and goes to the end of the line on the _ and says invalid character.

This is my code can you check it for me:

Public Sub ExportData()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TBL - Scheme Information&quot
With rst
Open "g:\schemeinfo.csv" For Output As #1
While Not .EOF
Print #1, AddSpaces(20, "scheme no&quot & ","_
& AddSpaces(255,"Scheme Name&quot & ","_
& AddSpaces(2,"Scheme Type Code&quot & ","_
& AddSpaces(1,"Scheme Inactive&quot & ","_
& AddSpaces(50,"Sector Code&quot
.MoveNext
Wend
Close #1
End With
rst.Close
dbs.Close
End Sub

Many thanks in advance.

 

[MichaelRed]

you need to add a space between the "," and the "_"

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[grtammi]

Ditto.

 

[linzig]

Hi guys,

The code doesn't error anymore thanks.

I've put the code behind a button on a form, when I click the button it doesn't appear to do anything.

Is this where I should have put the code, and is the file schemeinfo.csv ok to use?

Lindsey

 

[grtammi]

Hi Lindsey ...

You need to make reference to the field to send the field data, not the field name itself - sorry, my fault, I didn't make that very clear earlier.

Instead of this ...

Print #1, AddSpaces(20, "scheme no&quot & ","_
& AddSpaces(255,"Scheme Name&quot & ","_
& AddSpaces(2,"Scheme Type Code&quot & ","_
& AddSpaces(1,"Scheme Inactive&quot & ","_
& AddSpaces(50,"Sector Code&quot

Use this ...

Print #1, AddSpaces(20, Me![scheme no]) & ","_
& AddSpaces(255, Me![Scheme Name]) & ","_
& AddSpaces(2, Me![Scheme Type Code]) & ","_
& AddSpaces(1, Me![Scheme Inactive]) & ","_
& AddSpaces(50, Me![Sector Code])

Of course, the above assumes that you are calling the code from the form you are currently on.

If you need anything else Lindsey, just leave a post - I'll take the liberty of speaking for Michael here and say that him, I, or anyone else would be glad to assist you.

HTH

Greg

 

[linzig]

aaargh!!
It still doesn't work.

On my form I have the record source as TBL - Scheme Information. I have the five fields:

Scheme No
Scheme Name
Scheme Type Code
Scheme Inactive
Sector Code

I then have a button where the lovely code you sent me Greg sits, and then I have the 'addspaces' function in a module in the database.

Any idea where I'm going wrong?

Lindsey

 

[grtammi]

Wow, I'm doing a really bad job of this - I really need to start proof-reading myself ... sorry Lindsey.

You need to make reference to the name of the control holding the data ... so, for example, if the text box named txtMyTextBox has a Control Source of Scheme No, then call the AddSpaces function using AddSpaces(20, Me!txtMyTextBox)

etc etc etc ...

I hope this cleared it up for you - this should be the final installment of my confusing posts ... lol

Greg

 

[linzig]

It's me again Greg.

The name of my text boxes are the same as the name of the control source.

Maybe its the file I'm trying to send the data to. Was the file you wrote to on your c drive, what type of file was it?

Lindsey

 

[MichaelRed]

Sutrely something is sadly and badly awry. Little old Ms. A is having some troubles in her dotage with distinguishing between "field" names and the control source (especially when thsy are the same!). I routinely go through the process of prepending ["txt" | "cbo" | "cmd" | ... ] to the various control names Ms. A. assigns to controls created via ys olde drag and drop process, just to avoid this particular pitfall. This is deradfully compounded here, because hte field (and thus the control names) include the trrribly terrorizing embeded spaces, which Ms. A also stubbles over.

Somewhere in the midst of all of the above may be some a sliver of a glimmer of hope. Unfortunately, it is probably only helpful, not totally crrective.

Now, MY soloution -with some careful modification- would have none of these issues (just other ones of it's own making and choosing). Such as WHAT will happen when some poor trusting soul attempts to export something foolish - like a memo field?


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[grtammi]

Lindsey:

Can you send the db to me (in Access 97 format please? My company won't upgrade ) and I'll take a look at the form ... not much else I can from this end ..

Greg