Access 2010

Factual Questions
1

I have a new computer at work, with Windows 7 and a new MS-Office suite. I won’t get into how Microsoft seems to have done all they could to make the programs more difficult to use, but there’s something I’d like to fix if possible.

I’ve been using Access 2003 to convert Excel files into the fixed-position text files that are necessary for Easytrieve. Annoyingly, it added an ‘ID’ column – a column of sequentially-increasing line numbers. All of my Easytrieves (fixed-positions, remember) have their fields defined to account for these numbers. Access 2010 does not automatically insert the ID column. This would have been great four years ago. Now, I’d have to redefine the field positions in all of my Easytrieves. I assume that I could simply insert a column at the beginning of the file. It doesn’t matter that it wouldn’t be populated. But Windows 7 has been so buggy, and I’m so new to it, and I have a backlog of data to get through, that I haven’t had time to try that. In the meantime, I’m using a copy of Access 2003 that the IT guy installed for me.

Is there a way to tell Access 2010 to automatically insert an ID field (like 2003 does) every time I import a file?

2

Exactly how are you importing the file? Are you using VBA code, a macro with saved file specifications, manually, or what? How to fix the problem depends on what you’re doing.

I’ve just gotten 2010, but I’m sure I can figure this out if no one else steps up.

3

Manually. I don’t know VBA coding. Import => click on a .csv file => change all fields to Text => click OK.

4

That’s odd, it doesn’t look like the process has changed. I’ll walk through it, let me know where this goes off from your steps.

OK, so now in 2010 you have to pick the External Data ribbon.

Then click on the Text File button. (FYI, .csv files are text files, not really Excel files. Your computer is just set to open them with Excel.)

Select your file. I’m assuming you’re importing into a new table, so select that option. Or the “import to existing table”, they’re the same until the end.

Make sure it’s picked the Delimited format & click Next.

Check the box if you have a header row that you want recognized. Change all the field types to Text and click Next.

Name fields if desired (i.e., no header row) and Next.

This is the key page. It should be defaulted to “Let Access add primary key.” That’s what you’re used to Access doing, and mine still defaults to that in 2010. If it’s not set to that, then change it to that.

Next, and then give a table name (or pick a table, if you’re importing to an existing) and Finish and Bob’s yer uncle.

There are some things you can do to streamline this, if you’re interested.

5

Right. Sometimes I talk in shortcuts. Most data we receive is in .xls format. I do things to it (including getting rid of double-quotes, commas, and double-spaces) and save it as .csv for importation into Access.

That sounds like the… erm ‘key’. Apparently this installation defaults to no ‘key’ column. I’ll try it mañana when I’m at my desk.

Oh – One other thing I’ve just remembered. In 2003 I get a window that pops up with my file information in it. When I import the files, they appear below the three database files. It’s a simple thing to highlight one, click the X, click Enter, and repeat as necessary to delete the files I need to delete. (I like to delete them right away instead of saying ‘yes’ to overwrite.) Then I select the previous month’s file (which I’ve just imported, and which was ‘cleaned’) and export it as text, and repeat on the current file. In 2010 there’s a panel on the right. It only shows the latest file I’ve imported. I have to import a file, then export it, then import the other file and export it. It messes up the ‘flow’. Is there a way to not have the panel, and to have the window like 2003 has?

6

Not that I know of. It’s the spiffy Office 2007/2010 redesign.

It’s why I’m just now starting to look at 2010. :smiley:

7

Do your incoming files have the same name every month?

It would be fairly easy to do a macro that automates all of this (import/export/delete).

8

Yes, I give them the same names; e.g., 987654_Current.csv, 987654_Prev.csv, 987654_Current_Output.csv.

