MS-Access help

A follow-on from this thread.

In a nutshell:
[ul][li]Data is in Excel format[/li][li]Data needs to be in fixed positions in a .txt file[/li][li]Excel does this by Saving As Formatted text (Space delimited)[/li][li]There is a 240-character limit when saving this way[/li][li]Records tend to be more than 240 characters long[/li][li]MS-Access will give me a text file with data in fixed positions that is more than 240 bytes per record[/ul][/li]Now here are the problems:

As I said in the other thread, it would be helpful if numeric fields were… well, numeric. That is, I want a ten-byte field (say) that is right-justified and zero-filled. So instead of ‘12345[space][space][space][space][space]’ I want ‘0000012345’. There are a couple of posts in the other thread, but I have no idea how to use the information. For example, one says 'UPDATE table SET column = RIGHT(“0000000000”+10). That looks straightforward enough, only where do I type that stuff? Another suggestion uses some sort of function. (C++? Visual Basic? Java? I don’t know OO.) That’s beyond me.

And there’s another problem: I’m only using Access because it seems to be the only program that will let me save an Excel file as formatted text of a length that I need. I don’t know Access. Earlier I tried importing a file. It imported. Aside from the justification and zero-filling issue mentioned above, the dates now included time stamps. (Where did the times come from? Who can say? They’re not in the original data.) I managed to get them into MM/DD/YYYY format without time stamps. Only when I exported to text, they were M/D/YYYY and the time stamps were back.

As I said in the other thread, I can work with what I have. But it would be much nicer if the numeric fields were right justified and zero-filled (or at least right-justified) and if the dates would stay in MM/DD/YYYY format when I export to a text file.

And since I don’t know Access or OO, I’ll need step-by-step instructions.

I know nothing about Access, but have you looked into SQL Server Integration Services?

A macro in Excel can write to text files with no limitations in line length.

  1. Format the cells to include leading zeros: Select the cells, choose Format, Cells, Number Tab, Custom Category, Type “0000000000”

  2. Enter the following Macro shown below in the code window. This is done by pressing Alt-F11 to get into the VBA Editor, choosing “This Workbook” in the project Explorer pane, and pasting the macro code into the code pane.

  3. Select the range that you want to output

  4. Run the macro: press Alt-F8 and choose “ThisWorkbook.ExportText” and click Run



Sub ExportText()

Dim S As Range
Dim R As Long, C As Integer
Dim OutFileName As String, LineOut As String

OutFileName = "C:\Temp\Test.txt"
Open OutFileName For Output As #1
Set S = Selection
For R = 1 To S.Rows.Count
    LineOut = ""
    For C = 1 To S.Columns.Count
        LineOut = LineOut & S.Cells(R, C).Text & " "
    Next C
    Print #1, LineOut
Next R
Close #1

End Sub


Note that the output file name is hard-coded in the macro. This can be made more fancy if you want.

By ‘Select the range’, I assume you mean to change Dim S As Range to Dim S as 350 (picking a number of characters greater than the longest record)? That’s what I did, as I don’t see ‘Range’ anywhere else on the page.

I pressed ALT+F8 and it didn’t do anything.

Wait… I just looked again and there was a compile error and the Dim S line is red. Changed 350 back to Range and hit ALT+F8 again. It doesn’t appear to have done anything.

I don’t see anywhere where it says ‘ThisWorkbook.ExportText’.

Still haven’t found anything about ‘Range’, but I did find the Run button. If I highlight just the number fields and run the macro, only those columns are written out and they are left-justified so that the numbers aren’t in columns. If I highlight everything, then nothing is in columns.

I haven’t tried the Access query from the other thread, as I need step-by-step instructions on how to make it.

In the meantime I guess I’ll just parse the number fields byte-by-byte in the Easytrieve. It’s a pain, but I have some code for that somewhere.

Johnny L.A., sorry I checked that other thread for a couple days, but didn’t see you’d posted back to it.

Access automatically adds a time-stamp for all date fields. When you set your import specifications, change the datatype for that column to TEXT to avoid the timestamp. (This can cause problems if you’re doing date calculations later, but can be worked around.)

From your questions in the other thread:

To create a query, click on the Queries tab in Access. Then click Create query in Design view. In the popup box, select your Table and click OK. To add columns to the query, left-click the column in the Table box in the top pane and drag it to a column in the output pane at the bottom. (You can CTRL-click and SHIFT-click to select multiple columns.)

Now you should have a bunch of columns listed across the output pane (where it says “Field”, “Table”, “Sort” etc.). Click the red exclamation point at the top and it should run your query, and show you all the data in your table for the columns selected. Click the View button at the top left corner of the toolbar to get back to Design mode.

To change the formatting on your numeric fields, in the output pane of the query design page: go to the “Field:” box for the column you want to format. Right now, it should say your columnname in that box. Erase your “columnname” and type in: Format([columnname],“00000”) with however many digits you want your numeric field to be.

Click the run button again (exclamation point) and check to see that it’s formatted the way you want. Click back to design mode. In that same Field box, it now says “Expr1: Format([columnname],“00000”)”. You can change “Expr1” to the column name you want (although I don’t think it will let you use the exact same columnname again).

Save the query. Any time you change data in the table, you can rerun the query and it will update with the new data.

On the CSZ issue, you can parse it out in Access but it will take some work to figure out, plus several more queries. Give this a try first and post back.

On preview: OK, I see while I was typing (and getting some work done!), you’ve got some help in Excel. That’s what happens when I get distracted with work for a couple hours…

Let me know if you still need stuff in Access.

Johnny,

The “Dim S As Range” declares an object variable of type “Range” - it isn’t a variable to define what you want output, that is done by selecting cells with the mouse (I think you did get that far).

