Software·43,765 discussions

Data Extraction Software

Locked

Data Extraction / Merging Software
I am looking for a Excel plug-in or some windows based application that can extract data from many CSV files then, clean, manipulate, and merge data based on easy to define maps, scripts or macros. One of my goals is to find a software tool to handle and schedule dozens of incoming inventory files in various formats.

My goal is to cross match some inventory files (ex. column headers: part number, description, quantity) against price lists and merge the files. These price list are sometimes complicated and require calculations based on tiered pricing and product ranges. However, I want the program to be extremely easy to use so that a non-programmer can learn to use it quickly. See example below

table 1
part number description quantity
ABC123 Metal screw 20,000
BCD222 Metal bolt 20,000

table 2
Part # Price 1 Price 2 Price 3
(1-99) (100-999) (1000-9999)
ABC123 .09 .06 .03
BCD222 .23 .20 .15

Merged Table
part number description quantity price
ABC123 Metal screw 20,000 .03
BCD222 Metal bolt 333 .23

I have looked at a program from Pervasive called Data Integrator and it was a little too complicated for a non-programmer to handle. Do anyone know of any applications preferable an Excel plug-in’s that will perform the above task easily? Please help.

Topic

Reply To: Data Extraction Software

In reply to Data Extraction Software

I would probably use Microsoft Access if I were in your situation. It has the ability to link/import/export to/from multiple file formats. You can save field mappings for repetitive imports. Wizards make creating tables, queries and reports a breeze for someone who is mildly aquainted with the basics of such operations. Good help files and it’s pretty rare that I can’t find the answer to how to do something in the help section or in Access user forums on the web.

Reply To: Data Extraction Software

In reply to Data Extraction Software

I agree–Access would be flexible enough for what you’re describing. It can work either by maintaining sheets in Excel and linking to them, or importing the full sheet into Access and using Access forms for further manipulation and updating (my preference–constantly linking to Excel works, but can be poky). Excel really won’t handle the merging as well as Access–just not what it’s designed to do.

Reply To: Data Extraction Software

In reply to Data Extraction Software

You can use a vlookup command in excel to lookup the prices on table 2 and then perform a calc based on the values it brings back.

table1
part number, description, quantity
Column A, Column B, Column C,
ABC123, Metal screw, 20,000
BCD222, Metal bolt, 20,000

table2
Column A, Column B, Column C, Column D
Part #, Price 1, Price 2, Price 3
Pricing (1-99), (100-999), (1000-9999)
ABC123, .09, .06, .03
BCD222, .23, .20, .15

In table 1 add in column D with
vlookup($A2,!table2!$A$1:$D,2,0) * C2 to have the price * Quantity

Table 1
part number, description, quantity, price
Column A, Column B, Column C, Column D
ABC123, Metal screw, 20,000, .09
BCD222, Metal bolt, 20,000, .09

Reply To: Data Extraction Software

In reply to Data Extraction Software

I think the software intelliget available at http://www.mountonetech.com/products.asp is just right for you.

A suggestion for you

In reply to Data Extraction Software

Hi,

My suggestion would be to load up all your data in a database and perform all your complex calculations in there (a database will offer great functionalities for that). Then just export your processed data to Excel.

A great took to perform all your data import/export is called Data Moving Tool http://www.sersoftware.com/

Regards
JB

[Author]

Replies