Data Extraction From One CSV File Into Another
I have a CSV file that contains student attendance records for classes in several learning centers across the state. The problems is that each row contains up to 25 attendees (students) per row.
I need to transform that file into a new CSV file. The new one would have a row for each student. So, if the original file had one row that contain attendance information for 25 students then my new file (table) would contain 25 rows. Each row having the attendance record for just one student.
Also, I need to automate this transformation so that it is easy to do and only requires a button click or something equally as simple. I thought about using using Excel or Access but I am not sure that is the best solution.
My question is, what is the best software application solution to use for the transformation?
Your feedback is much appreciated!
I need to transform that file into a new CSV file. The new one would have a row for each student. So, if the original file had one row that contain attendance information for 25 students then my new file (table) would contain 25 rows. Each row having the attendance record for just one student.
Also, I need to automate this transformation so that it is easy to do and only requires a button click or something equally as simple. I thought about using using Excel or Access but I am not sure that is the best solution.
My question is, what is the best software application solution to use for the transformation?
Your feedback is much appreciated!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mimcc, I am curious, why didn't you think Crystal Reports could be used as a solution. It reads and writes Excel files.
ASKER
Robr, that's a very good point. You should always think about how you want the output ultimately displayed. In my case, this is an integration file for an online forms application. I can't change how this 3rd party forms application is programmed; I can only transform the data to work with its programming to give me the desired output.
So my output will need to be a CSV file that is then imported into the 3rd party application to build a table that is used to display my data in a more readable format.
So my output will need to be a CSV file that is then imported into the 3rd party application to build a table that is used to display my data in a more readable format.
Crystal works on a single record at a time so it would be difficult to extract/separate the records.
Another issue is Crystal produces its own form of CSV which is probably not useable as input to your program. It puts the column headings or places for them in each line of the CSV rather than as the first record.
mlmcc
Another issue is Crystal produces its own form of CSV which is probably not useable as input to your program. It puts the column headings or places for them in each line of the CSV rather than as the first record.
mlmcc
ASKER
Scott, your point about using what you're most comfortable with is very good. However, what I'm most comfortable with is a high-end platform independent application development platform. The problem with using this solution is it has a price tag to go with its features set. This application is for a small non-profit that can't afford the "tank" and in reality they only need a "baseball bat".
Yes, either Access or Excel would work for this. However, I'm not experienced in either of these applications enough to just go do it, so I would need to learn on the fly. Which brings me to your point about using SQL and DAO, which makes a lot of sense to me. Also, I am familiar with SQL.
Another advantage in using Access is that I could import the data into a database which gives me more options for future back-office processing.
Yes, either Access or Excel would work for this. However, I'm not experienced in either of these applications enough to just go do it, so I would need to learn on the fly. Which brings me to your point about using SQL and DAO, which makes a lot of sense to me. Also, I am familiar with SQL.
Another advantage in using Access is that I could import the data into a database which gives me more options for future back-office processing.
ASKER
mimcc, you are absolutely right, it wouldn't work. I don't think I've ever used it to export a CSV file before but I knew it was an option. Now, I know why that's not a valid option in this situation or any others that require a standard CSV format.
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Robr and Scott, you both gave great answers that helped me process through this decision. I am going to choose Access over Excel for the reasons I listed.
Thank you all for your help and time!
Thank you all for your help and time!
With Access, you could link the workbook, or import it directly, and then use standard SQL and DAO to manipulate the contents. To me, that's easier than work row-by-row and column-by-column in Excel.