If you’re curious, this is what’s happening: We get a file (‘Current’). It’s compared to the previous month’s file (‘Prev’). After being fonverted to FB text, the files are run through Easytrieve to sort them and to compare the two files. If the account numbers match, the name and address information from the previous file and the other current data are written to Current_Output (text). If they don’t match, a Nonmatch text file is written. Both output files are imported into Excel. The Nonmatch file is cleaned up (names and addresses corrected and put into a standard format), and then appended to the Current_Output file and the whole thing sorted. If the data is just being ‘cleaned up’, that’s that. If it’s being reformatted into one or both of two designated formats, it gets converted into text (with Access) and then run through a reformat Easytrieve.

9

This doesn’t directly answer the OP, but it might be useful to him anyway: try UBitmenu to bring back all of your well-known menus.

(In the case of Access, I gave up and reinstalled the Office 2003 version; too many broken references and a horrible workflow for development.)

10

Yeah, I’ve got Win7Pro with an XP VM so that I can run my stuff in 2003 until I have time (hah!) to get them ported over. I’ve got LOTS of VBA code to fix. I’ve stalled on this for what, nearly half a decade now? But I really don’t have a choice - the entire org is switching over to 2007/2010, and my customers are tired of not having it when everyone else does.

Johnny, I’m working from the assumption that you can’t do anything about the creation/formatting of your initial files (either because they’re from an outside source, or because it would be too difficult to fix in that system).

And I’m figuring that you’re using Excel for the clean up either because you’re more familiar with Excel and/or because Access doesn’t always play well with quotation marks. It’s entirely probable that you could set up Access to do the whole process, if you want to. I’ve got numerous Access processes that do just this sort of thing for people.

But for now, Imma leave that alone.

I do wonder why you are exporting the Excel files to csv and then importing those to Access, though. Why not just import the Excel files directly into Access?

At any rate, the first thing you should do is set up some file specifications. Even if you decide not to use macros, this will reduce the work you have to do each month.
Pick one of your files to import, and go through the process of setting up the import. Once you have the Field Names and Data Types set, click the Advanced button down in the bottom left corner.

This opens up the Import Specification box. If you look over the fields, you’ll see that it’s mostly just a different view of the stuff you’ve already set like the field and text delimiters, datatypes and indexes, etc. You can do all your settings in this box instead of the usual display, if you want. For instance, it can be easier sometimes to just scroll down the Data Type column and set everything to TEXT rather than clicking through the columns in the GUI.

Once you have everything set up correctly, click the Save As button and name the new specifications something that makes sense. If you’re going to work towards automation, use underscores (_) rather than spaces, it will make things easier later. Click OK when you’re done, and OK again to get out of the Import Specs box.

Voila! Now, any time you want to import that type of file, just click the Advanced button as soon as it appears, then click the Specs button to get your list of saved specifications. Select the File Specification for that file and click Open. All of the setting choices you made are automatically applied to the current file. You can just click Next through the wizard until you get to that key page and select your Primary Key.

The import specs are saved inside this particular database, so you won’t see them elsewhere - although you can import them into a different database if needed.

Now, do the same thing for your Export files.

Access 2010 also comes up after you finish an import/export and asks if you want to save settings. I would imagine that it’s doing something similar to this process - but I have not used it and can’t confirm that.

Let me know if you have any questions.
Give this part a try and then, if you’re interested, I’ll walk you through setting up a macro to automate your process.

11

Most of the data arrives in Excel format. In the past, my former employer would accept data in just about any format. Files would be converted to text at their data center, and there were COBOL or Easytrieve programs to put the text files into their format. They still accept ‘legacy’ data as it had been coming in, but new data has to be in their new (1500+ byte) format. (There’s also some data that they accept in their older 350-byte format.) My current employer hired me because I knew Easytrieve and could convert the data into the accepted format(s).

