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

Posted on 2004-10-21
Last Modified: 2010-05-18
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.
Question by:turgan
    LVL 3

    Accepted Solution

    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! :)

    LVL 11

    Expert Comment

    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. a text file?

    Hope this will give you some idea.

    LVL 27

    Expert Comment

    Hi turgan,

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Shellfire Box VPN + Lifetime Subscription

    The Shellfire Box easily connects all of your devices, even those that don't offer the possibility to establish a safe vpn connection. Access blocked content and surf safely, no matter where in the world you are located.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now