Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-10-21
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

omer_sha earned 2000 total points
ID: 12378609
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

ID: 12380110
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.carjax.com/docs/VB6DBHow2/ch05/ch05.htm#Import a text file?


Hope this will give you some idea.

LVL 27

Expert Comment

ID: 12380556
Hi turgan,

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


Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

598 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