PDA

View Full Version : Solved: Operate with data from txt file



gabo53
10-22-2012, 02:22 PM
Hi,

In new to vba and just a regular user of Excel, I have a text report generated by a third part software, really messy, by now I've got created (using VBA) a new text file with the requiered data separated by "pipe" symbols, now I'm stuck with what is really need to be done, I need to look in each line for info and operate the next lines, e.g.

Data structure: Level | Description | Qty

Data Sample:

1 | Desktop PC... | 2
2 | DVD combo | 1
3 | Sata DVD-R... | 1
3 | Sata Cable | 1
2 | HDD | 1
1 | LCD Monitor | 5

And so on... so what I would need from that data is the products that actually are going out of inventory, from that i need qty of the products and descriptions, i.e.:

Sata DVD-R... | 2 (multipliying 1 unit for each PC)
Sata Cable... | 2 (...)
HDD | 2
LCD Monitor | 5

I'm not so interested in "code" because I want to understand and learn, but I don't know how to proceed.

How can this be done?

The data would be used in Excel, would be easier operate the data before or after importing to it?

Any help would be highly appreciated, thanks.

Sorry for the long post but I wanted to be as clear as posible :)

Teeroy
10-25-2012, 02:22 AM
Excel has a built in method that's perfect for this. Copy and paste the data into excel then select the column with the data, use "Text to Columns" (in Data menu pre excel 2007, not sure on the ribbon) and set it to delimited option with the pipe as the delimiter. This will create the 3 columns of data for you.

magelan
10-25-2012, 08:07 AM
If you are interested in CODING a solution but not in just someone giving you the code to solve it..

look into using split, which goes like



storeThingsInMe = split(stringToSplit,delimiter)
to build yourself a list.


From there, you can run the simple math operations. like


dim cellvar as range
cellvar = range(a cell to run operations on)

cellvar.value = cellvar.value * quantityINeed

now maybe you want to build some kind of array to reference things in, like


dim partNameArr(0 to 1) as string

and then use it later on when youre reading your list...


redim preserve partNameArr(o to countOfParts)



If you want to open the txt file from excel and not have to copy-paste it, look at this code. Of course you should NOT copy-paste this code into your workbook, as it wont work and you will wind up re-typing the code from scratch anyway, but use this as your example.


'Filename = the full path to Your txt file eg."C:\MyDocuments\Mytxt.txt"
Open Filename For Input As #1
Do While (Not EOF(1))
' Read the file one line at the time
Input #1, myString
Close #1

Dont forget simple coding methodology - put all of your code in a module, not on your sheet. put code for buttons and dropdowns on the sheet, not in the module. separate different procedures or actions into different parts of code. Always use option explicit

for example, i would work my code out like this.


option explicit
sub mainProg()
[some intro code if its necessary, like dimming some variables]
call getDataFromTxt
call buildList
call figureQuantity
[some other procedures or exiting code, like cleaning things up]
end sub

sub getDataFromTxt
[code that opens or reads the txt file]
end sub

sub buildList
[code that builds your list]
end sub

sub figureQuantity
[code that does your math]
end sub

gabo53
10-29-2012, 08:21 PM
Thanks for the help guys, I ended working with excel...

I imported the data to excel (reading the txt file and using split), then creating loops for sub levels of data it goes through each row extracting info and doing the needed operations.

Probably it could be done better and faster working with arrays, saving the info from the first file with the vba code, and once operated importing to excel, but the current approach does the job and I'm sure the computers used for it would be at least as good (regular) as mine so the 10 seconds it takes isn't so bad.

Thanks to magelan and Teeroy :beerchug: