Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    1

    Question 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.


    THE PROBLEM:

    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.


    SOLUTION:

    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.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'll start by saying that this is *NOT* simple.

    You'd have to fully rationalise every field, with possibly a related table for the meanings of each, normalising the structure and then create complex forms and reports to analyse the data.

    If the 127 standard codes are common to multiple fields, it will simplify matters, but not by a lot... it will really only reduce the number of tables.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are the columns always in the same order? If so, this should be a doddle

    EDIT: Well, the solution I'd offer is to import into access and export straight back to excel with the column headers populated (should really have clarified this before, apologies )
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Are the columns always in the same order? If so, this should be a doddle

    EDIT: Well, the solution I'd offer is to import into access and export straight back to excel with the column headers populated (should really have clarified this before, apologies )
    LOL - I hit quote and was about to write "In that case I don't I reckon you have read the question properly", but you had added the EDIT in the interim.

    I was right
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    So was I
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •