• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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

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

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.

Hi turgan,

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now