Unanswered: NOOB-Import CSV & Reformat For Efficiency
This is for all you MS Access gurus, hoping you can help this total newbie solve a problem. I have dabbled in Access in the past, so am only a little familiar with it, but not enough to have any clue where to begin or what steps to take to resolve my current need.
I have a bunch of raw call detail data in CSV format (approx 160 or so columns). The data, depending on the column, consists of dates, timestamps, numbers, long strings of text, single digits, etc., but For the sake of simplicity, we'll say all data in each column is a text format, because that seems to work best for what I need. The problem is that today I only have an Excel spreadsheet which contains the 160 column headers and daily I have to take the raw CSV data and paste into Excel so that it lines up with the headers. I always bring the CSV data in as text format for all columns and this makes the dates and times correct (MM/DD/YY and HH:MMS respectively). Anyway, while this is a simple task, I have to do this multiple times throughout the day, which is a bit inefficient.
The biggest issue is that once I bring in the data, analyzing it for troubleshooting a call problem is very time-consuming. Some fields/columns have certain values that are presented for every call (varies per call) and it's not easy to remember what every value means for 160 fields. So the only way I can identify the values and their meanings is to insert a comment in the header cell in Excel and in that comment I list the details of the particular variables for that field, such as 1=meansthis, 2=meansthat, etc. The comments for all 160 column headers are already created, but the problem is that if I paste in 100 or 1,000 call detials (i.e. raw CSV data -- each call with 160 fields/columns), then I have to keep scrolling up to the header and Edit the Comment just to read the contents. Also, if I try to freeze the header row, then trying to view the comments is cumbersome, because the comment box is cut off when it reaches the border of the frozen pane and I usually have to unfreeze the panes or I have to drag the comment box around and resize just to read the data. This becomes very annoying after having to constantly do this all day long as you might imagine.
What I would like to do in MS Access sounds simple and so I hope this would be easy to resolve. Basically I want to create a method by which a person who is not that familiar with Access can get right into the program and paste in the raw CSV data and have Access spit out the results of that data with column headers in a clean, easy to read format. And in those results, instead of just displaying the CSV data in a nicer way, I also want Access to analyze each column, looking for specific "known" values and spitting out the meaning of those values, so that the users do not have to lookup what a 1 in column 47 means, or what a 2 means, etc.....instead the Access view will tell the user exactly what they want to know, making troubleshooting the call details that much simpler.
I can probably play around with some sort of GUI design that works for presenting the data in a clean way (I think). It's the part about getting Access to analyze each field and spit out, along with the raw value, the actual description of what that raw value means. For an extreme example, column 12 of the raw data identifies the call disconnect reason. This field alone can be one of 127 different industry standard codes, such as 3="No Route To Destination", 41="Temporary Failure", 17="User Busy", etc. All the raw data shows is a value 3 or 41 or 17 or any of the 127 values. So I want the user to be able paste in the raw CSV values and press a button and have Access reformat the data into a more readable view instead of 160 columns side-by-side and in field 12 I still want it to show the value 3 for example, but in parentheses I want Access to print the description of a value 3 -- (No Route To Destination).
Ideally I would like to have the results in some report, where the user can pick one call out of 100 to analyze or they can view all 100 at once, but in each call they would have the descriptions beside the ambiguous values from the raw data.
The problem is I have no idea where to begin with designing/coding any of this. I hope what I'm requesting is simple to figure out.
Datajunkie, your issue is not difficult. It may be more work than you are anticipating. But it sounds to me like you have a call table and several (maybe 60) lookup tables. Even if you have 60 lookup tables once you have set up one or two of them then you know how to set up the others. I would do the following:
1. Create a File Specification for your Call csv. Go to File/Get External Data/Link Tables (I am using Access XP might be different in more recent versions). Change the Files of Types to Text when the Link dialog box appears. Then when you see the Link Text Wizard hit the Advanced button. This will allow you to define all of the fields in your file. Give them appropriate names and data types (I would assign the correct data type instead of making them all text, dates may not order properly if they are set to text). When you have all that done, then press the Save As button to save the File Specification. Now whenever you need to link to a new CSV select the File Spec that you saved usnig the Specs button. Finish the wizard and your CSV will be linked. Now you can sort, search whatever you want in data view.
2. Create a lookup table for the 127 disconnect reasons. Are they stored somewhere that you don't have to retype them? Create a new table and add a DisconnectID field (this will be 1,2,3 etc.) and add a field call DisconnectReason. The DisconnectID should be a Long data type and is your PrimaryKey (highlight the field and press the key icon, a key should appear next to the field). The DisconnectReason should be a Text data type with an appropriate length. Save the table and call it tblDisconnectReasons. Then add all of the 127 reasons or a few for testing purposes. To test your lookup table, create a query, add the CSV linked table and the tblDisconnectReasons table. Then create a join between Column 12 from the CSV and the DisconnectID field. To do this, drag Column 12 to DisconnectID a line should be created. This is a join. To see the results add the * from the CSV table to the field list and the * from tblDisconnectReasons and view the query results. For every 1 in the CSV table you should see the DisconnectReason text.
If you can successfully do those two things you will know half of what you need to know to set up most or all of your lookup tabes. I would focus on these two things to see if you can master them then ask followup questions for more assistance.