excel csv text file import to database and view as pivot table for analysis

Hello,
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.
turganAsked:
Who is Participating?
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.

omer_shaCommented:
coldfusion?
your on your own there! :)

however, you can create a transfer text macro to get the text into a table.

first thing you have to do is to manually import one text file so you can create its Specification you will later use in a macro.

right click in an empty area in the database window and select "import", choose the type that matches your needs and select the file, then, in the wizard, goto advanced.
here you can create the Specification for your text file and define the data type for eace field you have.
also, you will need to select the form in which your raw text file is built (be it a comma, or tab seperated fields, or a constant width for every field type of file)
save the Specification (that will enable you to select it in a macro).
then, finish the wizard and create the table.
check to see everything is in place and all the datatypes match you liking.

now, create a from, based on that table, drag all the fields into the form design grid, and bring it to pivotTable or pivotChart view. (view--> pivottable or pivotChart)
this stage is almost identical to MSexcel.

once you have your pivottable form (save it) you can create the macro that will import the file into the desired table.
in a new macro select action - transferText, here you can select the Specification you created early on.

you can also use this macro to delete (using deleteObject) the table before importing new data or Backing it up using TranserText to Export the table elsewhere.

hope this helps you! :)

omer.
0

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
sparabCommented:
1.  I suggest you Link your text file in access (assign some unique table name)
2.  Make one report format in access or crystal report (using This Unqiue file name)
3.  So next time when u want to do the same process you just have to take care about unique file name.

I am giving you more example about importing Text file in delimitated format.

http://www.experts-exchange.com/Databases/MS_Access/Q_21002499.html

http://www.carjax.com/docs/VB6DBHow2/ch05/ch05.htm#Import a text file?

http://www.experts-exchange.com/Databases/MS_Access/Q_21002499.html

Hope this will give you some idea.

Regards
Sparab
0
jjafferrCommented:
Hi turgan,

Can you please give us a sample of how this raw text looks like and how you want it in the Table.


jaffer
0
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 Access

From novice to tech pro — start learning today.