Before I arrived, Excel files were ‘cleaned up’ manually. As I said, this primarily involves manipulating the names and addresses. For example, getting rid of consumer names and using their business names, moving DBA names from the primary name field and putting it into the secondary name field (e.g., ABC Corp DBA Wonder Weasel Inc gets the names put into two different fields), sorting out the primary and secondary addresses, fixing formats (e.g., making all of the phone numbers the same format), and so on. ‘Legacy’ data sometimes has fields that do not conform to previous data that must be removed, or they are lacking fields that need to be added. It would be nice if all of this could be done by a program, but businesses do their spreadsheets in so many different ways that you need a human to make judgments and to do the maniplation manually. I do have some data that is cleaned up ‘well enough’ with a program, but it’s not as good as doing it in Excel. I could make it better, but that would involve a whole lot of parsing that would slow things down. For these data, the existing program is acceptable.

Before my employer bought Easytrieve, everything was done ‘by hand’. Some files are tens of thousands of records long, and others are only a couple thousand but need an enormous amount of manipulation. And they need to be ‘cleaned’ every month. With three people (me, my coworker, and my boss) doing this every month, there were variations; and people tend not to do things exactly the same from month to month. This causes ‘exceptions’ in my former employer’s system. ‘Ave.’ is not the same as ‘Ave’, for example, so someone there has to check to make sure the record is correct. So I wrote a program to compare new data to old data. If the account numbers match, the names and addresses from the ‘clean’ file are inserted into the new data and the current amounts, dates, and whatnot are taken from the new file. This way the number of records to be manually cleaned is very much reduced. As an example, there is one file that has 80,000+ records, and the new records are only a couple hundred. New records are then available for comparison the following month. (Some data contributors do not send zero-balances. In those cases I write a third file that has cleaned data from previous files, which is appended to the ‘matched’ – ‘Current_Output’ – file with zero balances inserted into the amount fields. Thus, I don’t end up cleaning the same records every other month.)

To do all of this, the data must be in fixed-block text format. At first I thought I’d just open the Excel file and save it as FB text. Unfortunately, Excel 2003 doesn’t offer this option. The file could be written in fixed-block, only there was a limit to the record length. The ‘left side’ of the file would have everything in their proper fields, but anything beyond Excel’s line length limit would be put at the bottom of the file. Or I could get the whole Excel record written to a single text record; but it would be tab-delimited. Easytrieve will not read tab-delimited files; the files must be FB. So the solution is to save the file as comma-delimited. Commas must be gotten rid of, else Access will split a field into two fields at the comma. So I change commas to spaces, and double spaces to single spaces. I also discovered that double-quotes mess up the format, so those are deleted. Access can write files as FB text, where I can define the field length and the starting position. This is exactly what I need for Easytrieve. The only reason I use Access is so that I can make a FB text file.

Why not import an .xls file directly into Access? When I tried it (four years ago) it didn’t work. I see that Access 2010 has a button to do that, but I was not successful with Access 2003. Now that we have Excel 2010 (or is it 2007?) I think I can export Excel files as text such that they are not tab-delimited, or such that the records are not broken and moved to the bottom of the file. I haven’t tried it yet. I don’t know if the column lengths would remain the same from month to month, though. (And I’d have to change the ‘library’ section, where positions and lengths are defined) in Easytrieve.

12

Oh lordie, one of those. BTDT. You have my sympathies.

Yeah, I’d try the FB export again from Excel 2010. They greatly upped the allowed number of columns & rows, so maybe it will work now.

Another program you might want to look at someday is UltraEdit. It’s fairly cheap, I think around $50 for the set with the file compare software these days. I use UE all the time for splitting delimited files into columns. It’s a two-click process, and lets you work with the text as columns (e.g., cut & paste an entire column).

I originally bought it to work with giant files that nothing I had would open. Then with a different set of files, I needed it for cleaning up the hex code. Then I learned to do RegEx and write macros in it to do text cleanup for data import.

It’s also a code editor, but I don’t use that part. I do use the companion software UltraCompare to compare different files and find mismatches.

13

Open Office (the free, open source alternative to Office) can do this.
Calc (the equivalent to Excel) can read Excel files, and Save As a fixed format file.

So you could just open your incoming Excel file in Calc, then save it as a fixed file, and feed that to Eztrieve. A much simpler process.