excel csv text file import to database and view as pivot table for analysis
Posted on 2004-10-21
I have large text files, usually fixed width, sometimes delimited, huge files of data. I usually import them into excel and use them for reporting from the rows of data in these files. Step by step description is:
1) open raw text file
2) assign, add, modify field widths for the data column breaks
3) import data into an excel sheet
4) modify columns (such as divide percentage column by 100 so they'd show as 20% ie 0.2 instead of 20. ) , edit show correct decimals, add currency symbols
5) turn the data into a pivot table and begin analyzing for reporting purposes.
I'm not great with databases so maybe someone can help me find out an optimum way of automating this process to a level where I wouldn't need to open each of these final excel files with pivot tables to reach the data I need. Instead I want to type in the criteria I want in an excel sheet, and return the data I need. A simple database if possible. A pivot table-like solution would be amazing. so excel database query, access, (asp? coldfusion?) help would be appreciated. Thank you.