Now, the important thing is that the sheet should be formatted exactly the way you want in the output file: the leading zeros are important for two reasons: 1) they force each cell to have the same width regardless of the magnitude of the number it contains, and 2) you wanted your output that way.

So, if I’m guessing right, your problem is that the cells aren’t formatted as 10-character wide, with leading zeros. This was step 1 in my original reply.

Hope this helps!

I did that.

I’ll do it again. I’ve opened the original Excel file. I’ve highlighted the numeric fields. I changed them to Number, then went to Custom, added a bunch of zeros, and clicked ‘OK’. I saved the file as xls. Now I’m opening Access…

I click ‘New’ and choose 'Blank database. I click ‘Create’ and it opens a little window with three ‘Create’ choices. I ignore it. I click on File and ‘Get external data’. I choose the Excel file I just saved. (i.e., the one that has the numeric fields right-justified and with leading zeros) and a spreadsheet wizard opens. Next, Next, Next, Next, No primary key, Finish.

When I open Sheet 1 (I let the name default) my leading zeros are gone. When I export as text, the fields are left-justified.

But back to the Excel macro…

As I said, I did do the leading zeros. When I run the macro just on those columns, it looks pretty good (except for the negative signs, which shift values to the right). If I run the macro on the whole table, only the first column is in its own column. The rest of the table is not in columns.

redtail23: I looked all over Sheet1, but there’s not Queries tab. Turns out it’s on that little window I mentioned earlier. If I click on the Queries tab in this ‘db2’ window, will it know that it’s part of Sheet1? Or is it a separate thing?

I’ll experiment with what’s been posted so far. Right now I have to get out of here. My officemate’s constant throat-clearing (every 15 to 90 seconds, all day long) is making me homicidal.

I did my testing on nothing but positive numbers… are you sure you can’t adjust all your data to be positive numbers? (kidding!)

So, blank cells, cells containing text and negative numbers are going to be a complication. (I belatedly read your first post)

Here’s my test data:



|     6|     6|        10|        10|                            30|     6|
|     1|    -2|          |     55.55|San Diego, CA 92111           |     9|
|   101|   102|abcd      |       105|Los Angeles CA 90034-4950     |   109|
|     1|     2|         4|         0|Lancaster CA 93536            |     9|


The first row is special - it defines the column widths in the output file.

Here is the new macro, the easiest way to replace the one you already have is Alt-F8, select the macro’s name from the list and click edit. Copy and paste the new code, replacing the old.

Good luck!



Sub ExportText()

Dim S As Range
Dim R As Long, C As Integer, RowCount As Long, ColCount As Integer
Dim CellValue, CellOut As String, PadBlanks As String
Dim OutFileName As String, LineOut As String
Dim ColWidths() As Integer

OutFileName = "C:\Temp\Test.txt"
Open OutFileName For Output As #1
Set S = Selection
RowCount = S.Rows.Count
ColCount = S.Columns.Count
ReDim ColWidths(1 To ColCount)

For C = 1 To ColCount
    ColWidths(C) = S.Cells(1, C)
Next C

For R = 2 To RowCount
    LineOut = ""
    For C = 1 To ColCount
        CellValue = S.Cells(R, C)
        PadBlanks = String(ColWidths(C), " ")
        If CellValue = "" Then
            CellOut = PadBlanks
        ElseIf IsNumeric(CellValue) Then
            CellOut = Right(PadBlanks & Str(CellValue), ColWidths(C))
        Else
            CellOut = Left(CellValue & PadBlanks, ColWidths(C))
        End If
        LineOut = LineOut & CellOut & " "
    Next C
    Print #1, LineOut
Next R
Close #1


That’s much easier to code in Easytrieve. All I’d have to do is parse the field and if I find a space change it to a zero. If it finds a dash, then change it to a zero and set the negative flag (in the output from the Easytrieve) in its position. Of course, I’ve already written the code to fix left-justified fields. But I can always change it. :wink:

Now I need to learn how to run the PC version of Easytrieve. It’s a piece of cake using JCL on a mainframe, but the so-called manual that came with the PC version neglects to actually state how to run the Easytrieve once it’s written! (There are .pdf files I can look at. Didn’t have time today.)

Thanks for the help. I didn’t count on having to convert the Excel file to flat text. It’s a bit of a learning curve.

Heya Johnny, looks like you may have gotten this one licked but I’ll post the answers to your questions anyway.

I stepped through your process and I see one reason you’re having problems with my directions, which is that I don’t work from Excel much (I usually have flat files). I remember now that Access won’t let you set datatypes or anything when importing from Excel, it’s just supposed to pick up the info from the spreadsheet. And you’re right, it is dropping the number formatting with the extra zeroes padded in. You’d have to do the formatting in a query (see previous instructions).

You’d also need to format your dates in the query to get rid of the timestamp. That’s pretty easy - change your column in the query (per previous instructions for formatting the numbers) from DateName to FormatDateTime([DateName],2). That’ll change it to a m/d/y format without the timestamp.

When you click on the Queries tab and start a new query, you tell it that you want to base the query on Sheet1, that’s how it knows. (When you click Create A Query in Design View, it pops up a box titled Show Table - you select Sheet1 from the Tables tab and tell it OK - that tells it that you want to query the data in Sheet1.)

I’d probably write a VBA function to parse the CSZ. I could code a bunch of nested Ifs into the query itself, but that gets really ugly and wouldn’t allow as much checking as I could put into a function. From my experience with inconsistent data, I’d be checking for everydamthing on every piece, which I could do in a function. Let me know if you go Access and I’ll work one up.

Good luck with your project!