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!
LVL 1
FgriffinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
it depends on how you want the output displayed.  If ultimately Excel, then that is the app to use.

I commonly use VBA in Excel as i find it easy to test. Even to read and write files.

But a pure VBS or Powershell script would work just as well. Whatever you feel most comfortable using.

have a read of http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_1300-Get-Excel-OpenText-to-Import-Exact-File-Contents.html
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Either Access or Excel would work for this, so it really depends on what you're most comfortable doing. I'm more comfortable using Access, so that's what I'd use, but as Robr said it's really up to you.

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.
FgriffinAuthor Commented:
mimcc, I am curious, why didn't you think Crystal Reports could be used as a solution. It reads and writes Excel files.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

FgriffinAuthor Commented:
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.
mlmccCommented:
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
FgriffinAuthor Commented:
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.
FgriffinAuthor Commented:
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!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can import CSV into access easily. The External Data ribbon has several options for this, or you can use DoCmd.TransferText from VBA to handle that.

Once you get the data into a table, you can then review it, and normalize it from there. I assume you have some experience with these sorts of things, but essentially you'd break out the columns of that table into Rows, as needed. For example, I'd assume you have a Student table, a Course table, and Student_Course table (at the very least). You could query the imported table for Distinct STudent records, and then determine if you need to add any of those using a NOT IT scenario:

SELECT DISTINCT Student FROM YourImportTable WHERE Student NOT IN (SELECT Student FROM Student)

You could then loop through a recordset, and add the new STudent records (or you could use an INSERT INTO statement, instead of the SELECT above).

You'd do the same for Courses, and such.

The tricky part would be the Join - you'd have to go row-by-row in the Import table, and determine which students are associated with that Row, and then add records to the Join table.

That's a very rudimentary start, of course, and without actually seeing the data it could be well off the mark.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FgriffinAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.