NOOB-Import CSV & Reformat For Efficiency

[datajunkie]

First, I'm not really sure what forum I should post this particular topic in, so if I should put this somewhere else, please let me know where would be best.

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:MM:SS 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.

I have no idea where to begin with designing/coding any of this. I hope what I'm requesting is simple to figure out.

 

[MagicMan]

Hi.
I posted a "How to learn VB or Access" response in the string "Renumber a List". You might want to take a look. If you have Visual basic experience, you can use VB to import the csv to access. I use Access 2007, and the External Data (top) menu provides the capabilty to create a table, append to a table , or link to an external file as a table in Access. The external file selection can be another database, an Excel workbook, a Text or Csv file. The proceedure can be saved, and a command button on the main form can launch the append function after the initail table is created. As to column definition, each of these can be set up as a table, with the 160 field table (read about normalization) having lookup defintions to the related table. The form, or forms and reports can be designed by using the forms wizard, and similarly the reports. If you wish to break the information up into more logical forms(pages), you can create a blank form and add fields as you see fit. Access is quite simple to use, but can also be complex. If you read the other thread, planning and up front reading to gain knowledge will save you many hours down the road. I do believe you can get a basic version up and running real quick with little experience, which will then provide you with the time to read and design the real application.
Smiles
Bob

 

[JohnLee]

Hi,

I have something similar, in that I import fixed width data as apposed to CSV. However importing CSV into access is stright forward. Now I'm using Access 2000.

The process I'm about to describe I only have to do once and thereafter, everytime I get a file I just run my program to import the required data. Now my data is received in exactly the same format every time, and that's critical if this process is to work. So your CSV files should follow the exact same format every time you receive them.

Firstly make sure your in the table Objects view, then choose File/Get External Data/Import from the file menu. The import window will open up. From here navigate to where the CSV file you want to import is, now whilst in that same window below where "File Name" appears [your selected CSV file] there is a drop down list box field titled "Files of type:". From this list box select "Text Files (*,txt;*,csv;*,tab;*,asc)" double click on the required CSV file and the import text wizard will open up.

In this view you will see a sample of your data in the "Sample from Data" window and above that you will see two options as follows:

"Delimited - Characters such as a comma or tab seperate each field"
"Fixed Width - Fields are aligned in columns with spaces between each field"

Select the 1st Option "Delimited - Characters .........", then click on the next button, in this next view you will see your data seperated according to the commas, you can scroll right and left to check that the data has been seperated as you required and can make adjustments as required if it has not. Once you are satisfied that the data is seperated as required click on the next button and the next view will present you with two options as follows:

"In a new table"
"in an existing table"

By default the "In a new table" is checked, and since this is your first import and first table, accept the default option.

Click on the next button again and you will be presented with another view, but this time you are invited to give meaningful names to your fields and to specify their data types etc. You should take this opportunity to do this at this point [it will make things easier later on], take your time and ensure that you have named and given the correct data types to each of your fields.

Once you have done this click on the next button again and you will be presented with the next view, which will give you the following options:

"Let access add a primary key"
"Choose my own primary key"
"No primary key"

By default Access selects the first option "Let access......" I tend to let access do the work and let it add the primary key.

Click on the next button and you will be presented with the next view, which will give you the opportunity to give your CSV file a meaningful table name. Something like "tblMyCSVFile". the first three characters identifies it as a table object "tbl". Now your not finished here yet. DO NOT CLICK THE NEXT BUTTON YET, instead click on the advanced button, this will bring you to the Import Specification window, here you will give your import specification a meaningful name [because if you don't do that now, you will have to repeat the process we have just been through everytime, unless you create the specification]. In this view you can see your field information for your specification, plus a number of other features. Your only concerned for this purpose with the "Delimited" option which you should ensure is checked and then once you've done that click on the "Save As" button, a popup dialog box will appear titled "Save Import/Export Specification" and it will have a default name. Change this name to a meaningful name that you CAN REMEMBER [you will need it later, so I suggest you write it down]. Once you've given it a meaningful name click on the "OK" button then click on the "OK" button in the "Import Specification" view, where you will be taken back to the "Import Text Wizard" view, Click on the "Finish" button, and your table will be created with the data seperated out with the field headings and data types created.

We'll come back to the import specification later, for when you start to automatically import your CSV files.

The next step is to set about creating some look up tables that will be needed to assist with identifying the meanings of those variables you mentioned.

Once you've done that, we can then set about creating the other tables and queries that will clean up your data for your reports. Don't worry, most of this will be automated, using VB and/or macros, but you need to get this far first.

If you are able to if you could post a copy of your CSV file or a sample of it, I can then follow you as you build your DB and assist you.

John